Check when log backups were last restored

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.