本機備份無法防範「整機故障」。利用 xp_cmdshell 建立網路磁碟映射,搭配 SQL Agent 作業排程,即可實現低成本的異地備份。本文提供生產可用的完整 T-SQL 腳本與部署步驟。
-- 1. 建立網路磁碟映射(需具備目標伺服器寫入權限)
-- Y: 為映射磁碟代號;192.168.0.69\sqlbackup 為共享路徑;password/account 為遠端憑證
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';
-- 3. 執行備份(DB 為目標資料庫名稱)
BACKUP DATABASE [DB] TO DISK = @filename
WITH COMPRESSION, STATS = 10; -- 壓縮備份、顯示進度
-- 4. 移除網路映射(釋放連線)
EXEC master..xp_cmdshell 'net use Y: /delete';
sqlbackup,設定「讀寫」共享權限給專用網域帳號(如 sqlbackup_svc)。EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
DB_20240115-140500.bak 且可還原。DECLARE @SharePath VARCHAR(200) = '\\192.168.0.69\sqlbackup';
DECLARE @DriveChar CHAR(1) = 'Y';
DECLARE @DBName SYSNAME = 'DB';
DECLARE @RetainDays INT = 7;
DECLARE @Cmd VARCHAR(500), @FilePath VARCHAR(300), @Err INT;
BEGIN TRY
-- 映射
SET @Cmd = 'net use ' + @DriveChar + ': "' + @SharePath + '" "P@ssw0rd" /user:domain\sqlbackup_svc';
EXEC @Err = master..xp_cmdshell @Cmd, NO_OUTPUT;
IF @Err <> 0 THROW 50001, '網路映射失敗', 1;
-- 備份檔名
SET @FilePath = @DriveChar + ':\' + @DBName + '_'
+ CONVERT(CHAR(8), GETDATE(), 112) + '_'
+ REPLACE(CONVERT(CHAR(8), GETDATE(), 108), ':', '') + '.bak';
-- 備份
BACKUP DATABASE @DBName TO DISK = @FilePath WITH COMPRESSION, CHECKSUM, STATS = 5;
-- 清理舊備份 (保留 N 天)
SET @Cmd = 'forfiles /p ' + @DriveChar + ':\ /m ' + @DBName + '_*.bak /d -' + CAST(@RetainDays AS VARCHAR) + ' /c "cmd /c del @path"';
EXEC master..xp_cmdshell @Cmd, NO_OUTPUT;
END TRY
BEGIN CATCH
DECLARE @Msg NVARCHAR(4000) = ERROR_MESSAGE();
RAISERROR('備份作業失敗: %s', 16, 1, @Msg) WITH LOG;
END CATCH
-- 移除映射
EXEC master..xp_cmdshell 'net use ' + @DriveChar + ': /delete', NO_OUTPUT;
| 風險 | 對策 |
|---|---|
| xp_cmdshell 安全性 | 專用低權限網域帳號、防火牆限制來源 IP、審計登入 |
| 網路中斷導致備份掛起 | 設定 net use /persistent:no、Agent 作業步驟設定逾時、監控作業歷程 |
| 備份檔案損毀/不可還原 | 定期執行 RESTORE VERIFYONLY、異地複製第二份 |
| 磁碟空間耗盡 | 磁碟配額警示、保留天數自動清理 |
這套腳本已在生產穩定運行超過一年,每小時備份一次,配合保留策略與監控告警,以最低成本滿足 RPO=1hr、RTO=4hr 的業務需求。