Log Shipping – NORECOVERY vs. STANDBY mode

Here is the script that will help you the database backup and restore histories

SELECT  TOP 10   b.database_name
         , BMF.physical_device_name as BackupFileName      
         , B.backup_finish_date as BackupDate
         , B.backup_size/1024.0/1024.0 AS BackupSizeMB
FROM
         msdb.dbo.backupset B
         JOIN msdb.dbo.backupmediafamily BMF ON B.media_set_id = BMF.media_set_id
--WHERE database_name = '<YourDatabaseName>'
ORDER BY B.backup_finish_date DESC

Restore history

SELECT TOP 10
         RH.destination_database_name AS [Database],
         RH.user_name AS [Restored By], 
         RH.restore_date AS [Restore Started],
         BMF.physical_device_name AS [Restored From], 
         RF.destination_phys_name AS [Restored To],
         RH.*
FROM
         msdb.dbo.restorehistory RH
         INNER JOIN msdb.dbo.backupset BS ON RH.backup_set_id = BS.backup_set_id
         INNER JOIN msdb.dbo.restorefile RF ON RH.restore_history_id = RF.restore_history_id
         INNER JOIN msdb.dbo.backupmediafamily BMF ON BMF.media_set_id = BS.media_set_id
--WHERE destination_database_name = '<DatabaseName>'
ORDER BY RH.restore_history_id DES