SQL Server – Check Latest Transaction Log Backup

To know what the last transaction log backup of your database was, execute the T-SQL script below.

SELECT

 CONVERT(CHAR(100),​​ SERVERPROPERTY('Servername'))​​ AS​​ ServerName,

 msdb.dbo.backupset.database_name​​ AS​​ DatabaseName,

 MAX(msdb.dbo.backupset.backup_finish_date)​​ AS​​ Last_TLog_Backup_Date

FROM​​ msdb.dbo.backupmediafamily

 INNER​​ JOIN​​ msdb.dbo.backupset​​ ON​​ msdb.dbo.backupmediafamily.media_set_id​​ =​​ msdb.dbo.backupset.media_set_id

WHERE​​ msdb..backupset.type​​ =​​ 'L'

GROUP​​ BY

 msdb.dbo.backupset.database_name

ORDER​​ BY 

 msdb.dbo.backupset.database_name