When was this database restored?


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


6 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.

    	[Backup Set Name]=BS.name,
    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 […]

  4. […] for what LOADHISTORY actually does? It causes you to write an entry to the restore history table. You can tell which record this is because the restore_type is set to a V. Really, the only […]

  5. […] weren’t correctly re-applied afterwards. Hard to prove but easy to disprove by looking at the restore history. Hopefully you scripted out the permissions ahead of time or at least took a backup before doing […]

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 )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

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

Join 3,755 other subscribers

Follow me on Twitter

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