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?
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
|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
Tray the query I posted above.
I’d suggest adding
WHERE [rs].[destination_database_name] = 'PRD_102300' (for the DB in question)
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!