SQL Server 資料庫備份與還原預存程序範例

分享一組可直接部署的備份/還原預存程序，採用動態 SQL 執行 BACKUP/RESTORE DATABASE，支援參數化路徑與資料庫名稱，適合整合至排程作業或應用程式呼叫。

備份預存程序：pr_backup_db

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'pr_backup_db' AND type = 'P')
    DROP PROCEDURE dbo.pr_backup_db;
GO

CREATE PROCEDURE dbo.pr_backup_db
    @Flag          VARCHAR(10) OUTPUT,   -- 回傳 'OK' 或錯誤訊息
    @BackupDBName  SYSNAME,              -- 要備份的資料庫名稱
    @FileName      NVARCHAR(1000)        -- 完整備份檔路徑（含檔名）
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX), @Params NVARCHAR(500);

    SET @Params = N'@FileName NVARCHAR(1000)';
    SET @SQL = N'BACKUP DATABASE ' + QUOTENAME(@BackupDBName) + 
               N' TO DISK = @FileName WITH INIT, NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT, CHECKSUM, COMPRESSION;';
               -- INIT: 覆寫既有備份集；CHECKSUM: 頁面檢查碼；COMPRESSION: 壓縮（2008 企業版+）

    BEGIN TRY
        EXEC sp_executesql @SQL, @Params, @FileName = @FileName;
        SET @Flag = N'OK';
    END TRY
    BEGIN CATCH
        SET @Flag = ERROR_MESSAGE();
        -- 可選：記錄錯誤至自訂日誌表
    END CATCH
END
GO

還原預存程序：pr_restore_db

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'pr_restore_db' AND type = 'P')
    DROP PROCEDURE dbo.pr_restore_db;
GO

CREATE PROCEDURE dbo.pr_restore_db
    @Flag         VARCHAR(10) OUTPUT,
    @RestoreDBName SYSNAME,
    @FileName     NVARCHAR(1000),
    @MoveData     NVARCHAR(500) = NULL,   -- 選擇性：移動資料檔 'LogicalName=NewPath'
    @MoveLog      NVARCHAR(500) = NULL    -- 選擇性：移動記錄檔 'LogicalName=NewPath'
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX), @Params NVARCHAR(500), @MoveClause NVARCHAR(1000) = N'';

    IF @MoveData IS NOT NULL SET @MoveClause += N' MOVE ' + @MoveData;
    IF @MoveLog IS NOT NULL SET @MoveClause += N' MOVE ' + @MoveLog;

    SET @Params = N'@FileName NVARCHAR(1000)';
    SET @SQL = N'RESTORE DATABASE ' + QUOTENAME(@RestoreDBName) + 
               N' FROM DISK = @FileName WITH REPLACE, STATS = 10' + @MoveClause + N';';

    BEGIN TRY
        EXEC sp_executesql @SQL, @Params, @FileName = @FileName;
        SET @Flag = N'OK';
    END CATCH
    BEGIN
        SET @Flag = ERROR_MESSAGE();
    END CATCH
END
GO

呼叫範例

-- 備份
DECLARE @Result VARCHAR(10);
EXEC dbo.pr_backup_db @Result OUT, N'SalesDB', N'D:\Backups\SalesDB_20240115.bak';
PRINT @Result;

-- 還原至不同路徑
DECLARE @Result2 VARCHAR(10);
EXEC dbo.pr_restore_db @Result2 OUT, N'SalesDB_Test', N'D:\Backups\SalesDB_20240115.bak',
    N'N''SalesDB_Data'', N''D:\Data\SalesDB_Test.mdf''',
    N'N''SalesDB_Log'', N''D:\Log\SalesDB_Test.ldf''';
PRINT @Result2;

進階擴充建議

• 差異/記錄備份：加入 @BackupType CHAR(1) 參數（F/D/L），動態組裝 WITH DIFFERENTIAL 或 BACKUP LOG。
• 多檔案備份：支援 @FileList NVARCHAR(MAX) 以逗號分隔多個磁碟檔案，平行寫入提速。
• 驗證備份：備份後呼叫 RESTORE VERIFYONLY FROM DISK = @FileName。
• 加密備份：SQL 2014+ 支援 WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = ...)。
• 非同步執行：搭配 sp_start_job 啟動 Agent 作業，避免應用程式阻塞。

權限需求

• 執行者需具備 db_backupoperator 角色（備份）或 dbcreator / sysadmin（還原）。
• SQL Server 服務帳戶需對備份資料夾具備讀寫權限。

將備份/還原邏輯封裝為預存程序，可確保參數檢查、錯誤處理、稽核記錄一致性，便於自動化與治理。