How to: Find Database Restore History

The following query will help you to track back when the database was restore and which backed up file was used to restore for the particular database.

USE msdb;
GO
SELECT
    DBRestored = destination_database_name,
    SourceDB = b.database_name,
    SourceFile = physical_name,
    BackupDate = backup_start_date,
    RestoreDate = restore_date
FROM RestoreHistory h
INNER JOIN BackupSet b
 ON h.backup_set_id = b.backup_set_id
INNER JOIN BackupFile f
 ON f.backup_set_id = b.backup_set_id
ORDER BY RestoreDate
GO