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

T-SQL
1
2
CREATE DATABASE test
GO
CREATE DATABASE test
GO

Create some sample data

T-SQL
1
2
3
4
5
6
USE test
GO
 
SELECT * INTO TestTable
FROM master..spt_values
GO
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.

T-SQL
1
2
3
4
5
6
7
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
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.

T-SQL
1
2
3
4
5
6
7
8
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
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

<strong>physical_device_name	backup_start_date	backup_finish_date	BackupSizeKB</strong>
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
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
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