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.
/*************************************************************************************** * 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 */
More info on log shipping is available at msdn.
