SQL Server 2005 自動備份至遠端電腦的存儲過程實作

需求場景

每日自動完整備份本地資料庫，並透過網路共享複製到另一台伺服器（異地備份），可選擇是否保留本地暫存檔。

核心存儲過程代碼

USE Master;
GO
CREATE PROC [dbo].[bakup_DataBase]
AS
BEGIN
    DECLARE @strPsw VARCHAR(50), @strUsr VARCHAR(50), @strCmdShell VARCHAR(300);
    DECLARE @strDataBaseName VARCHAR(20), @FullFileName VARCHAR(200);
    DECLARE @FileFlag VARCHAR(50), @ToFileName VARCHAR(200);
    DECLARE @SQLStr VARCHAR(500), @SQLStr2 VARCHAR(500), @FlagDel VARCHAR(20);

    -- 檔名規則：YYYY-MM-DD-HH-MM-SS
    SET @FileFlag = REPLACE(REPLACE(CONVERT(CHAR(20), GETDATE(), 20), ':', ''), ' ', '-');

    -- 目標機器認證（需具備寫入共享權限的 Windows 帳號）
    SET @strUsr = 'DOMAIN\administrator';   -- 修改為實際域\用戶
    SET @strPsw = 'password';               -- 修改為實際密碼

    -- 建立網路連線
    SET @strCmdShell = 'net use \\192.168.0.22\c$ ' + @strPsw + ' /user:' + @strUsr;

    -- 要備份的資料庫
    SET @strDataBaseName = 'LilianDB';

    -- 本地暫存路徑（需 SQL Server 服務帳號有寫入權限）
    SET @FullFileName = 'E:\SqlServer自動備份文件\' + 'LilianDB_backup_' + @FileFlag + '.BAK';

    -- 遠端儲存路徑（UNC 路徑）
    SET @ToFileName = '\\192.168.0.22\D$\OneCardBak\';

    -- True=刪除本地暫存檔，False=保留
    SET @FlagDel = 'False';

    -- 複製與刪除指令
    SET @SQLStr = 'copy ' + @FullFileName + ' ' + @ToFileName;
    SET @SQLStr2 = 'del ' + @FullFileName;

    -- 1. 完整備份到本地
    BACKUP DATABASE @strDataBaseName TO DISK = @FullFileName WITH INIT;

    -- 2. 建立網路連線
    EXEC master..xp_cmdshell @strCmdShell;

    -- 3. 複製到遠端
    EXEC master..xp_cmdshell @SQLStr;

    -- 4. 選擇性刪除本地暫存檔
    IF (@FlagDel = 'True')
        EXEC master..xp_cmdshell @SQLStr2;
END;
GO

關鍵設定說明

排程自動化

1. 在 SQL Server Agent 建立作業，步驟類型選 T-SQL，命令：EXEC dbo.bakup_DataBase;
2. 設定排程（如每日 02:00 執行）。
3. 啟用 Agent 服務並設為自動啟動。
4. 建議在作業屬性→通知中配置失敗時郵件通知 DBA。

安全與維護建議

• 備份檔加密：SQL 2014+ 支援 WITH ENCRYPTION；2005 需第三方工具或透明資料庫加密 (TDE) 為 Enterprise 版專屬。
• 定期驗證備份可還原性（RESTORE VERIFYONLY）。
• 遠端保留策略：配合 Windows 排程或 PowerShell 清理超過 N 天的舊備份。
• 監控磁碟空間：本地與遠端皆需預留足夠空間。