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

SQL Server 自動化備份方案完整實作：T-SQL 儲存程序、作業排程與生產級策略範本

編輯：關於SqlServer

前言：為什麼需要自動化備份框架？

手動備份不可靠、不可追蹤、難以納入災難復原演練。本文提供一套完整的 T-SQL 自動化備份框架，包含：

動態路徑解析：自動取得預設資料/備份目錄。
彈性備份程序：支援完整/差異/記錄備份、多檔案、覆蓋/追加、動態檔名 (含日期時間)。
還原程序：支援強制覆蓋、關閉現有連線、自動重定位檔案路徑。
Agent 作業建立程序：一鍵部署每日/每週/每月/定時重複排程。
生產級策略範本：完整+差異+記錄組合、核心庫多層級備份與還原驗證。

一、核心輔助函數：取得預設路徑


CREATE FUNCTION dbo.f_getdbpath(@dbname SYSNAME)
RETURNS NVARCHAR(260)
AS
BEGIN
    DECLARE @re NVARCHAR(260);
    -- 取得 master 資料庫檔案路徑，反轉字串找最後一個 '\'
    IF @dbname IS NULL OR DB_ID(@dbname) IS NULL
        SELECT @re = RTRIM(REVERSE(filename)) FROM master..sysdatabases WHERE name = 'master';
    ELSE
        SELECT @re = RTRIM(REVERSE(filename)) FROM master..sysdatabases WHERE name = @dbname;

    IF @dbname IS NULL
        -- 備份目錄 = master 所在目錄 + '\BACKUP'
        SET @re = REVERSE(SUBSTRING(@re, CHARINDEX('\', @re) + 5, 260)) + 'BACKUP';
    ELSE
        -- 資料目錄 = 指定資料庫檔案所在目錄
        SET @re = REVERSE(SUBSTRING(@re, CHARINDEX('\', @re), 260));
    RETURN @re;
END

用法：
SELECT dbo.f_getdbpath('') → 預設資料目錄
SELECT dbo.f_getdbpath(NULL) → 預設備份目錄
SELECT dbo.f_getdbpath('MyDB') → MyDB 資料檔目錄

二、備份主程序：p_backupdb


CREATE PROCEDURE dbo.p_backupdb
    @dbname     SYSNAME = '',           -- 空白 = 目前資料庫
    @bkpath     NVARCHAR(260) = '',     -- 空白 = 預設備份目錄
    @bkfname    NVARCHAR(260) = '',     -- 檔名範本，支援 \DBNAME\ \DATE\ \TIME\
    @bktype     NVARCHAR(10) = 'DB',    -- DB=完整, DF=差異, LOG=記錄
    @appendfile BIT = 1                 -- 1=追加(NOINIT), 0=覆蓋(INIT)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    IF ISNULL(@dbname,'') = '' SET @dbname = DB_NAME();
    IF ISNULL(@bkpath,'') = '' SET @bkpath = dbo.f_getdbpath(NULL);
    IF ISNULL(@bkfname,'') = '' SET @bkfname = '\DBNAME\_\DATE\_\TIME\.BAK';

    -- 解析檔名範本
    SET @bkfname = REPLACE(REPLACE(REPLACE(@bkfname,
        '\DBNAME\', @dbname),
        '\DATE\', CONVERT(VARCHAR, GETDATE(), 112)),  -- YYYYMMDD
        '\TIME\', REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '')); -- HHMMSS

    SET @sql = 'BACKUP ' + CASE @bktype WHEN 'LOG' THEN 'LOG ' ELSE 'DATABASE ' END + @dbname
        + ' TO DISK = ''' + @bkpath + @bkfname + ''''
        + ' WITH ' + CASE @bktype WHEN 'DF' THEN 'DIFFERENTIAL, ' ELSE '' END
        + CASE @appendfile WHEN 1 THEN 'NOINIT' ELSE 'INIT' END;

    PRINT @sql;  -- 除錯用
    EXEC sp_executesql @sql;
END

呼叫範例


-- 完整備份至預設目錄，檔名：MyDB_20241219_143000.bak
EXEC p_backupdb @dbname='MyDB';

-- 差異備份，自訂路徑與檔名
EXEC p_backupdb @dbname='MyDB', @bkpath='E:\Backups\', @bkfname='MyDB_\DATE_\TIME_.bak', @bktype='DF';

-- 記錄備份每 2 小時
EXEC p_backupdb @dbname='MyDB', @bktype='LOG';

三、還原主程序：p_RestoreDb


CREATE PROCEDURE dbo.p_RestoreDb
    @bkfile     NVARCHAR(1000),   -- 備份檔完整路徑
    @dbname     SYSNAME = '',     -- 還原後資料庫名，空白=從檔名推導
    @dbpath     NVARCHAR(260) = '', -- 資料/記錄檔存放目錄，空白=預設資料目錄
    @retype     NVARCHAR(10) = 'DB', -- DB=完整, DBNOR=差異/記錄基底(NORECOVERY), DF=差異, LOG=記錄
    @filenumber INT = 1,          -- 備份集檔案編號
    @overexist  BIT = 1,          -- 覆蓋既有資料庫
    @killuser   BIT = 1           -- 覆蓋時殺掉現有連線
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX), @lfn NVARCHAR(128), @tp CHAR(1), @i INT = 0;

    -- 1. 解析目標資料庫名
    IF ISNULL(@dbname,'') = ''
    BEGIN
        SELECT @sql = REVERSE(@bkfile);
        SELECT @sql = CASE WHEN CHARINDEX('.', @sql)=0 THEN @sql ELSE SUBSTRING(@sql, CHARINDEX('.',@sql)+1, 1000) END;
        SELECT @sql = CASE WHEN CHARINDEX('\', @sql)=0 THEN @sql ELSE LEFT(@sql, CHARINDEX('\',@sql)-1) END;
        SET @dbname = REVERSE(@sql);
    END

    -- 2. 目標路徑
    IF ISNULL(@dbpath,'') = '' SET @dbpath = dbo.f_getdbpath('');

    -- 3. 基本 RESTORE 陳述式
    SET @sql = 'RESTORE ' + CASE @retype WHEN 'LOG' THEN 'LOG ' ELSE 'DATABASE ' END + @dbname
        + ' FROM DISK = ''' + @bkfile + ''''
        + ' WITH FILE = ' + CAST(@filenumber AS VARCHAR)
        + CASE WHEN @overexist=1 AND @retype IN('DB','DBNOR') THEN ', REPLACE' ELSE '' END
        + CASE @retype WHEN 'DBNOR' THEN ', NORECOVERY' ELSE ', RECOVERY' END;

    -- 4. 自動重定位檔案 (MOVE)
    IF @retype IN ('DB','DBNOR')
    BEGIN
        CREATE TABLE #fl (ln NVARCHAR(128), pn NVARCHAR(260), tp CHAR(1), fgn NVARCHAR(128), sz NUMERIC(20,0), msz NUMERIC(20,0));
        INSERT INTO #fl EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @bkfile + '''');
        DECLARE c CURSOR FOR SELECT ln, tp FROM #fl;
        OPEN c; FETCH NEXT FROM c INTO @lfn, @tp;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @sql = @sql + ', MOVE ''' + @lfn + ''' TO ''' + @dbpath + @dbname + CAST(@i AS VARCHAR)
                + CASE @tp WHEN 'D' THEN '.mdf' ELSE '.ldf' END + '''';
            SET @i = @i + 1;
            FETCH NEXT FROM c INTO @lfn, @tp;
        END
        CLOSE c; DEALLOCATE c; DROP TABLE #fl;
    END

    -- 5. 殺掉現有連線
    IF @overexist = 1 AND @killuser = 1
    BEGIN
        DECLARE @spid VARCHAR(20), c2 CURSOR FOR
            SELECT CAST(spid AS VARCHAR) FROM master..sysprocesses WHERE dbid = DB_ID(@dbname);
        OPEN c2; FETCH NEXT FROM c2 INTO @spid;
        WHILE @@FETCH_STATUS = 0
        BEGIN EXEC('KILL ' + @spid); FETCH NEXT FROM c2 INTO @spid; END
        CLOSE c2; DEALLOCATE c2;
    END

    PRINT @sql;
    EXEC sp_executesql @sql;
END

四、Agent 作業建立程序：p_createjob


CREATE PROCEDURE dbo.p_createjob
    @jobname    VARCHAR(100),
    @sql        VARCHAR(MAX),
    @dbname     SYSNAME = '',
    @freqtype   VARCHAR(6) = 'day',   -- day, week, month
    @fsinterval INT = 1,              -- 日內重複間隔 (小時)
    @time       INT = 170000          -- 開始時間 HHMMSS
AS
BEGIN
    IF ISNULL(@dbname,'') = '' SET @dbname = DB_NAME();

    -- 建立作業
    EXEC msdb..sp_add_job @job_name = @jobname;

    -- 建立步驟
    EXEC msdb..sp_add_jobstep @job_name = @jobname,
        @step_name = N'備份執行', @subsystem = 'TSQL',
        @database_name = @dbname, @command = @sql,
        @retry_attempts = 5, @retry_interval = 5;

    -- 建立排程
    DECLARE @ftype INT, @fstype INT, @ffactor INT;
    SELECT @ftype = CASE @freqtype WHEN 'day' THEN 4 WHEN 'week' THEN 8 WHEN 'month' THEN 16 END;
    SELECT @fstype = CASE @fsinterval WHEN 1 THEN 0 ELSE 8 END;  -- 0=一次, 8=重複
    IF @fsinterval <> 1 SET @time = 0;  -- 重複模式從 00:00 起算
    SET @ffactor = CASE @freqtype WHEN 'day' THEN 0 ELSE 1 END;

    EXEC msdb..sp_add_jobschedule @job_name = @jobname,
        @name = N'排程', @freq_type = @ftype, @freq_interval = 1,
        @freq_subday_type = @fstype, @freq_subday_interval = @fsinterval,
        @freq_recurrence_factor = @ffactor, @active_start_time = @time;
END

五、生產級備份策略範本

策略 A：經典「完整 + 差異 + 記錄」


DECLARE @sql VARCHAR(MAX);

-- 1. 每週完整備份 (週日 17:00)
SET @sql = 'EXEC p_backupdb @dbname=''PROD_DB''';
EXEC p_createjob @jobname='每週完整備份', @sql=@sql, @freqtype='week', @time=170000;

-- 2. 每日差異備份 (每天 17:00)
SET @sql = 'EXEC p_backupdb @dbname=''PROD_DB'', @bktype=''DF''';
EXEC p_createjob @jobname='每日差異備份', @sql=@sql, @freqtype='day', @time=170000;

-- 3. 每 2 小時記錄備份 (全天候)
SET @sql = 'EXEC p_backupdb @dbname=''PROD_DB'', @bktype=''LOG''';
EXEC p_createjob @jobname='每2小時記錄備份', @sql=@sql, @freqtype='day', @fsinterval=2;

策略 B：核心庫三層驗證備份 (生產/週/日三個驗證庫)

目的：備份同時驗證可還原性，提供時間點復原查詢庫。


-- 月底完整備份 + 產生月驗證庫 + 為週/日庫奠基
DECLARE @sql NVARCHAR(MAX) = '
DECLARE @path NVARCHAR(260), @fname NVARCHAR(100);
SET @fname = ''PRODUCE_'' + CONVERT(VARCHAR(10), GETDATE(), 112) + ''_m.bak'';
SET @path = dbo.f_getdbpath(NULL) + @fname;

-- 完整備份
EXEC p_backupdb @dbname=''PRODUCE'', @bkfname=@fname;

-- 還原為月驗證庫
EXEC p_RestoreDb @bkfile=@path, @dbname=''PRODUCE_月'';

-- 為週庫奠基 (NORECOVERY)
EXEC p_RestoreDb @bkfile=@path, @dbname=''PRODUCE_周'', @retype=''DBNOR'';

-- 為日庫奠基 (NORECOVERY)
EXEC p_RestoreDb @bkfile=@path, @dbname=''PRODUCE_日'', @retype=''DBNOR'';
';
EXEC p_createjob @jobname='每月備份驗證', @sql=@sql, @freqtype='month', @time=164000;

-- 週日差異備份 + 還原週驗證庫
SET @sql = '
DECLARE @path NVARCHAR(260), @fname NVARCHAR(100);
SET @fname = ''PRODUCE_'' + CONVERT(VARCHAR(10), GETDATE(), 112) + ''_w.bak'';
SET @path = dbo.f_getdbpath(NULL) + @fname;
EXEC p_backupdb @dbname=''PRODUCE'', @bkfname=@fname, @bktype=''DF'';
EXEC p_RestoreDb @bkfile=@path, @dbname=''PRODUCE_周'', @retype=''DF'';
';
EXEC p_createjob @jobname='每週差異驗證', @sql=@sql, @freqtype='week', @time=170000;

-- 每日記錄備份 + 還原日驗證庫
SET @sql = '
DECLARE @path NVARCHAR(260), @fname NVARCHAR(100);
SET @fname = ''PRODUCE_'' + CONVERT(VARCHAR(10), GETDATE(), 112) + ''_l.bak'';
SET @path = dbo.f_getdbpath(NULL) + @fname;
EXEC p_backupdb @dbname=''PRODUCE'', @bkfname=@fname, @bktype=''LOG'';
EXEC p_RestoreDb @bkfile=@path, @dbname=''PRODUCE_日'', @retype=''LOG'';
';
EXEC p_createjob @jobname='每日記錄驗證', @sql=@sql, @freqtype='day', @time=171500;

六、部署與維護檢核表

[ ] 在 master 或專用管理資料庫建立上述物件。
[ ] 確認 SQL Server Agent 服務啟動且設為自動。
[ ] 測試每個程序單獨執行無誤。
[ ] 部署作業後，檢視 msdb..sysjobhistory 確認成功。
[ ] 定期 (月/季) 執行還原演練，驗證備份檔完整性與 RTO。
[ ] 監控備份檔磁碟空間，設定清理舊備份檔作業 (保留 N 代)。
[ ] 記錄備份鏈斷裂警報 (可查 msdb..backupset 連續性)。

結語

這套框架將「備份策略」轉化為「可版控、可部署、可監控的程式碼」，消除手動操作風險。核心價值在於：動態路徑解析 適應叢集/AlwaysOn 環境、參數化檔名 確保唯一性、還原自動重定位 支援異機還原、驗證庫機制 落實備份可用性審計。建議納入團隊 DevOps 流程，以程式碼管理備份策略。