SQL Server 資料庫備份四大法：維護計畫、T-SQL 作業、xp_cmdshell、PowerShell 完整比較

生產環境備份策略四大主流實作方式完整比較：GUI 維護計畫、T-SQL Agent 作業、xp_cmdshell 命令列、PowerShell + sqlcmd，含完整腳本、優缺點分析與選型建議。

方法一：維護計畫 (GUI、零程式碼、適合中小型環境)

• 拖曳「備份資料庫工作」+「清除維護工作」。
• **關鍵設定**：
  • 資料庫選擇：**所有使用者資料庫**（自動包含新增資料庫）。
  • 勾選 **驗證備份完整性** + **壓縮備份**。
  • 備份路徑**避開磁碟根目錄**，建立專用資料夾。
• 優點：視覺化、易維護、內建清理、通知；缺點：彈性較低、大型環境管理繁瑣、SSIS 套件部署依賴。

方法二：T-SQL + Agent 作業 (核心 DBA 技能、高彈性、無外部相依)

單一資料庫動態檔名 + 保留天數刪除

DECLARE @filename VARCHAR(500), @date DATETIME = GETDATE(), @old_date DATETIME = DATEADD(DAY, -5, GETDATE());
SET @filename = 'E:\Backup\DBName-' + CONVERT(CHAR(8), @date, 112) + '.BAK';
BACKUP DATABASE [DBName] TO DISK = @filename WITH COMPRESSION, CHECKSUM;
EXEC master.dbo.xp_delete_file 0, N'E:\Backup', N'bak', @old_date, 1;  -- 刪除 5 天前 .bak

多資料庫游標批次備份

DECLARE @name SYSNAME, @path NVARCHAR(260) = N'E:\Backup\', @date CHAR(8) = CONVERT(CHAR(8), GETDATE(), 112);
DECLARE cur CURSOR FOR SELECT name FROM sys.databases WHERE name NOT IN ('master','tempdb','model','msdb') AND state_desc = 'ONLINE';
OPEN cur; FETCH NEXT FROM cur INTO @name;
WHILE @@FETCH_STATUS = 0 BEGIN
    DECLARE @fn NVARCHAR(500) = @path + @name + '_' + @date + '.bak';
    BACKUP DATABASE @name TO DISK = @fn WITH COMPRESSION, CHECKSUM;
    FETCH NEXT FROM cur INTO @name;
END
CLOSE cur; DEALLOCATE cur;
-- 清理邏輯同前

• 優點：完全可控、可版控、易整合監控、無 xp_cmdshell 風險；缺點：需 T-SQL 技能、清理邏輯自行實作。

方法三：xp_cmdshell + 命令列 (舊版相容、可直接呼叫外部工具)

前置：啟用 xp_cmdshell

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

備份腳本 (forfiles 清理 + 動態產生 BACKUP 陳述式)

DECLARE @path VARCHAR(50) = 'E:\Backup', @ddate CHAR(8) = CONVERT(CHAR(8), GETDATE(), 112), @sql NVARCHAR(MAX);
-- 清理 1 天前
SET @sql = 'xp_cmdshell ''forfiles /p "' + @path + '" /d -1 /m *.bak /c "cmd /c del /f @file"'''; EXEC (@sql);
-- 產生並執行所有使用者資料庫備份
SET @sql = ''; SELECT @sql = @sql + 'BACKUP DATABASE [' + name + '] TO DISK = ''' + @path + '\' + REPLACE(name,'.','') + @ddate + '.bak'' WITH COMPRESSION; ' FROM master..sysdatabases WHERE name NOT IN ('master','tempdb','model','msdb');
EXEC (@sql);

• 高風險：xp_cmdshell 需 sysadmin、易遭注入、稽核困難。**生產環境極不建議**，若必須用請配合 Proxy 帳號、嚴格防火牆、啟用即關。

方法四：PowerShell + sqlcmd (現代化、功能豐富、跨平台)

邏輯：PS 呼叫 sqlcmd 執行預存程序/腳本

# backup.ps1
$dbs = @('DB1','DB2','DB3')
foreach ($db in $dbs) {
    $fn = "E:\Backup\$db\_$(Get-Date -Format 'yyyyMMdd_HHmmss').bak"
    & sqlcmd -S localhost -E -Q "BACKUP DATABASE [$db] TO DISK = N'$fn' WITH COMPRESSION, CHECKSUM"
    if ($LASTEXITCODE -ne 0) { Write-Error "$db 備份失敗"; Send-MailMessage ... }
}
# 清理
Get-ChildItem E:\Backup -Filter *.bak -Recurse | Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-5) } | Remove-Item -Force

• 優點：邏輯豐富（並行、加密、上傳雲端、複雜清理）、Windows/Linux 通用、易整合 CI/CD、無 xp_cmdshell 風險。
• 缺點：需 PS 技能、外部相依 (sqlcmd/Invoke-Sqlcmd)。

四法選型建議

通用最佳實務 (無論哪法)

• 3-2-1 原則：3 份副本、2 種介質、1 份異地。
• 定期還原演練：備份未驗證等於不存在。
• 加密備份：WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = ...)。
• 監控告警：作業失敗、備份檔案大小異常、磁碟空間不足 → 即時通知。