Monday, April 8, 2013

Looking in the Backup History

The other day I had a developer request a refresh from production from a date in the past. It seems they were testing a bug that cropped up and needed to replicate the way the system looked on a particular date.

Since this was beyond our local retention policy, we needed to restore the backup file from the virtual tape library.

Time to make friends with the Backup Administrator...

You want to tell your Backup Administrator the name of the server and the file, along with the date of the backup. This last part is slightly tricky. It is fairly typical for the file you are looking for to be on the following days' backup.

For example, if you wrote a SQL Backup file on January 1st, it probably isn't backed up until January 2nd. You may want to spend some time with your Backup Administrators to understand how your backups work and what the procedure is to get a file restored.

So how do you find out what the backup file was called two weeks ago? Query the msdb database, of course.


-- look for a backup filename from the past...

select top 30
s.database_name, s.backup_start_date,
--, s.*
--, m.*
from msdb.dbo.backupset s
join msdb.dbo.backupmediafamily m
on s.media_set_id = m.media_set_id
where s.database_name = 'YOUR_DATABASE_NAME_HERE'
and s.type = 'D'
order by s.backup_start_date desc


This query will return the names of the FULL backup files for the past thirty day. You can modify this query as needed.

Backup History


  1. Nice script, will add it to my repository. thanks.

  2. Hiya Patrick, I'm glad you liked it.