#0387 – SQL Server – Script to find source backups used in a database restore

In our development environment, we frequently refresh our databases from various baseline backup sets for each sprint of the product development cycle. During one such sprint, we had a confusion as  to which backup was used to refresh the database.

Microsoft SQL Server tracks every possible detail about each backup and restore operation in the [msdb] system database, and unless explicitly cleared, this history is available effectively forever.

I therefore developed a quick query on the [msdb].[dbo].[restorehistory] and  [msdb].[dbo].[backupset] tables of the msdb database to fetch the following information:

  1. Name of the restored database
  2. Date of the restore
  3. User who restored the DB
  4. Type of the restore (full/differential/transaction log)
  5. Was the restore done over an existing database (i.e. “replace” operation)
  6. Name of the backup set
  7. User who performed the backup
  8. Database creation date as logged  during the backup
  9. Backup Start & End times
  10. Type of the backup (full/differential/transaction log)
  11. Machine where the backup was taken

We were able to confirm that the restored backup was indeed a correct one and a documentation discrepancy was the root cause of the confusion. I thought the script would be useful for the entire community in fulfilling such adhoc verification tasks. I trust you will find it useful.

(FYI – In the script, I have kept additional joins to the [msdb].[dbo].[backupfile] and [msdb].[dbo].[restorefile] to get the information of the individual  database files as well. You can uncomment them as necessary.)

Here is the script below fetching information for the [AdventureWorks2012] database:

USE msdb;
SELECT BackupRestoreHistory.[destination_database_name] AS RestoredDatabaseName,
       BackupRestoreHistory.[restore_date] AS BackupRestoreDate,
       BackupRestoreHistory.[user_name] AS BackupRestoredByUser,
       CASE BackupRestoreHistory.[restore_type] WHEN 'D' THEN 'Database'
                                                WHEN 'F' THEN 'File'
                                                WHEN 'G' THEN 'Filegroup'
                                                WHEN 'I' THEN 'Differential'
                                                WHEN 'L' THEN 'Transaction Log'
                                                WHEN 'V' THEN 'Verify Only'
                                                ELSE 'Information Not Available'
       END AS RestoreType,
       BackupRestoreHistory.[replace] AS IsDatabaseReplacedDuringRestore,
       --RestoreFileInformation.[destination_phys_name] AS RestoredFileName,
       --BackupFileInformation.[physical_name] AS SourceBackupFileName,
       BackupSetInformation.[name] AS BackupSetName,
       BackupSetInformation.[user_name] AS BackupDoneByUser,
       BackupSetInformation.[database_name] AS DatabaseNameWhenBackupWasTaken,
       BackupSetInformation.[database_creation_date] AS DatabaseCreationDateRecordedAtBackup,
       BackupSetInformation.[backup_start_date] AS BackupStartDateTime,
       BackupSetInformation.[backup_finish_date] AS BackupEndDateTime,
       CASE BackupSetInformation.[type] WHEN 'D' THEN 'Database'
                                        WHEN 'I' THEN 'Differential database'
                                        WHEN 'L' THEN 'Log'
                                        WHEN 'F' THEN 'File or filegroup'
                                        WHEN 'G' THEN 'Differential file'
                                        WHEN 'P' THEN 'Partial'
                                        WHEN 'Q' THEN 'Differential partial'
                                        ELSE 'Information Not Available'
        END AS BackupType,
        BackupSetInformation.[server_name] AS ServerWhereBackupWasTaken
FROM msdb.dbo.restorehistory AS BackupRestoreHistory
LEFT OUTER JOIN msdb.dbo.backupset AS BackupSetInformation ON BackupRestoreHistory.backup_set_id = BackupSetInformation.backup_set_id 
--LEFT OUTER JOIN msdb.dbo.restorefile AS RestoreFileInformation ON BackupRestoreHistory.[restore_history_id] = RestoreFileInformation.[restore_history_id]
--LEFT OUTER JOIN msdb.dbo.backupfile AS BackupFileInformation ON BackupSetInformation.[backup_set_id] = BackupFileInformation.[backup_set_id]
WHERE BackupRestoreHistory.[destination_database_name] = 'AdventureWorks2012';

Backup information corresponding to a restored version of the AdventureWorks2012 database

Until we meet next time,

Be courteous. Drive responsibly.


Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s