SQL Server 彙總歷史備份資訊評估資料庫成長趨勢：msdb.dbo.backupset 實戰分析

規劃儲存空間、申請擴容時，最可靠的依據是歷史備份大小趨勢。本文示範如何查詢 msdb.dbo.backupset 彙總每月備份大小，分析資料庫真實成長曲線，並說明備份歷史保留原則對分析的影響。

核心查詢：按月彙總完整備份大小

SELECT
    [database_name] AS [Database],
    DATEPART(MONTH, [backup_start_date]) AS [Month],
    AVG([backup_size] / 1024.0 / 1024.0) AS [Backup Size MB]
FROM msdb.dbo.backupset
WHERE [database_name] = N'AdventureWorks2012'  -- 修改為目標資料庫
  AND [type] = 'D'                              -- D = 完整備份
GROUP BY [database_name], DATEPART(MONTH, [backup_start_date])
ORDER BY [Month];

查詢結果解讀

• backup_size 為實際寫入備份檔的位元組數 (不含未配置頁面)。
• 資料庫檔案可能 20GB，但完整備份僅 3GB (僅備份已配置頁面)。
• 備份大小趨勢 ≈ 資料真實成長趨勢，適合容量規劃。

結果範例

關鍵影響因素：備份歷史保留原則

SQL Server 2008+ 引入原則型管理，預設建立清理作業：

• 作業名稱：syspolicy_purge_history (每日 02:00 執行)。
• 保留天數：預設 0 = 永不清理 (可在原則「歷史記錄保留天數」修改)。
• 若清理作業已執行或手動清除 backupset，歷史分析將受限。

檢查保留設定

-- 檢視原則保留天數
SELECT * FROM msdb.dbo.syspolicy_policies WHERE name = 'Backup History Retention';

-- 檢視清理作業
EXEC msdb.dbo.sp_help_job @job_name = 'syspolicy_purge_history';

進階分析延伸

• 區分備份類型： type = 'D' (完整)、'I' (差異)、'L' (記錄)。
• 計算成長率： 當月備份大小 / 上月備份大小 - 1。
• 預測未來需求： 套用線性回歸或指數平滑模型。
• 跨資料庫比較： 移除 database_name 篩選，加入 database_name 至 GROUP BY。

最佳實務建議

1. 確保 msdb 定期備份，避免歷史資料遺失。
2. 設定合理的歷史保留天數 (建議 90-365 天) 平衡分析需求與 msdb 體積。
3. 建立例行報表 (SSRS/Power BI/Email) 自動化每月成長趨勢通報。
4. 結合 sys.master_files 當前檔案大小，對照備份趨勢進行擴容規劃。

善用 backupset 歷史資料，能以零成本獲得最真實的資料庫成長基線，支援精準的容量規劃與成本控制。