-- 1. 建立網路映射 (映射遠端共享為本機磁碟機代號)
-- 參數：Y: 磁碟機代號、\\192.168.0.69\sqlbackup 共享路徑、密碼、遠端帳號
EXEC master..xp_cmdshell 'net use Y: \\192.168.0.69\sqlbackup "password" /user:192.168.0.69\account';
-- 2. 動態生成帶時間戳檔名 (格式: YYYYMMDD-HHMMSS.bak)
DECLARE @filename VARCHAR(200);
SELECT @filename = 'Y:\DB\'
+ REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ', '-'), ':', '')
+ '.bak';
-- 結果範例: Y:\DB\20231025-143000.bak
-- 3. 執行備份 (DB 為目標資料庫名)
BACKUP DATABASE [DB] TO DISK = @filename
WITH COMPRESSION, STATS = 5, CHECKSUM; -- 壓縮、進度、校驗和
-- 4. 移除網路映射 (釋放資源、安全)
EXEC master..xp_cmdshell 'net use Y: /delete';
| 環節 | 關鍵點 | 最佳實踐 |
|---|---|---|
| xp_cmdshell | 需啟用 sp_configure 'xp_cmdshell', 1；**極高風險**，生產環境建議改用 Proxy Account / PowerShell Agent Step / Maintenance Plan 避免直接開啟。 | |
| net use 認證 | 密碼明文出現在腳本/日誌中。**必須**建立專用備份帳號、最小權限 (僅寫入共享)、定期輪換密碼、審計日誌。 | |
| 動態檔名 | CONVERT(..., 120) (ODBC yyyy-mm-dd hh:mi:ss) + 多層 REPLACE 移除非法字元。可改用 FORMAT(GETDATE(), 'yyyyMMdd-HHmmss') (2012+)。 | |
| 備份選項 | COMPRESSION 減少 60-80% 體積/時間；CHECKSUM 偵測頁面損壞；STATS=5 記錄進度便於監控。 | |
| 路徑預建 | 遠端共享下的 DB 子資料夾**必須預先建立**，且 SQL Server 服務帳戶/Proxy 帳戶需有 **寫入/修改** NTFS 權限。 |
# SQL Server Agent 作業步驟類型：PowerShell
$src = "localhost" # 來源實例
$db = "DB" # 目標資料庫
$share = "\\192.168.0.69\sqlbackup\DB" # 遠端共享完整路徑
$cred = Get-StoredCredential -Name "SQLBackupCred" # Windows Credential Manager 儲存帳密
$filename = Join-Path $share ("{0:yyyyMMdd-HHmmss}.bak" -f (Get-Date))
# 使用 dbatools 模組 (強烈推薦) 或原生 SMO
Backup-DbaDatabase -SqlInstance $src -Database $db -Path $share -FileName $filename `
-CompressBackup -Checksum -CopyOnly -AzureBaseUrl $null `
-NetworkCredential $cred
# dbatools 自動處理網路認證、壓縮、校驗、多檔案條帶化、進度事件
優勢**：無需啟用 xp_cmdshell、憑證安全儲存、支援條帶化/多線程/雲端、豐富錯誤處理/重試邏輯、社群活躍維護。
Hourly_Backup_DB_Remote、擁有者 sa、類別 資料庫維護。2、間隔 1 分鐘。00:00、結束 23:59。# PowerShell 步驟 2：清理 7 天前備份
$share = "\\192.168.0.69\sqlbackup\DB"
$days = 7
Get-ChildItem -Path $share -Filter "*.bak" |
Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-$days) } |
Remove-Item -Force -ErrorAction SilentlyContinue
.bak、大小合理。RESTORE HEADERONLY FROM DISK = '...' 驗證備份集完整性。RESTORE DATABASE ... FROM DISK = '...' WITH MOVE, REPLACE, STATS=5 驗證可用性。**「本地備份無異地，等於沒備份」**。本文提供從基礎 xp_cmdshell + net use 到生產級 PowerShell + dbatools + Credential Manager 的完整演進路徑。核心要素：**遠端共享 + 動態檔名 + 壓縮校驗 + 安全認證 + 自動清理 + 定期演練**。請根據環境安全基線選擇實施層級，**強烈建議棄用 xp_cmdshell 改用 PowerShell Proxy/模組方案**。
