#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:

Name of the restored database
Date of the restore
User who restored the DB
Type of the restore (full/differential/transaction log)
Was the restore done over an existing database (i.e. “replace” operation)
Name of the backup set
User who performed the backup
Database creation date as logged during the backup
Backup Start & End times
Type of the backup (full/differential/transaction log)
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;
GO
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';
GO