XML Feeds
There was a need to create a job that would take the last backup from one server and restore it on another server every day at 5 AM. There are a couple of things you need to know before you do that; some of these things are: permissions, what is the file name of the last backup, are there any jobs that need to be stopped and disabled before you restore etc etc.
This post will focus on getting the physical name for the last backup for a database. Let's look at some code.
Create a test database
- CREATE DATABASE test
- GO
Create some sample data
- USE test
- GO
- SELECT * INTO TestTable
- FROM master..spt_values
- GO
Now it is time to do the backups, I have a C:\Junkdraw\ folder on my machine and a job that runs every night that will delete everything older than 2 weeks (I got this idea from the lifehacks book and it really helps with getting rid of not needed files). If you want to run the backup commands on your PC, just make sure to change C:\Junkdraw\ to something that does exist.
- BACKUP DATABASE [Test] TO DISK = N'C:\Junkdraw\Test.bak' WITH NOFORMAT, NOINIT
- ,SKIP, NOREWIND, NOUNLOAD, STATS = 10
- GO
- BACKUP DATABASE [Test] TO DISK = N'C:\Junkdraw\Test2.bak' WITH NOFORMAT, NOINIT
- ,SKIP, NOREWIND, NOUNLOAD, STATS = 10
- GO
In order to get the information about the backups we need to join the backupset and backupmediafamily tables. Here is the query that will give me the physical device name, backup start date, backup finish date and the size of the backup.
- SELECT physical_device_name,
- backup_start_date,
- backup_finish_date,
- backup_size/1024.0 AS BackupSizeKB
- FROM msdb.dbo.backupset b
- JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
- WHERE database_name = 'Test'
- ORDER BY backup_finish_date DESC
Output
physical_device_name backup_start_date backup_finish_date BackupSizeKB C:\Junkdraw\Test2.bak 2010-03-24 12:43:43.000 2010-03-24 12:43:43.000 1483.000000 C:\Junkdraw\Test.bak 2010-03-24 12:43:33.000 2010-03-24 12:43:33.000 1482.000000
What if you want to know the time the last backup finished for all the databases? You can use the following query to get that info
- WITH LastBackupTaken AS (
- SELECT database_name,
- backup_finish_date,
- RowNumber = ROW_NUMBER() OVER (PARTITION BY database_name
- ORDER BY backup_finish_date DESC)
- FROM msdb.dbo.backupset
- )
- SELECT database_name,backup_finish_date
- FROM LastBackupTaken
- WHERE RowNumber = 1
Here are the results for that query
master 2010-03-24 12:58:03.000 model 2010-03-24 12:58:24.000 msdb 2010-03-24 12:58:15.000 Policy 2010-03-24 09:59:32.000 Test 2010-03-24 12:43:43.000
This last part is also on our wiki, you can find it here Get last backup dates for all databases
*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.