萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL Server 遠端定時備份腳本:網路磁碟映射 + Agent 作業實現異地備份

本機備份無法防範「整機故障」。利用 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';

部署步驟

  1. 準備遠端共享:目標伺服器建立資料夾 sqlbackup,設定「讀寫」共享權限給專用網域帳號(如 sqlbackup_svc)。
  2. 啟用 xp_cmdshell(僅限受控環境):
    EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
    EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
  3. 建立 SQL Agent 作業:
    • 步驟 1:執行上述 T-SQL 腳本(類型:Transact-SQL)。
    • 排程:每小時/每日視 RPO 而定(如每小時 05 分)。
    • 警示:作業失敗時發送郵件給 DBA。
  4. 驗證:手動執行作業,確認遠端共享出現 DB_20240115-140500.bak 且可還原。
  5. 進階強化版(含保留天數清理、錯誤處理、日誌記錄)

    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、異地複製第二份
    磁碟空間耗盡磁碟配額警示、保留天數自動清理

    替代方案對比

    • 維護計畫:GUI 易用、支援子計畫、但遠端路徑需 SQL 服務帳號有權限,較難排錯。
    • Ola Hallengren 腳本:社群標準解決方案,支援壓縮、加密、可用性群組、清理、IndexOptimize,強烈推薦生產環境採用。
    • 第三方工具:Redgate SQL Backup、Veeam、Commvault — 功能豐富、集中管理、成本較高。

    這套腳本已在生產穩定運行超過一年,每小時備份一次,配合保留策略與監控告警,以最低成本滿足 RPO=1hr、RTO=4hr 的業務需求。

copyright © 萬盛學電腦網 all rights reserved