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 バックアップ時刻とログバックアップの経過時間を一覧表示する。1 つのデータベースについては、LSN の連続性、圧縮後サイズ、チェックサムフラグ、ファイルパスを含むバックアップチェーンを表示する。

使用する場面

朝のチェック、リストアに必要なファイルの特定、監査での「最後のバックアップはいつか」への回答、ログ満杯アラート時にログバックアップが実行されているかの確認に。

注意

msdb はこのインスタンスが取得したバックアップしか把握していない。別のサーバーや別の AG レプリカで取得したバックアップは見えない (AG ではすべてのレプリカを確認する)。ログバックアップの first_lsn は直前のバックアップの last_lsn と等しくなければならない。差がある場合はチェーンが途切れている。FULL 復旧モデルで last_log が NULL の場合、ログが一度もバックアップされておらず、増大し続ける。読み取り専用クエリのため安全。