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