SQL Server：如何查询最后一个事务日志备份的时间？

Question

我想查询所有数据库（在SQL Server 2008实例中）上一次事务日志备份的时间。如何操作？我知道这个信息肯定有，但是不知道在哪里。

- juur

Answer 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

- Martin Smith

Answer 2

我建议使用这个修改过的脚本，这样您就可以看到哪个数据库处于FULL或BULK_LOGGED恢复模型，并且没有任何日志备份。

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

- PollusB

---

看到这个语句的输出，我看到备份完成日期的值为NULL...那是什么意思？http://stackoverflow.com/questions/40050221/sql-server-what-exactly-is-the-backup-finish-date-in-master-sys-databases - Zach Smith