It’s a very common practice every DBA verifies where restored Database is as per expected or not, and there may be a question where restored database is restored from proper backup or not. Here is a query which will show how we can identify the file(s) that was used for the restore, when the backup actually occurred and when the database was restored.
SELECT RH.[destination_database_name] Destination_DB_Name, RH.[restore_date] Restore_Date, BKS.[backup_start_date] Backup_StartDateTime, BKS.[backup_finish_date] Backup_FinishDateTime,
BKS.[database_name] as [Source_Database_Name], BMF.physical_device_name] as [Backup_file_used_for_Restore],
RF.file_number File_number,RF.destination_phys_drive Restore_TragetDrive,
RF.destination_phys_name Restore_TragetFileName
FROM msdb..restorehistory RH
INNER JOIN msdb..restorefile RF on RH.restore_history_id = RF.restore_history_id
INNER JOIN msdb..backupset BKS ON RH.[backup_set_id] = BKS.[backup_set_id]
INNER JOIN msdb..backupmediafamily BMF ON BKS.[media_set_id] = BMF.[media_set_id]
ORDER BY RH.[restore_date] DESC
Please comment if any suggestion or modification J
WikiDBA 