This script is useful if want to check when transaction log backups were last restored to a specific database.
With minor amendments, you can also check other restore types and check for all databases, not just a specific one.
[sql]
/***************************************************************************************
* Checks when a specific database was last restored from a log backup
* restore_type options are listed at bottom of script
* restore_date is useful in alerting/troubleshooting. This example is looking for log
restores within the last day. If no rows are returned, this would be an indication that
log shipping is not working
* destination_database_name can be given a value to check for specific database or
commented out if you want to return info on all databases
****************************************************************************************/
select restore_date, physical_device_name
from msdb..restorehistory as a
inner join msdb..backupset as b on a.backup_set_id = b.backup_set_id
inner join msdb..backupmediaset as c on b.media_set_id = c.media_set_id
inner join msdb..backupmediafamily as d on c.media_set_id = d.media_set_id
where a.destination_database_name = ‘ANALYSIS’ — Comment out this line for all databases
and restore_type = ‘L’ — See comments below for restore_type
and restore_date > DATEADD(d, -1, getdate())
order by restore_date DESC
/*
Restore_type =
D = Database
F = File
G = Filegroup
I = Differential
L = Log
V = Verifyonly
Can be NULL
*/
[/sql]
More info on log shipping is available at msdn.
Leave a Reply