DB SQL help

Can anyone tell me if I could see in SQL if a DB had been restored?

To confirm that it was done? Or forensically, to see if it was done?

1 Like

The following seems to work:

SELECT 
   [rs].[destination_database_name], 
   [rs].[restore_date], 
   [bs].[backup_start_date], 
   [bs].[backup_finish_date], 
   [bs].[database_name] as [source_database_name], 
   [bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] 
ORDER BY [rs].[restore_date] DESC

On my Test server I get

destination_database_name restore_date backup_start_date backup_finish_date source_database_name backup_file_used_for_restore
PRD_102300 2019-11-11 08:55:51.323 2019-11-11 08:51:13.000 2019-11-11 08:53:36.000 MC-UAT \usdatdbs00001\h$\BACKUP\2019-11-11 - MC-UAT for 10.2.300.BAK
PRD_102300 2019-10-21 09:54:13.033 2019-10-21 09:38:56.000 2019-10-21 09:41:00.000 MC-UAT \usdatdbs00001\h$\BACKUP\UAT to copy to 10.2 - 2019-10-21b.BAK
PRD_102300 2019-10-21 09:41:27.523 2019-10-21 09:38:56.000 2019-10-21 09:41:00.000 MC-UAT \usdatdbs00001\h$\BACKUP\UAT to copy to 10.2 - 2019-10-21b.BAK
PRD_102300 2019-10-21 09:08:04.913 2019-10-21 09:03:20.000 2019-10-21 09:05:40.000 MC-UAT \usdatdbs00001\h$\BACKUP\UAT to copy to 10.2 - 2019-10-21a.BAK
PRD_102300 2019-10-01 09:54:49.663 2019-10-01 09:26:28.000 2019-10-01 09:27:38.000 MC-UAT \usdatdbs00001\h$\BACKUP\UAT to copy to 10.2.BAK

to see if it was done. I think someone did something they weren’t suppose too… UGH

So was that top line a restore??

Tray the query I posted above.

I’d suggest adding
WHERE [rs].[destination_database_name] = 'PRD_102300' (for the DB in question)

1 Like

Yes. In the output I posted that was a restore from our 10.1.400 system (We’re still in the process of an upgrade to 10.2.300)

FTR - I’m not the author of that SQL code. I know just enough SQL to be REALLY, REALLY, dangerous!

1 Like