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

SQL Server 備份還原預存程序實作：動態 SQL、路徑解析與 xp_cmdshell 應用

編輯：關於SqlServer

核心需求：參數化、可重用的備份/還原自動化

企業級環境常需排程備份、異地還原或跨伺服器遷移。本文解析兩支生產級預存程序 pr_backup_db、pr_restore_db 及輔助函數 fn_GetFilePath，涵蓋動態 SQL 執行、檔案路徑解析、xp_cmdshell 檔案存在性驗證等關鍵技術。

一、備份預存程序：pr_backup_db

CREATE PROC pr_backup_db
    @flag VARCHAR(10) OUTPUT,
    @backup_db_name VARCHAR(128),
    @filename VARCHAR(1000)  -- 完整備份檔路徑
AS
BEGIN
    DECLARE @sql NVARCHAR(4000), @par NVARCHAR(1000);
    SET @par = '@filename VARCHAR(1000)';
    SET @sql = 'BACKUP DATABASE ' + QUOTENAME(@backup_db_name) + 
               ' TO DISK = @filename WITH INIT, STATS = 10';
    EXEC sp_executesql @sql, @par, @filename;
    SET @flag = 'ok';
END
GO

設計亮點：

使用 sp_executesql 參數化動態 SQL，防注入
WITH INIT 覆寫既有備份檔，適合輪替備份策略
STATS = 10 回報進度百分比，利於監控
輸出參數 @flag 提供呼叫端狀態確認

二、路徑解析函數：fn_GetFilePath

CREATE FUNCTION fn_GetFilePath(@filename NVARCHAR(260))
RETURNS NVARCHAR(260)
AS
BEGIN
    DECLARE @file_path NVARCHAR(260), @filename_reverse NVARCHAR(260);
    SET @filename_reverse = REVERSE(@filename);
    SET @file_path = SUBSTRING(@filename, 1, 
                       LEN(@filename) + 1 - CHARINDEX('\', @filename_reverse));
    RETURN @file_path;
END
GO

邏輯：反轉字串找第一個 \ 位置，反推目錄長度。適用於從完整檔案路徑提取資料夾路徑，供還原時建立目標資料/日誌檔目錄使用。

三、還原預存程序：pr_restore_db（核心片段）

CREATE PROC pr_restore_db
    @flag VARCHAR(20) OUTPUT,
    @restore_db_name NVARCHAR(128),
    @filename NVARCHAR(260)
AS
BEGIN
    DECLARE @proc_result TINYINT, @loop_time SMALLINT, @max_ids SMALLINT;
    DECLARE @file_bak_path NVARCHAR(260), @flag_file BIT;
    DECLARE @master_path NVARCHAR(260), @sql NVARCHAR(4000);
    
    -- 1. 參數合法性驗證：路徑必須含 '\' 且不以 '\' 結尾
    IF RIGHT(@filename,1) <> '\' AND CHARINDEX('\',@filename) <> 0
    BEGIN
        -- 2. 使用 xp_cmdshell 驗證檔案實體存在
        DECLARE @sql_cmd NVARCHAR(4000);
        SET @sql_cmd = 'DIR ' + @filename;
        EXEC @proc_result = master..xp_cmdshell @sql_cmd, NO_OUTPUT;
        
        IF @proc_result = 0  -- 檔案存在
        BEGIN
            -- 3. 取得邏輯檔名、移動至目標路徑、執行 RESTORE...
            -- （後續邏輯涉及 RESTORE FILELISTONLY、WITH MOVE、RECOVERY/NORECOVERY 等）
        END
        ELSE
            SET @flag = '檔案不存在';
    END
    ELSE
        SET @flag = '路徑格式錯誤';
END
GO

四、關鍵技術點深度解析

技術	用途	注意事項
`sp_executesql`	參數化動態 SQL	優於 EXEC('字串')，計畫重用、防注入
`xp_cmdshell`	呼叫 OS 指令驗證檔案	需啟用 `sp_configure 'xp_cmdshell', 1`；權限管控嚴格
`RESTORE FILELISTONLY`	取得備份組內邏輯檔名	還原前必須查詢，以便 `WITH MOVE` 指定新路徑
`WITH MOVE`	重新導向資料/日誌檔位置	異機還原或磁碟布局不同必須使用
`NORECOVERY/RECOVERY`	控制還原後資料庫狀態	差異/日誌還原鏈中間節點用 NORECOVERY