SQL Server Logshipping restore status

Following query can be used to check log shipping restore status


SELECT top 10 s3.physical_device_name
 , s1.restore_type
 , s2.first_lsn
 , s2.last_lsn
 , s2.checkpoint_lsn
 , s2.database_backup_lsn
 , s1.restore_date
 , s2.backup_start_date
 , s1.destination_database_name
 , s1.backup_set_id
FROM   msdb..restorehistory as s1 INNER JOIN msdb..backupset as s2
 ON s1.backup_set_id = s2.backup_set_id
 INNER JOIN msdb..backupmediafamily as s3
 ON s2.media_set_id = s3.media_set_id
 where
 s1.destination_database_name ='MyDB' -- the database restored
 and s1.restore_type in('D','L')  -- sl.restore_type in ('D','L') means diff or Transaction Log backups
 order by s1.restore_date desc