When was this database restored?

4

July 27, 2016 by Kenneth Fisher

I frequently need to know where backups went and I restore those backups for operational recovery on a regular basis. Would you believe in 20+ years as a DBA I can count the number of database restores for a disaster on my fingers? (Which is good because taking off your shoes at the office is considered bad form.) In that same 20+ years I’ve needed to know when a database was last restored exactly once. Still, it’s always nice to know where information is. In this particular case rather than checking BOL I queried msdb.sys.all_objects for anything like ‘%restore%’ and turned up dbo.restorehistory.

SELECT * FROM dbo.restorehistory

RestoreHistory

4 thoughts on “When was this database restored?

  1. Kristen says:

    I have a script which will display the Restore History for databases. It helps if the Backup Comment includes enough details about the original backup to enable the file to be identified, but failing that the original database and server names, and the backup start date/time, help.

    SELECT DISTINCT TOP 100
    	RH.destination_database_name,
     	RF.file_number,
    	RH.restore_date,
    	RF.destination_phys_drive,
    	RF.destination_phys_name,
    	[Backup Set Name]=BS.name,
    	BS.description,
    	BS.database_creation_date,
    	BS.backup_start_date,
    	BS.database_name,
    	BS.server_name,
    	RH.restore_type,
    	RH.replace,
    	RH.stop_at
    FROM	msdb.dbo.restorehistory AS RH
    	LEFT OUTER JOIN msdb.dbo.restorefile AS RF
    		ON RF.restore_history_id = RH.restore_history_id
    	LEFT OUTER JOIN msdb.dbo.restorefilegroup AS RG
    		ON RG.restore_history_id = RH.restore_history_id
    	LEFT OUTER JOIN msdb.dbo.backupset AS BS
    		ON BS.backup_set_id = RH.backup_set_id
    WHERE	1=1
    -- 	AND RH.destination_database_name = N'MyDatabaseName'	-- SELECT DB_NAME()
    	-- restore_type : D=Full, I=Differential, L=Log, G=FileGroup, V=VerifyOnly
    -- 	AND RH.restore_type = 'D'
    ORDER BY RH.restore_date DESC,
    	RF.file_number DESC
  2. […] on a SQL Server. You can even specify -Since if you’d like to filter by date. Thanks to Kenneth Fisher for the […]

  3. […] getting backup history earlier but now we talk about Get-DbaRestoreHistory a command inspired by Kenneth Fishers blog post to show when a database was restored and which file was […]

Leave a Reply

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,134 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: