Thursday, October 6, 2011

How to get last restore date and backup file name

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. 

bus.database_name Org_DBName,Restored_To_DBName,Last_Date_Restored, Backup_nameFROMmsdb..backupset busINNER
..restorehistoryINNER JOIN(
SELECT .destination_database_name Restored_To_DBName,

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