SQL Server 存儲過程批量匯出與定時備份存儲過程實戰

場景一：SSMS 圖形化匯出所有存儲過程腳本

1. 物件總管中右鍵目標資料庫 → 「工作」→「產生指令碼」。
2. 「選擇物件」→ 勾選「存儲過程」→ 下一步。
3. 勾選需匯出的具體存儲過程 → 下一步。
4. 選擇「儲存至檔案」→ 指定路徑 (如 C:\Scripts\Procs_20240101.sql) → 完成。

產出的 .sql 檔包含 CREATE PROCEDURE 完整定義，可直接在目標環境執行部署。

場景二：T-SQL 定時備份存儲過程 (含時間戳檔名)

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

ALTER PROCEDURE [dbo].[dbBak]
AS
BEGIN
    DECLARE @sqlCommand NVARCHAR(1000);
    DECLARE @dateTime NVARCHAR(20);

    -- 產生 yyyyMMddHHmmss 格式時間戳
    SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(), 111), '/', '') +
                       REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '');

    -- 動態構建 BACKUP 指令
    SET @sqlCommand = 'BACKUP DATABASE BLNBusyData TO DISK = ''D:\DataBaseBakUp\bln_' + @dateTime + '.BAK''';

    EXECUTE sp_executesql @sqlCommand;
END;
GO

關鍵點解析

• CONVERT(..., 111) 產生 yyyy/MM/dd，CONVERT(..., 108) 產生 HH:mm:ss。
• REPLACE 移除分隔符，得到連續數字字串，避免檔名非法字元。
• sp_executesql 執行動態 SQL，支援參數化 (此處簡單拼接即可)。
• 需搭配 SQL Agent 作業排程執行 (參考前文 msdb 作業建立腳本)。

進階建議：參數化增強版

CREATE PROCEDURE dbo.usp_BackupDatabase
    @DatabaseName SYSNAME,
    @BackupPath NVARCHAR(260) = N'D:\Backups\',
    @BackupType CHAR(1) = N'F', -- F=Full, D=Diff, L=Log
    @Compress BIT = 1,
    @Checksum BIT = 1
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @FileName NVARCHAR(500), @SQL NVARCHAR(MAX);

    SET @FileName = @BackupPath + @DatabaseName + '_' +
        CASE @BackupType WHEN 'F' THEN 'FULL' WHEN 'D' THEN 'DIFF' ELSE 'LOG' END + '_' +
        REPLACE(CONVERT(CHAR(8), GETDATE(), 112), '/', '') + '_' +
        REPLACE(CONVERT(CHAR(8), GETDATE(), 108), ':', '') + '.bak';

    SET @SQL = N'BACKUP DATABASE ' + QUOTENAME(@DatabaseName) +
        N' TO DISK = ' + QUOTENAME(@FileName, '''') +
        CASE @BackupType WHEN 'D' THEN N' WITH DIFFERENTIAL' ELSE N'' END +
        N', COMPRESSION = ' + CASE @Compress WHEN 1 THEN N'ON' ELSE N'OFF' END +
        N', CHECKSUM = ' + CASE @Checksum WHEN 1 THEN N'ON' ELSE N'OFF' END +
        N', STATS = 5;';

    EXEC sp_executesql @SQL;
END;

總結

• 匯出腳本：適合版控、跨環境部署、程式碼審查。
• 定時備份 SP：核心生產環境自動化備份邏輯，建議參數化、加入壓縮/校驗、輸出詳細統計資訊。