功能特色

• 支援備份所有使用者資料庫 (排除 system DB) 或指定資料庫清單。
• 檔案命名規則：`資料庫名_YYYYMMDD.bak`，自動附加日期避免覆蓋。
• 自動偵測預設備份路徑，或接受自訂路徑參數。
• 使用動態 SQL + 游標迴圈，相容 SQL Server 2000/2005/2008/2012+。

建立預存程序 dbo.p_backupdb

SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; GO

CREATE PROCEDURE [dbo].[p_backupdb]
    @bkpath NVARCHAR(260) = 'D:',  -- 備份目錄，預設 D:\；空值則自動偵測 SQL 預設備份路徑
    @dbname NVARCHAR(4000) = ''    -- 指定資料庫名 (支援 LIKE 模糊)，空值則備份所有使用者 DB
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(4000), @strdate NVARCHAR(8), @db sysname;
    DECLARE @cursor CURSOR;

    -- 產生日期字串 YYYYMMDD
    SET @strdate = REPLACE(CONVERT(NVARCHAR(10), GETDATE(), 120), '-', '');

    -- 參數檢查與路徑正規化
    IF ISNULL(@bkpath, '') = ''
    BEGIN
        -- 自動抓取 master 資料檔目錄並附加 \BACKUP
        DECLARE @masterfile NVARCHAR(260);
        SELECT @masterfile = REVERSE(SUBSTRING(REVERSE(filename), 
            CHARINDEX('\', REVERSE(filename)) + 1, 260)) + 'BACKUP\'
        FROM master..sysaltfiles WHERE name = 'master' AND fileid = 1;
        SET @bkpath = @masterfile;
    END
    ELSE IF RIGHT(@bkpath, 1) <> '\'
        SET @bkpath = @bkpath + '\';

    -- 建立資料庫清單游標
    IF ISNULL(@dbname, '') = ''
        SET @cursor = CURSOR LOCAL FAST_FORWARD FOR
            SELECT name FROM master..sysdatabases 
            WHERE name NOT IN ('master','tempdb','model','msdb') AND status <> 256; -- 排除載入中/離線
    ELSE
        SET @cursor = CURSOR LOCAL FAST_FORWARD FOR
            SELECT name FROM master..sysdatabases 
            WHERE name NOT IN ('master','tempdb','model','msdb') 
              AND name LIKE '%' + @dbname + '%' AND status <> 256;

    OPEN @cursor;
    FETCH NEXT FROM @cursor INTO @db;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = 'BACKUP DATABASE [' + @db + '] TO DISK = ''' 
                   + @bkpath + @db + '_' + @strdate + '.bak'' 
                   WITH INIT, NOUNLOAD, NAME = ''' + @db + ' Full Backup'', STATS = 10;';
        -- PRINT @sql; -- 偵錯用
        EXEC (@sql);
        FETCH NEXT FROM @cursor INTO @db;
    END

    CLOSE @cursor;
    DEALLOCATE @cursor;
END
GO

使用範例

-- 1. 備份所有使用者資料庫至 D:\Backup\
EXEC p_backupdb @bkpath = 'D:\Backup', @dbname = '';

-- 2. 備份名稱含 'ERP' 的資料庫至預設路徑
EXEC p_backupdb @bkpath = '', @dbname = 'ERP';

-- 3. 備份單一資料庫
EXEC p_backupdb @bkpath = 'E:\SQLBackup', @dbname = 'MyDB';

進階擴充建議

1. 差異/記錄備份： 新增 `@backuptype` 參數 (FULL/DIFF/LOG) 切換 `WITH DIFFERENTIAL` 或 `BACKUP LOG`。
2. 壓縮備份 (2008+)： 加入 `WITH COMPRESSION` 選項大幅縮減檔案體積。
3. 非同步執行： 搭配 SQL Agent 作業排程，並記錄備份結果至記錄表供監控報警。
4. 驗證備份： 結合 `RESTORE VERIFYONLY` 確保備份檔可用性。

注意事項

• 執行帳戶需具備 `db_backupoperator` 或 `sysadmin` 角色。
• 目標資料夾需授與 SQL Server 服務帳戶寫入權限 (NTFS ACL)。
• 大型資料庫建議加入 `MAXTRANSFERSIZE`、`BLOCKSIZE` 優化吞吐量。