SQL Server 2000 備份與還原預存程序實作範例

備份資料庫預存程序：pr_backup_db

此預存程序封裝動態 SQL 執行備份，支援參數驗證與錯誤回報：

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'pr_backup_db' AND xtype = 'P')
    DROP PROC pr_backup_db
GO

CREATE PROC pr_backup_db
    @flag VARCHAR(20) OUTPUT,
    @backup_db_name VARCHAR(128),
    @filename VARCHAR(1000)  -- 路徑+檔名
AS
DECLARE @sql NVARCHAR(4000), @par NVARCHAR(1000)

-- 檢查資料庫是否存在
IF NOT EXISTS (SELECT * FROM master..sysdatabases WHERE name = @backup_db_name)
BEGIN
    SELECT @flag = 'db not exist'
    RETURN
END

-- 驗證路徑格式：需包含 \ 且不以 \ 結尾
IF RIGHT(@filename, 1) <> '\' AND CHARINDEX('\', @filename) <> 0
BEGIN
    SELECT @par = '@filename VARCHAR(1000)'
    SELECT @sql = 'BACKUP DATABASE ' + @backup_db_name + ' TO DISK = @filename WITH INIT'
    EXEC sp_executesql @sql, @par, @filename
    SELECT @flag = 'ok'
    RETURN
END
ELSE
BEGIN
    SELECT @flag = 'file type error'
    RETURN
END
GO

使用範例

DECLARE @result VARCHAR(20)
EXEC pr_backup_db @result OUTPUT, 'MyDatabase', 'D:\Backups\MyDB_20240115.bak'
PRINT @result  -- 輸出 'ok' 或錯誤代碼

設計重點

• 動態 SQL：使用 sp_executesql 參數化執行，防止 SQL 注入
• 路徑驗證：確保檔案路徑格式正確，避免備份失敗
• 狀態回傳：透過 OUTPUT 參數回傳執行結果，便於呼叫端判斷
• WITH INIT：覆寫現有備份檔，避免檔案無限增長

擴充建議

• 加入差異備份、記錄備份選項
• 整合壓縮功能（呼叫 xp_makecab）
• 記錄備份歷程至管理表
• 支援多檔案分割備份（大型資料庫）

此預存程序提供標準化備份介面，適合納入自動化維護計畫或應用程式呼叫。