Saturday, 16 February 2013

Script to get database restore history


Run the script on database instance where you need to find the restore history of all the databases. This provides 4 column result set of:
  • Which database has been restored (DBRestored)
  • When was it restore (RestoreDate)
  • What is the source of the database backup (SourceDB)
  • When was the backup taken (BackupDate)
Snapshot below:




USE msdb ;
SELECT
DISTINCT
        DBRestored = destination_database_name ,
        RestoreDate = restore_date ,
        SourceDB = b.database_name ,
        BackupDate = backup_start_date
FROM    RestoreHistory h
        JOIN MASTER..sysdatabases sd ON sd.name = h.destination_database_name
        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
GROUP BY destination_database_name ,
        restore_date ,
        b.database_name ,
        backup_start_date
ORDER BY RestoreDate DESC
GO

No comments:

Post a Comment