T-SQL – Finding Database Restore History
Being an SQL Server DBA, it is very important to retrieve Database Restore History, in case you are getting issues after database restoration.
If you want to track down Restoration date time and Backup file date time which was used to restore the database, then below script would be a great help.
DBRestored = destination_database_name,
RestoreDate = restore_date,
SourceDB = b.database_name,
SourceFile = physical_name,
BackupDate = backup_start_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