Find database restore date in SQL Server
Last week I had a situation to check & confirm some data in one of the master table with current live database with two-week old data so obviously I have to restore two-week or older backup somewhere and check the data of the master table from restored database to current live database.
We have one development server and we used to restore live database there as and when needed so I wanted to check when was the live database restored in development environment last time. If database was restored in last 15 days, I have to take one of the two-week old backup and restore it in development environment. If development database wasn’t restored in last two weeks, I can directly check and compare data of master table and save my few hours. So I have developed one TSQL script which can give me the history of my database restore.
I have used some system table of MSDB database in SQL Server because backup/restore history of databases resides in MSDB database. Here is the list of system table resides in MSDB database which I have used to find database restore date in SQL Server along with its official definition given by Microsoft in BOL.
RestoreHistory: This table is part of MSDB database and contain one row for each restore operation. You can consider this table as a RestoreHistoryMaster.
BackupSet: This is also one of the system table in MSDB database which Contains a row for each backup set. A backup set contains the backup from a single, successful backup operation. RESTORE, RESTORE FILELISTONLY, RESTORE HEADERONLY, and RESTORE VERIFYONLY statements operate on a single backup set within the media set on the specified backup device or devices.
RestoreFile: Contains one row for each restored file, including files restored indirectly by filegroup name. This table is also part of MSDB database.
BackupMediaFamily: Contains one row for each media family. If a media family resides in a mirrored media set, the family has a separate row for each mirror in the media set. This table is stored in the MSDB database.
[sourcecode language=”sql”]USE msdb
WHEN ResHist.Restore_Type = ‘D’ THEN ‘Database’
WHEN ResHist.Restore_Type = ‘F’ THEN ‘File’
WHEN ResHist.Restore_Type = ‘G’ THEN ‘Filegroup’
WHEN ResHist.Restore_Type = ‘I’ THEN ‘Differential’
WHEN ResHist.Restore_Type = ‘L’ THEN ‘Log’
WHEN ResHist.Restore_Type = ‘V’ THEN ‘Verifyonly’
WHEN ResHist.Restore_Type = ‘R’ THEN ‘Revert’
END AS Restore_Type,
bckMedFam.Physical_Device_Name AS Restored_From,
ResFile.Destination_Phys_Name AS Restored_To,
ResHist.User_Name AS Restored_By
BackupSet bckSet ON ResHist.backup_set_id = bckSet.backup_set_id
RestoreFile ResFile ON ResHist.restore_history_id = ResFile.restore_history_id
ROW_NUMBER() over(Partition by media_set_id order by family_Sequence_Number) as rn
bckMedFam.media_set_id = bckSet.media_set_id and bckMedFam.rn=1
— -15 will check for whether database restore in last 15 days or not
— if restore ws done before 15 days, it won’t come in this list
ResHist.restore_date >= DATEADD(dd, -15, GETDATE()) and
–Pass your database name here
If I don’t get any record in above TSQL Script, I am all good and I don’t need to restore database to compare my old master table data with new otherwise I have to spend few hours to find my two-week older backup, transfer it to development server and restore it. sometime, even a small script can save your day. :)
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles .