Finding Files - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Finding Files

Author

Topic

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-10-09 : 21:16:04

Can i do this

I want to find certain backup files in a particular directory
and copy over to a different folder

some thing like this
I can get name from sysdatabases

Select name from sysdatabases
where name in(db1,db2,db3)

may be put it in a cursor

xp_cmdshell dir 'C:\mssql\backup\@dbname_db_*.bak'

or there might be a better way of doing this

any clue where to start would be of great help

Thanks,

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-10 : 00:53:21

see
http://www.nigelrivett.net/CheckIfFileExists.html

I would recommand the xp_cmdshell with temp table version.

Theres no need for a cursor as database names are unique so you can use that for the loop.

In fact
http://www.nigelrivett.net/BackupAllDatabases.html
does a similar thing in places

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-10 : 13:00:58

Why not use the backupset table in the msdb database to do this? That is where the backup information is stored. From there you'd be able to find out the file name and other information. Then you could use xp_cmdshell to copy the file.

Tara

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-10-11 : 00:42:46

I can't find the filename in the backupset table

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-13 : 12:16:49

The file name and path is stored in msdb..backupfile. Joining these two tables together should work for you.

Tara

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-10-14 : 19:41:26

Tara,

There is no column which holds backup file name
in either of the tables

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-14 : 19:45:00

It's backupmediafamily instead of backupfile.

Tara

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-10-15 : 20:38:53

Tara ,
I kind of wrote this query to get the most recent file
differential backup file names of all the databases which are not in full recovery mode but i get all the files that are backed up today.

Select bm.physical_device_name, max(bs.backup_finish_date)
from msdb..backupset bs inner join msdb..backupmediafamily bm
on bs.media_set_id = bm.media_Set_id
inner join master..sysdatabases d
on bs.database_name = d.name
where DATABASEPROPERTYEX(d.name, 'RECOVERY') <> 'Full'
and bs.type = 'i'and convert(char(10),(bs.backup_finish_Date),101) >
convert(char(10),getdate() -1,101)
and convert(char(10),(bs.backup_finish_Date),101) <
convert(char(10),getdate() +1,101)
group by bm.physical_device_name

suppose for example these are the backup files of two databases

C:\backup\Test_diff_backup.200310151300
C:\backup\Test_diff_backup.200310151000
C:\backup\Test_diff_backup.200310150500

C:\backup\est_diff_backup.200310151300
C:\backup\est_diff_backup.200310151300
C:\backup\est_diff_backup.200310151300
i want only
C:\backup\Test_diff_backup.200310151300
C:\backup\est_diff_backup.200310151300

I hope this is not confusing
Thanks,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources