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 순서, 압축 크기, checksum 플래그, 파일 경로를 포함한 백업 체인을 보여 준다.

사용 시점

아침 점검에서, 복원에 필요한 파일을 찾을 때, 감사에서 "마지막 백업은 언제였나"에 답할 때, 또는 log-full 알림 중 로그 백업이 실행 중인지 확인할 때.

주의

msdb는 이 인스턴스가 수행한 백업만 알고 있다. 다른 서버나 다른 AG replica에서 수행한 백업은 보이지 않는다(AG에서는 모든 replica를 확인). 로그 백업의 first_lsn은 이전 백업의 last_lsn과 같아야 하며, 차이가 있으면 체인이 끊어진 것이다. FULL 복구 모델에서 last_log가 NULL이면 로그가 한 번도 백업되지 않은 것이며 계속 커진다. 읽기 전용 쿼리이며 안전함.