您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> SQL Server 2005

SQL Server 2005 異地自動備份實戰：Agent 作業 + xp_cmdshell 方案

編輯：SQL Server 2005

需求背景

公司安防平台運行於單一伺服器，無獨立備份機，需將 SQL Server 2005 資料庫自動備份至異地主機。採用 SQL Server Agent 作業排程，配合 xp_cmdshell 執行網路映射與備份指令。

步驟一：啟用 SQL Server Agent 服務

在「服務」中將 SQL Server Agent 啟動類型設為「自動」並啟動服務，確保作業能按排程執行。

步驟二：建立備份作業

SSMS 中展開「SQL Server Agent」→「作業」右鍵「新增作業」。
「一般」頁輸入作業名稱（如「每日異地備份」）。
「步驟」頁新增步驟，子系統選「Transact-SQL 腳本 (T-SQL)」，貼入備份腳本。

核心備份腳本解析

-- 1. 啟用 xp_cmdshell（僅需執行一次，建議在伺服器初始化時完成）
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

-- 2. 變數宣告與設定（請依實際環境修改）
DECLARE @Store VARCHAR(200) = 'EmployeeInfo';   -- 資料庫名
DECLARE @User  VARCHAR(200) = 'liuhs';          -- 遠端機器管理員帳號
DECLARE @Pwd   VARCHAR(200) = 'winter';         -- 密碼（避免特殊符號）
DECLARE @IPPart VARCHAR(200) = 'd$\back';       -- 遠端共享路徑
DECLARE @IP     VARCHAR(200) = '192.168.3.42';  -- 遠端 IP
DECLARE @retaindays INT = 1;                    -- 保留天數

-- 3. 建立網路磁碟映射 Z:
DECLARE @cmd NVARCHAR(200) = 'net use z: \\' + @IP + '\' + @IPPart + ' "' + @Pwd + '" /user:' + @IP + '\' + @User;
EXEC master..xp_cmdshell @cmd;

-- 4. 清理舊備份（示例邏輯，可依需求調整）
DECLARE @i INT = 0, @now DATETIME = GETDATE(), @deletefiles VARCHAR(2000), @delcmd VARCHAR(2000);
WHILE @i < 30
BEGIN
    SET @deletefiles = 'z:\data' + @Store + '_db_' + CONVERT(VARCHAR(8), DATEADD(dd, -@retaindays - @i, @now), 112) + '.BAK';
    SET @delcmd = 'del ' + @deletefiles;
    EXEC master..xp_cmdshell @delcmd;
    SET @i = @i + 1;
END

-- 5. 完整備份
DECLARE @backupfile VARCHAR(2000) = 'z:\data' + @Store + '_db_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 20), '-', ''), ' ', ''), ':', '') + '.BAK';
DECLARE @sql VARCHAR(4000) = 'BACKUP DATABASE ' + @Store + ' TO DISK=''' + @backupfile + ''' WITH RETAINDAYS=' + CAST(@retaindays AS VARCHAR(10));
EXEC(@sql);

-- 6. 交易記錄備份
SET @backupfile = 'z:\data' + @Store + '_tlog_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 20), '-', ''), ' ', ''), ':', '') + '.TRN';
SET @sql = 'BACKUP LOG ' + @Store + ' TO DISK=''' + @backupfile + ''' WITH RETAINDAYS=' + CAST(@retaindays AS VARCHAR(10));
EXEC(@sql);

-- 7. 移除映射
EXEC master..xp_cmdshell 'net use z: /delete';

步驟三：設定排程

在「排程」頁新增每日執行計劃（如凌晨 02:00），啟用作業。

常見錯誤與排查

系統錯誤 5（拒絕存取）：確認遠端共享權限授予備份帳號（建議 Administrators 群組）；遠端機器「本地安全性原則」→「網路存取：本地帳戶的共用和安全模型」設為「經典 - 本地使用者以自己的身分驗證」。
xp_cmdshell 執行失敗：檢查是否已正確啟用進階選項與 xp_cmdshell 組態。
備份檔案為 0 位元組：檢查磁碟空間、網路連線穩定度、資料庫是否處於單一使用者模式。