Saturday, October 27, 2012

Find last time when transaction log backup


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