Find last time when transaction log backup
b.type = 'L' means transaction
log
b.type = 'D' means full
b.type = 'I' means differential
SELECT d.name,
MAX(b.backup_finish_date) AS
backup_finish_date
FROM master.sys.sysdatabases d WITH(NOLOCK)
LEFT OUTER JOIN msdb..backupset b WITH(NOLOCK)
ON b.database_name
= d.name
AND b.type = 'L'
WHERE b.backup_finish_date
IS NOT NULL
GROUP BY d.name
ORDER BY backup_finish_date ASC
