6 thoughts on “When was this database restored?”

1. Kristen says:

   August 8, 2016 at 8:48 AM

   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

   Reply
   • Kenneth Fisher says:

     August 8, 2016 at 1:29 PM

     Nice! I love creating scripts like that. I’ll have to add that one to my bag of tricks.

     Reply
2. we’re now releasing monthly! here’s september’s batch. – dbatools says:

   September 26, 2016 at 8:21 AM

   […] on a SQL Server. You can even specify -Since if you’d like to filter by date. Thanks to Kenneth Fisher for the […]

   Reply
3. dbatools at #SQLSatDublin | SQL DBA with A Beard says:

   June 21, 2017 at 3:29 AM

   […] 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 […]

   Reply
4. RESTORE VERIFYONLY what does WITH LOADHISTORY do? | SQL Studies says:

   July 18, 2018 at 8:01 AM

   […] 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 […]

   Reply
5. Why are my permissions missing? | SQL Studies says:

   June 22, 2021 at 8:00 AM

   […] 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 […]

   Reply

Leave a Reply Cancel reply

Enter your comment here...

Fill in your details below or click an icon to log in:

Email (required) (Address never made public)

Name (required)

Website

You are commenting using your WordPress.com account. ( Log Out /  Change )

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

Cancel

Connecting to %s

Notify me of new comments via email.

Notify me of new posts via email.

Δ

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