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.
.
You can use the restorhistory and backup tables in msdb to do this like this:
ReplyDeleteSELECT
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;
Thanks, Jonthan Kehayias, for sharing such a cleanly written script for restor history.
ReplyDeleteBut only problem with this script is that physical_device_name returns, path, where backup was taken, not from where it was restored.