On a daily basis, we often have to restore a database to a different server and relay the information back to the business owner to validate the task's completion. To properly restore the database, the owner must know who, when and what backup is used. We can use the script below to gather all the information necessary to properly restore the database.

Script:–

SQL 2008 and 2008R2/2012:–

DECLARE @dbname sysname, @days int SET @dbname = NULL –substitute for whatever database name you want SET @days = -30 –previous number of days, script will default to 30 SELECT rsh.destination_database_name AS [Database], rsh.user_name AS [Restored By], CASE WHEN rsh.restore_type = ‘D’ THEN ‘Database’ WHEN rsh.restore_type = ‘F’ THEN ‘File’ WHEN rsh.restore_type = ‘G’ THEN ‘Filegroup’ WHEN rsh.restore_type = ‘I’ THEN ‘Differential’ WHEN rsh.restore_type = ‘L’ THEN ‘Log’ WHEN rsh.restore_type = ‘V’ THEN ‘Verifyonly’ WHEN rsh.restore_type = ‘R’ THEN ‘Revert’ ELSE rsh.restore_type END AS [Restore Type], rsh.restore_date AS [Restore Started], bmf.physical_device_name AS [Restored From], rf.destination_phys_name AS [Restored To] FROM msdb.dbo.restorehistory rsh INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) –want to search for previous days AND destination_database_name = ISNULL(@dbname, destination_database_name) –if no dbname, then return all ORDER BY rsh.restore_history_id  

SQL 2005 script:

DECLARE @dbname sysname, @days int SET @dbname = NULL –substitute for whatever database name you want SET @days = -30 –previous number of days, script will default to 30 SELECT rsh.destination_database_name AS [Database], rsh.user_name AS [Restored By], CASE WHEN rsh.restore_type = ‘D’ THEN ‘Database’ WHEN rsh.restore_type = ‘F’ THEN ‘File’ WHEN rsh.restore_type = ‘G’ THEN ‘Filegroup’ WHEN rsh.restore_type = ‘I’ THEN ‘Differential’ WHEN rsh.restore_type = ‘L’ THEN ‘Log’ WHEN rsh.restore_type = ‘V’ THEN ‘Verifyonly’ WHEN rsh.restore_type = ‘R’ THEN ‘Revert’ ELSE rsh.restore_type END AS [Restore Type], rsh.restore_date AS [Restore Started], bmf.physical_device_name AS [Restored From], rf.destination_phys_name AS [Restored To] FROM msdb.dbo.restorehistory rsh INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) –want to search for previous days AND destination_database_name = ISNULL(@dbname, destination_database_name) –if no dbname, then return all ORDER BY rsh.restore_history