When was SQL Server Database Restored, the Source and Backup Date

Problem

After restoring a database your users will typically run some queries to verify the data is as expected. However, there are times when your users may question whether the restore was done using the correct backup file. In this tip I will show you how you can identify the file(s) that was used for the restore, when the backup actually occured and when the database was restored.

Solution

The restore information is readily available inside the msdb database, making the solution as easy as a few lines of T-SQL.

When I ask people about how they verify their database restores I often get back a response that includes something similar to the following code:

RESTORE VERIFYONLY FROM DISK = 'G:\dbname.bak'

The above command simply returns this message when successful: "The backup set on file 1 is valid." Is that really useful for your end user that is complaining that the data is not correct? Chances are their complaint is not about if the backup set was valid, but more specifically it is about your selection of the backup file, or the timing of the backup itself.

If the backup was done at the wrong time, or if you restored from the wrong backup file, then the end user may be seeing exactly that problem while reviewing the data. So, how do you provide some proof that you did the restore from the correct backup file? The following script can give you this information.

SELECT 
   [rs].[destination_database_name], 
   [rs].[restore_date], 
   [bs].[backup_start_date], 
   [bs].[backup_finish_date], 
   [bs].[database_name] as [source_database_name], 
   [bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] 
ORDER BY [rs].[restore_date] DESC

The script will return the following result set:

Here is a screenshot of a sample result set returned by the script.

Next Steps

• Take the above code and execute against your instance.
• Keep this script handy next time you want to know when a database was restored, what file it came from and when the backup actually occurred
• Take a look at these other Backup and Recovery tips

Thomas LaRock

Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and former Microsoft Certified Trainer. He has over 20 years’ experience in the IT industry in roles including programmer, developer, analyst, and database administrator.

LaRock has spent much of his career focused on data and database administration, which led to his being chosen as a Technical Evangelist for Confio Software in 2010. While at Confio, his research and experience helped to create the initial versions of the software now known as SolarWinds Database Performance Analyzer. LaRock joined the SolarWinds family through the acquisition of Confio in 2013.

LaRock is also the Immediate Past President of the Professional Association for SQL Server (PASS) and is an avid blogger, author, and technical reviewer for numerous books about SQL Server management. He now focuses his time working with customers to help resolve problems and answer questions regarding database performance tuning and virtualization for SQL Server, Oracle, MySQL, SAP, and DB2, making it his mission to give IT and data professionals longer weekends.