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.
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:
Column Name |
Description |
destination_database_name | The name of the database that has been restored. |
restore_date | The time at which the restore command was started. |
backup_start_date | The time at which the backup command was started. |
backup_finish_date | The time at which the backup command completed. |
source_database_name | The name of the database after it was restored. |
backup_file_used_for_restore | The file(s) that the restore used in the RESTORE command. |