How to check database restore history and backup file used for restore in SQL Server?
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.dbo.restorehistory rs
INNER JOIN msdb.dbo.backupset bs ON rs.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
ORDER BY rs.restore_date DESC;