Thursday, August 2, 2012

SQL Server : How to Keep Database Restore History

Recently, one of our DBA restored a database on live server, but with old backup accidently. Later on, we have found that it was hard to detect which backup was actually restored. To check, which backups we have created for database we have a perfect script, which you can find over here and here. But unfortunately no such script found anywhere to get restore history.
Here is a script, we have used to create a job, which will fetch restore related log entries from SQL Server Log and will archive it to a user created history table.

Output of history table will be as following.
.

2 comments:

  1. You can use the restorhistory and backup tables in msdb to do this like this:

    SELECT
    destination_database_name,
    server_name AS 'SourceServer',
    database_name AS 'SourceDB',
    physical_device_name,
    type,
    backup_start_date,
    restore_date
    FROM msdb.dbo.restorehistory AS rh
    INNER JOIN msdb.dbo.backupset AS bs
    ON bs.backup_set_id = rh.backup_set_id
    INNER JOIN msdb.dbo.backupmediafamily AS bmf
    ON bmf.media_set_id = bs.media_set_id
    ORDER BY restore_date DESC;

    ReplyDelete
  2. Thanks, Jonthan Kehayias, for sharing such a cleanly written script for restor history.

    But only problem with this script is that physical_device_name returns, path, where backup was taken, not from where it was restored.

    ReplyDelete

All suggestions are welcome