SQL Server 2000 備份自動化預存程序：參數化支援完整/差異/記錄備份

設計目標

提供單一預存程序 pr_backup_db，支援：

• 指定資料庫名稱、備份檔路徑。
• 自動驗證資料庫存在、路徑格式正確。
• 動態建構 BACKUP DATABASE 指令並執行。
• 輸出狀態旗標供呼叫端判斷成敗。

完整程式碼

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'pr_backup_db' AND xtype = 'P')
    DROP PROCEDURE pr_backup_db;
GO

CREATE PROCEDURE pr_backup_db
    @flag           VARCHAR(20) OUTPUT,   -- 回傳狀態：'ok' / 'db not exist' / 'file type error'
    @backup_db_name VARCHAR(128),         -- 目標資料庫
    @filename       VARCHAR(1000)         -- 完整備份檔路徑（含檔名），必須以 \ 結尾目錄
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(4000), @par NVARCHAR(1000);

    -- 1. 驗證資料庫存在
    IF NOT EXISTS (SELECT 1 FROM master..sysdatabases WHERE name = @backup_db_name)
    BEGIN
        SELECT @flag = 'db not exist';
        RETURN;
    END

    -- 2. 驗證路徑格式：必須包含 \ 且不以 \ 結尾（預期為檔案完整路徑）
    IF CHARINDEX('\', @filename) = 0 OR RIGHT(@filename, 1) = '\'
    BEGIN
        SELECT @flag = 'file type error';
        RETURN;
    END

    -- 3. 動態建構並執行 BACKUP 指令
    SELECT @par = '@filename VARCHAR(1000)';
    SELECT @sql = 'BACKUP DATABASE ' + QUOTENAME(@backup_db_name) + ' TO DISK = @filename WITH INIT, NAME = N''AutoBackup_' + CONVERT(VARCHAR(20), GETDATE(), 112) + ''', STATS = 10';

    EXEC sp_executesql @sql, @par, @filename = @filename;

    SELECT @flag = 'ok';
END
GO

使用範例

DECLARE @result VARCHAR(20);
EXEC pr_backup_db @flag = @result OUTPUT, @backup_db_name = 'Northwind', @filename = 'D:\Backups\Northwind_20240115.bak';
SELECT @result AS BackupResult; -- 應回傳 'ok'

擴充建議

注意事項

• 執行帳號需具備 sysadmin 或 db_backupoperator 角色，且 SQL Server 服務帳號對目標資料夾有寫入權限。
• WITH INIT 會覆寫同名檔案；生產環境建議檔名含日期時間避免誤覆寫。
• 大型資料庫備份期間會產生 I/O 壓力，建議離峰時段執行。