SQL Server: how to query when the last transaction log backup has been taken?
39,477
Solution 1
SELECT d.name,
MAX(b.backup_finish_date) AS backup_finish_date
FROM master.sys.sysdatabases d
LEFT OUTER JOIN msdb..backupset b
ON b.database_name = d.name
AND b.type = 'L'
GROUP BY d.name
ORDER BY backup_finish_date DESC
Solution 2
I recommend using this modified script so you can see which database is in FULL or BULK_LOGGED recovery model and not having any LOG BACKUP.
SELECT d.name,
d.recovery_model_desc,
MAX(b.backup_finish_date) AS backup_finish_date
FROM master.sys.databases d
LEFT OUTER JOIN msdb..backupset b
ON b.database_name = d.name
AND b.type = 'L'
GROUP BY d.name, d.recovery_model_desc
ORDER BY backup_finish_date DESC
Related videos on Youtube
Author by
juur
Updated on March 05, 2020Comments
-
juur over 4 years
I would like to query for all databases (in SQL Server 2008 instance) date when the last transaction log backup has been taken. How to do that? I know that this information is somewhere, but I don't know where.
-
Zach Smith over 7 yearsLooking at the output of this statement, I see NULL for backup_finish_date... what does that mean? stackoverflow.com/questions/40050221/…