SQL Server 一鍵備份所有使用者資料庫：動態游標批次備份儲存程序實作

前言

管理多資料庫實例時，逐一手動備份極不現實。本文提供一支儲存程序 p_backupdb，能一次備份所有使用者資料庫 (排除系統庫)，或指定特定資料庫清單，檔名自動附加日期，支援自訂備份目錄。

完整程式碼

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

CREATE PROCEDURE [dbo].[p_backupdb]
    @bkpath NVARCHAR(260) = 'D:\',   -- 備份目錄，預設 D:\，若不存在會嘗試用 master 路徑
    @dbname NVARCHAR(4000) = ''      -- 資料庫名稱清單 (可含 LIKE 萬用字元)，空白 = 所有使用者資料庫
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX), @strdate NVARCHAR(20);
    SET @strdate = REPLACE(CONVERT(NVARCHAR(10), GETDATE(), 120), '-', ''); -- YYYYMMDD

    -- 1. 正規化備份路徑
    IF ISNULL(@bkpath, '') = ''
    BEGIN
        SELECT @bkpath = RTRIM(REVERSE(filename)) FROM master..sysfiles WHERE name = 'master';
        SELECT @bkpath = REVERSE(SUBSTRING(@bkpath, CHARINDEX('\', @bkpath), 4000)) + 'BACKUP\';
    END
    ELSE IF RIGHT(@bkpath, 1) <> '\'
        SET @bkpath = @bkpath + '\';

    -- 2. 建立目標資料庫游標
    DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT name
        FROM master..sysdatabases
        WHERE name NOT IN ('master','tempdb','model','msdb')
          AND (ISNULL(@dbname,'') = '' OR name LIKE '%' + @dbname + '%');

    OPEN db_cursor;
    FETCH NEXT FROM db_cursor INTO @dbname;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = 'BACKUP DATABASE ' + QUOTENAME(@dbname)
            + ' TO DISK = ''' + @bkpath + @dbname + '_' + @strdate + '.bak'''
            + ' WITH FORMAT, COMPRESSION, CHECKSUM';  -- 建議加入壓縮、校驗和

        PRINT 'Backing up: ' + @dbname;  -- 進度輸出
        EXEC sp_executesql @sql;

        FETCH NEXT FROM db_cursor INTO @dbname;
    END

    CLOSE db_cursor;
    DEALLOCATE db_cursor;
END
GO

關鍵設計細節

呼叫範例

-- 1. 備份所有使用者資料庫到預設 D:\
EXEC p_backupdb;

-- 2. 備份所有使用者資料庫到指定網路磁碟
EXEC p_backupdb @bkpath = '\\BackupServer\SQLBackups\';

-- 3. 僅備份名稱含 'CRM' 的資料庫
EXEC p_backupdb @bkpath = 'E:\Backups\', @dbname = 'CRM';

-- 4. 備份特定清單 (需自行修改程序支援清單解析，或多次呼叫)
EXEC p_backupdb @bkpath = 'E:\Backups\', @dbname = 'DB_A';
EXEC p_backupdb @bkpath = 'E:\Backups\', @dbname = 'DB_B';

進階擴充建議

1. 平行備份：使用 sp_start_job 啟動多個 Agent 作業平行執行，或 CLR/SSIS 多執行緒。
2. 差異/記錄備份切換：加入 @bktype 參數，配合排程實現完整/差異/記錄輪替。
3. 備份驗證：備份完成後執行 RESTORE VERIFYONLY，寫入記錄表。
4. 集中記錄：建立 BackupLog 表，記錄資料庫、開始/結束時間、檔案大小、耗時、狀態。
5. 清理舊備份：配合 xp_delete_file 或 PowerShell 定期刪除超過保留天數的 .bak 檔。
6. AlwaysOn 可用性群組支援：檢查 sys.dm_hadr_availability_replica_states，僅在主要複本執行備份 (或次要複本備份需設定 BACKUP_PRIORITY)。

結語

這支儲存程序以 游標遍歷 + 動態 SQL + 參數化路徑/檔名 核心模式，解決了「多資料庫批次備份」的自動化需求。部署到管理資料庫、建立每日 Agent 作業呼叫，即可實現零人工介入的全例備份。搭配進階擴充 (平行、驗證、清理、AG 感知)，可演進為企業級備份自動化平台核心元件。