SQL restore script - name of backup file??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Administration (2000)

SQL restore script - name of backup file???

Author

Topic

normajeanb
Starting Member

7 Posts

Posted - 2007-10-24 : 17:49:24

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-24 : 18:27:55

quote:
Originally posted by normajeanb

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!)

Here's what I use:


SELECT t.DatabaseName AS dbName, bms.physical_device_name AS bkpName
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bms 
ON bs.media_set_id = bms.media_set_id
INNER JOIN master.dbo.sysdatabases s 
ON bs.database_name = s.name
INNER JOIN
(

	SELECT 
		database_name AS DatabaseName, 
		MAX(backup_start_date) AS BackupDate
	FROM msdb.dbo.backupset
	WHERE 
		database_name IN ('GT', 'GT_QTRACS', 'GT_Reports', 'QAdmin', 'QLandmark', 'QMap') AND
		type = 'D'
	GROUP BY database_name
) t
ON bs.database_name = t.DatabaseName AND bs.backup_start_date = t.BackupDate

Just change the WHERE clause in the derived table to what fits your requirement.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

normajeanb
Starting Member

7 Posts

Posted - 2007-10-24 : 19:10:37

Thanks for your reply - I am on that same track, but the issue is that the name stored in the backupmediafamily is NOT the name of my physical backup file. I think it should be - I've logged an issue with Microsoft about this but haven't heard back yet.
By the way, I ran your query, and here are the results:
DBName:CypressReports
BackupFileName: BE_SQLAgent-CypressReports__35bbfdc7_add4_48c5_bcdf_f41120e8f02a_
Issue is that the REAL backup file in backup folder is:
CypressReports_backup_200710240001.bak

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-24 : 19:19:10

I don't use maintenance plans, so I haven't seen that issue before.

So "bms.physical_device_name AS bkpName" isn't providing a path either?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

normajeanb
Starting Member

7 Posts

Posted - 2007-10-24 : 19:43:34

Well, sleuthing a bit more I found that the most recent backup (the one with the odd name) is NOT the full backup I have scheduled for midnight. In fact there is a 'mystery' backup run under the user_id:
NT AUTHORITY\SYSTEM that occurred at 8:00 this morning. Now one fact about our setup is that the actual server is several states away - I do the administrative DBA work here remotely. We DO have an IT guy in the office where the server lives and now I'm wondering if he could be setting off a backup there that writes to a tape drive or something, and that executes as the NT AUTHORITY\SYSTEM id.
So, maybe that odd name of the physical_device_name is some kind of backup device that I know nothing of? Does that seem likely?

normajeanb
Starting Member

7 Posts

Posted - 2007-10-24 : 19:48:22

P.S. In answer to your earier post that odd name is indeed the physical_device_name stored for the most recent backup in the backupmediaset table.
I was able to find the correct filename that I was looking for (my midnight backup filename) but it is not now the most recent backup - it was superceeded by this mystery backup.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-24 : 19:53:26

Yes that seems very likely. We don't allow our server admins to perform database backups. We only allow them to sweep our files to tape.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

normajeanb
Starting Member

7 Posts

Posted - 2007-10-24 : 19:59:10

Thank you Tara!
That is what I will implement here too and I think then my script will work to pull the most recent backup.
Many thanks for helping me with this.
Cheers, Normajean

Kristen
Test

22859 Posts

Posted - 2007-10-26 : 06:02:56

Not sure I would my script to rely on MSDB - which might be "toast" when I need to make my restore.

Some related into here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example (including queries for listing Backup and Restore history etc.)

Restore based on filenames see:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71665
and some discussion via this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Restore+Full+and+all+TLogs+backups+based+on+files+in+disk+folder

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources