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:JunkdrawTest.bak' WITH NOFORMAT, NOINIT
,SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [Test] TO  DISK = N'C:JunkdrawTest2.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:JunkdrawTest2.bak 2010-03-24 12:43:43.000 2010-03-24 12:43:43.000 1483.000000
C:JunkdrawTest.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