SQL Server 備份歷史深度挖掘：msdb 系統視圖查詢與自動化報表實戰

前言：備份不僅是備份，更是可觀測性

備份完成不代表任務結束。DBA 需回答：哪些資料庫漏備？記錄備份鏈是否中斷？備份耗時趨勢？壓縮率？離場備份是否落地？所有答案都藏在 `msdb` 系統資料庫的備份歷史視圖中。本文系統梳理核心視圖、提供實戰查詢腳本、並延伸自動化報表與告警方案。

一、核心系統視圖解析

二、實戰查詢腳本庫

1. 近 7 天所有資料庫備份總覽 (含壓縮率、耗時)

SELECT
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS ServerName,
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS DurationSec,
    CASE bs.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Diff'
        WHEN 'L' THEN 'Log'
        ELSE bs.type
    END AS BackupType,
    bs.backup_size / 1024.0 / 1024.0 AS BackupSizeMB,
    bs.compressed_backup_size / 1024.0 / 1024.0 AS CompressedSizeMB,
    CASE WHEN bs.backup_size > 0
         THEN ROUND(100.0 * (1 - bs.compressed_backup_size / bs.backup_size), 2)
         ELSE 0 END AS CompressionPct,
    bmf.physical_device_name,
    bs.name AS BackupSetName,
    bs.description
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.backup_start_date >= DATEADD(DAY, -7, GETUTCDATE())
  AND bs.type IN ('D','I','L')
ORDER BY bs.database_name, bs.backup_finish_date DESC;

2. 偵測漏備資料庫 (超過 24h 無完整備份 / 4h 無記錄備份)

WITH LastBackups AS (
    SELECT
        database_name,
        MAX(CASE WHEN type = 'D' THEN backup_finish_date END) AS LastFull,
        MAX(CASE WHEN type = 'L' THEN backup_finish_date END) AS LastLog
    FROM msdb.dbo.backupset
    WHERE backup_finish_date >= DATEADD(DAY, -7, GETUTCDATE())
    GROUP BY database_name
),
AllDBs AS (
    SELECT name AS database_name FROM sys.databases WHERE state_desc = 'ONLINE' AND name NOT IN ('tempdb')
)
SELECT
    d.database_name,
    lb.LastFull,
    lb.LastLog,
    CASE WHEN lb.LastFull IS NULL OR lb.LastFull < DATEADD(HOUR, -24, GETUTCDATE()) THEN '⚠ Full Backup Missing/Stale' ELSE 'OK' END AS FullStatus,
    CASE WHEN lb.LastLog IS NULL OR lb.LastLog < DATEADD(HOUR, -4, GETUTCDATE()) THEN '⚠ Log Backup Missing/Stale' ELSE 'OK' END AS LogStatus
FROM AllDBs d
LEFT JOIN LastBackups lb ON d.database_name = lb.database_name
WHERE lb.LastFull IS NULL OR lb.LastFull < DATEADD(HOUR, -24, GETUTCDATE())
   OR lb.LastLog IS NULL OR lb.LastLog < DATEADD(HOUR, -4, GETUTCDATE())
ORDER BY d.database_name;

3. 備份鏈完整性檢查 (LSN 連續性)

-- 檢查指定資料庫記錄備份鏈是否中斷
DECLARE @DBName SYSNAME = 'YourDB';
WITH LogBackups AS (
    SELECT
        backup_set_id,
        first_lsn,
        last_lsn,
        backup_finish_date,
        LAG(last_lsn) OVER (ORDER BY backup_finish_date) AS PrevLastLSN
    FROM msdb.dbo.backupset
    WHERE database_name = @DBName AND type = 'L'
)
SELECT
    backup_finish_date,
    first_lsn,
    last_lsn,
    PrevLastLSN,
    CASE WHEN first_lsn = PrevLastLSN THEN '✅ Continuous' ELSE '❌ BROKEN CHAIN' END AS ChainStatus
FROM LogBackups
ORDER BY backup_finish_date;

4. 備份效能趨勢分析 (MB/sec)

SELECT
    database_name,
    CAST(backup_finish_date AS DATE) AS BackupDate,
    AVG(backup_size / 1024.0 / 1024.0 / NULLIF(DATEDIFF(SECOND, backup_start_date, backup_finish_date), 0)) AS AvgMBps,
    COUNT(*) AS BackupCount,
    SUM(backup_size) / 1024.0 / 1024.0 AS TotalMB
FROM msdb.dbo.backupset
WHERE type = 'D' AND backup_finish_date >= DATEADD(DAY, -30, GETUTCDATE())
GROUP BY database_name, CAST(backup_finish_date AS DATE)
ORDER BY database_name, BackupDate DESC;

5. 異地備份落地驗證 (檢查 physical_device_name 路徑)

SELECT DISTINCT
    bs.database_name,
    bmf.physical_device_name,
    CASE
        WHEN bmf.physical_device_name LIKE '\\\\%' THEN 'Network Share'
        WHEN bmf.physical_device_name LIKE 'https:%' THEN 'Azure Blob / S3'
        WHEN bmf.physical_device_name LIKE 'D:%' OR bmf.physical_device_name LIKE 'E:%' THEN 'Local Disk (Non-C)'
        ELSE 'C Drive / Other'
    END AS StorageTier,
    MAX(bs.backup_finish_date) AS LatestBackup
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.backup_finish_date >= DATEADD(DAY, -1, GETUTCDATE())
GROUP BY bs.database_name, bmf.physical_device_name
ORDER BY bs.database_name;

三、自動化報表與告警架構

1. SSRS / Power BI 報表設計

• 資料集：上述查詢封裝為預存程序 `dbo.usp_BackupReport_*`
• 參數：`@DaysBack`、`@ServerList` (多實例聯合查詢 via Linked Server)
• 視覺化：備份成功率儀表、耗時趨勢線、壓縮率分布、漏備清單表格
• 訂閱：每日晨間 08:00 郵件推送給 DBA 團隊與管理層

2. SQL Agent 告警作業 (每小時執行)

-- 檢查漏備、鏈中斷、備份失敗 (msdb..sysjobhistory 關聯)
EXEC msdb.dbo.sp_add_job @job_name = N'Backup_Health_Check_Hourly';
EXEC msdb.dbo.sp_add_jobstep @job_name = N'Backup_Health_Check_Hourly',
    @step_name = N'Check_Missing_Backups',
    @subsystem = N'TSQL',
    @command = N'EXEC dbo.usp_CheckMissingBackups;', -- 內含發送郵件邏輯
    @on_success_action = 3; -- Go to next step
-- 排程：每小時

3. 關鍵指標儀表板 (Grafana + Prometheus / Azure Monitor)

四、清理與維護：別讓 msdb 爆滿

-- 保留 90 天備份歷史，其餘清理 (需謹慎，建議先備份 msdb)
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = DATEADD(DAY, -90, GETUTCDATE());

-- 或僅清理特定資料庫
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = 'OldDB';

-- 索引維護：msdb 表同樣需要重組/重建
ALTER INDEX ALL ON msdb.dbo.backupset REBUILD;
ALTER INDEX ALL ON msdb.dbo.backupmediafamily REBUILD;

五、總結

備份歷史是資料庫可靠性的「黑盒子」。善用 `msdb` 核心視圖，建立「查詢 → 報表 → 告警 → 清理」閉環，讓備份狀態從「黑盒」變「白盒」，從「事後查詢」變「事前預防」。每一個漏備偵測、每一條鏈中斷告警，都在為資料安全買單。