SQL Server:master.sys.databases中的backup_finish_date到底是什么 [英] SQL Server: What exactly is the backup_finish_date in master.sys.databases

问题描述

使用以下查询查询最后的backup_finish_date时(来自

When querying for the last backup_finish_date with the following query (from SQL Server: how to query when the last transaction log backup has been taken?):

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

我所有数据库的backup_finish_date为null，这适用于恢复模式为BULK_LOGGED，FULL或SIMPLE的DB.

The backup_finish_date for all my databases is null, this is for DBs with a recovery mode of BULK_LOGGED, FULL or SIMPLE.

这是否意味着这些数据库都没有备份其事务日志(如链接问题的标题所建议)?

Does this imply that none of these databases has had their transaction log backed up (as suggested by the title of the linked question)?

推荐答案

是的，您是正确的.如果您的backup_finish_date是null，则表示它从未备份过.但是，可以修改/调节msdb..backupset.您可以检查具有备份类型的数据库的上一次备份.

Yes, You are correct. If your backup_finish_date is null means it was never backed up. However, msdb..backupset can be modified/tempered. You can check last backup of the database with backup type.

SELECT   d.name,
         d.recovery_model_desc,
         b.type, -- type of backup
         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
GROUP BY d.name, d.recovery_model_desc, b.type
ORDER BY backup_finish_date DESC

type Can be:
D = Database OR Full
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
Can be NULL.

请参考 MSDN

这篇关于SQL Server:master.sys.databases中的backup_finish_date到底是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！