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
Until we meet next time,
Be courteous. Drive responsibly.