Scripting restore - unexpected filename

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 2005 Forums

Transact-SQL (2005)

Scripting restore - unexpected filename

Author

Topic

normajeanb
Starting Member

7 Posts

Posted - 2007-10-24 : 17:52:37

Hi; I'm writing scripts to pull the most recent backupset for automating a restore (should I ever need to run a restore, errr I guess I should say 'WHEN' I need to run it!)
The problem is that the physical_device_name returned from the system table does not really return the actual file name from disk.
Here is the name of the filename in backup folder:
CypressReports_backup_200710240001.bak
Here is the physical_device_name returned from the query:
BE_SQLAgent-CypressReports__35bbfdc7_add4_48c5_bcdf_f41120e8f02a_
Here is the query that I ran to get the physical_device_name:
SELECT mf.physical_device_name
FROM msdb.dbo.backupset b
join msdb.dbo.backupmediafamily mf
on b.media_set_id = mf.media_set_id
Where b.database_name = 'CypressReports'
AND b.backup_set_id = @backup_set_id_start (most recent bk)

The backup is getting created via a maintenance job (SSMS generated).

The final result of my complete query is supposed to generate Restore statements that I can just execute. Here's what got generated (see below). I can see that it correctly found all the transaction logs that I do every 15 mins (keeps the log files small for replication agents), but I'm really wondering about that full backup name in the first command????
What is this name? How did it get created? Why is this name saved in the system records and not the actual name of the backup file?
Any advice would really be appreciated here. (I'm a SQL developer that is a newbie DBA and wading into unfamiliar waters here.)
Thanks! Normajean
BackupSetID Command
7229 RESTORE DATABASE CypressReports FROM DISK = 'BE_SQLAgent-CypressReports__35bbfdc7_add4_48c5_bcdf_f41120e8f02a_' WITH NORECOVERY
7235 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710240850.trn' WITH NORECOVERY
7240 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710240920.trn' WITH NORECOVERY
7245 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710240950.trn' WITH NORECOVERY
7250 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710241020.trn' WITH NORECOVERY
7259 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710241050.trn' WITH NORECOVERY
7264 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710241120.trn' WITH NORECOVERY
7269 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710241150.trn' WITH NORECOVERY
7274 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710241220.trn' WITH NORECOVERY
7279 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710241250.trn' WITH NORECOVERY
7284 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710241320.trn' WITH NORECOVERY
7289 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710241350.trn' WITH NORECOVERY
7294 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710241420.trn' WITH NORECOVERY
7303 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710241450.trn' WITH NORECOVERY
7308 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710241520.trn' WITH NORECOVERY
7313 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710241550.trn' WITH NORECOVERY
7318 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710241620.trn' WITH NORECOVERY
7323 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710241650.trn' WITH NORECOVERY
7328 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710241720.trn' WITH NORECOVERY
999999999 RESTORE DATABASE CypressReports WITH RECOVERY

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-24 : 18:01:56

dup? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91545

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources