It is very typical to frequently have a copy of production database restored on to DEV\QA\TEST Instances, so as to work with more real time data. The below query helps in figuring out details such as when the database was restored,source server of restored database, user who restored it, which backup file was used to restore… etc.
Also, this query is useful to generate a report of all backup files that were restored along with other details while testing your backups by physically restoring them.
NOTE: At this point, the script works correctly if your backup types are Full, Log, Differential.Also, it is a good practice to delete unwanted backup history from MSDB database. So, if the backup\restore history data got purged, we might not see results from some databases.
Select A.destination_database_name as [Restored_Database_Name],
A.user_name as [RestoredBy_User_Name],
A.Restore_Date,A.replace as [Replace],
Case When A.restore_type='D' then 'Full'
When A.restore_type='I' then 'Differential'
When A.restore_type='L' then 'Log' End as [Restore_Type],
B.database_name as [Backup_DatabaseName],
B.database_creation_date as [Backup_DB_Creation_Date],
B.server_name as [Backup_ServerName],
B.user_name as [Backuped_by_User],
B.backup_start_date as [Backup_DB_Start_Date],
B.backup_finish_date as [Backup_DB_Finish_Date],
Case When B.[Type]='D' then 'Full'
When B.[Type]='I' then 'Differential'
When B.[Type]='L' then 'Log' End as [Restore_Type],
D.physical_device_name as [BackupFile],Position as [Backup_File_Position],
D.family_sequence_number as [Split_Backup_Sequence]
from msdb.dbo.restorehistory A
INNER JOIN msdb.dbo.backupset B on A.backup_set_id=B.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily D on D.media_set_id=B.media_set_id
INNER JOIN (Select Max(restore_history_id) as restore_history_id ,
Group by destination_database_name) C on C.destination_database_name=A.destination_database_name
and A.restore_history_id >=C.restore_history_id