从 msdb 查看备份历史并检查缺失的备份

命令

-- Last FULL / DIFF / LOG per database (never backed up first):
SELECT d.name AS db,
       d.recovery_model_desc,
       MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
       MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_diff,
       MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log,
       DATEDIFF(MINUTE, MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END), GETDATE()) AS log_age_min
FROM   sys.databases d
  LEFT JOIN msdb.dbo.backupset b
         ON b.database_name = d.name AND b.is_copy_only = 0
WHERE  d.name <> N'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY last_full;

-- Chain for one DB over the last 7 days (LSN, size, file):
SELECT b.backup_start_date, b.backup_finish_date, b.type, b.is_copy_only,
       b.first_lsn, b.last_lsn, b.database_backup_lsn,
       CAST(b.backup_size / 1048576.0 AS DECIMAL(12,1))            AS size_mb,
       CAST(b.compressed_backup_size / 1048576.0 AS DECIMAL(12,1)) AS compressed_mb,
       b.has_backup_checksums, mf.physical_device_name
FROM   msdb.dbo.backupset b
  JOIN msdb.dbo.backupmediafamily mf ON mf.media_set_id = b.media_set_id
WHERE  b.database_name = N'Musteri'
  AND  b.backup_start_date >= DATEADD(DAY, -7, GETDATE())
ORDER BY b.backup_start_date;

作用

列出每个数据库最后一次 FULL/DIFF/LOG 备份的时间和日志备份时长；针对单个数据库，列出备份链及其 LSN 序列、压缩后大小、校验和标志和文件路径。

适用场景

用于晨检、查找还原所需的文件、在审计中回答“最后一次备份是什么时候”，或在日志已满告警期间确认日志备份是否在运行。

注意

msdb 只知道本实例执行的备份；在其他服务器或其他 AG 副本上执行的备份不可见（在 AG 中要检查每个副本）。日志备份的 first_lsn 必须等于前一个的 last_lsn；出现缺口说明链已断。在 FULL 恢复模式下 last_log 为 NULL 表示日志从未备份，会持续增长。只读查询；安全。