Users: 15,000+ | Page Views: 50,000+ | PPS: 2.5
TechDevOps.com
Explore Tools
{Experts in Microsoft SQL Server Operations, Performance, HA/DR, Data Migrations & Cloud Computing}



T-SQL - Get Transaction Log Restore History on the Secondary Database Server for any Log Shipped Database(s)
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2018-10-31








T-SQL:

USE MSDB
GO
SELECT
reh.Destination_Database_Name, reh.Restore_Type, reh.User_Name, reh.Restore_Date,
bas.First_LSN, bas.Last_LSN, bas.Backup_Start_Date, bas.Backup_Finish_Date,
bmf.Physical_Device_Name, bas.Server_Name, bas.Recovery_Model, bas.Compressed_Backup_Size,
bms.Is_Compressed, bas.Compatibility_Level
FROM RESTOREHISTORY reh WITH (nolock)
Inner Join msdb.dbo.backupset bas on reh.backup_set_id = bas.backup_set_id
Inner Join msdb.dbo.backupmediaset bms on bas.backup_set_id = bms.media_set_id
Inner Join msdb.dbo.backupmediafamily bmf on bas.media_set_id = bmf.media_set_id
WHERE reh.DESTINATION_DATABASE_NAME = 'NYC'
ORDER BY reh.RESTORE_DATE DESC