Archive for the ‘Database Restores’ Category

How to find the recent restore information for a database or databases?

Have you ever wondered when was the last time a specific database or databases were restored and by whom?  This critical piece of information will help you to follow up and understand the reason behind the restore(s).

The following script was originally posted by Thomas LaRock on www.mssqltips.com. http://www.mssqltips.com/sqlservertip/1724/when-was-the-last-time-your-sql-server-database-was-restored/ I have modified it to add the “Restore Option” and “Recovery Option” columns.  Be aware of the @dbname and the @days variables. @dbname is set to NULL as the default and will query all databases that had restores for the days specified in the @days variable. You can specify the database name to be queried and also the number of days to query by simply keying the values in these two corresponding variables.

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],
 CASE WHEN rsh.replace = 1 THEN 'With Replace'
	WHEN rsh.replace = 0 THEN 'Not Specified'
 END AS [Restore Option],
 CASE WHEN rsh.recovery = 1 THEN 'With Recovery'
	WHEN rsh.recovery = 0 THEN 'With Norecovery'
	WHEN rsh.recovery is null THEN 'With Recovery'
 END AS [Recovery Option],
 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 DESC
GO

Here’s an example what the output looks like:
Database Restores History

REFERENCES:

When was the last time your SQL Server database was restored?
http://www.mssqltips.com/sqlservertip/1724/when-was-the-last-time-your-sql-server-database-was-restored/

msdb.dbo.restorehistory
http://msdn.microsoft.com/en-us/library/ms187408(v=sql.90).aspx

msdb.dbo.backupset
http://technet.microsoft.com/en-us/library/ms186299(v=sql.90).aspx

msdb.dbo.backupmediafamily
http://technet.microsoft.com/en-us/library/ms190284(v=sql.90).aspx