17

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.

juur
  • 5,633
  • 10
  • 32
  • 36

2 Answers2

29
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
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
5

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
Paul B.
  • 2,394
  • 27
  • 47
PollusB
  • 1,726
  • 2
  • 22
  • 31
  • Looking at the output of this statement, I see NULL for backup_finish_date... what does that mean? http://stackoverflow.com/questions/40050221/sql-server-what-exactly-is-the-backup-finish-date-in-master-sys-databases – Zach Smith Oct 14 '16 at 18:56