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 return the correct file name from disk.
Here is the name of the filename in backup folder:
Here is the physical_device_name returned from the query:
Here is the query that I ran to get the 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 (already set to value of 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?
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.....etc. etc. etc.