While working with databases, one of the most repeated task are database restore and refresh from production to test or dev environment. I’ve had situations where after a refresh (restore databases), our client would assume the refresh was not done or the refresh was not completed. They were not seeing the data they were expecting. Now how can you prove that you did the refresh from the most recent backup successfully?
The below script will give you all restore details like restore date, which backup was restored, what sources location, and etc.
[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
-By MD Ullah