This script is with a small modification to include the backup file name from the article published in
This script will output the Orignal DB Name, Destination DB Name, Last Restore date and Backup file name for the databases in a SQL Server.
SELECTbus.database_name Org_DBName,Restored_To_DBName,Last_Date_Restored,bus.name Backup_nameFROMmsdb..backupset busINNER
( JOINSELECTbackup_set_id,Restored_To_DBName,Last_Date_Restored
FROMmsdb
..restorehistoryINNER JOIN(
rhSELECT .destination_database_name Restored_To_DBName,
msdb
Max(rh.restore_date) Last_Date_RestoredFROM ..restorehistory rhGROUP BYrh
.destination_database_name) AS InnerRestONdestination_database_name = Restored_To_DBName ANDrestore_date = Last_Date_Restored) As RestDataONbus
You get a specific database just add a where clause against the appropriate field. It makes use of the restorehistory and backupset tables in the MSDB database..backup_set_id = RestData.backup_set_id
