SQL Server 一鍵備份所有使用者資料庫：動態 T-SQL 儲存程序完整版（含日期命名、路徑自動偵測）

手動逐一備份數十個資料庫既繁瑣又易遺漏。本文提供經驗證修正的儲存程序 p_backupdb，支援備份全部或指定資料庫、自動產生「資料庫名_YYYYMMDD.bak」檔名、智慧偵測預設備份路徑，適合排程作業自動化部署。

儲存程式碼

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

CREATE PROC [dbo].[p_backupdb]
  @bkpath NVARCHAR(260) = 'D:',      -- 備份目錄，空值則自動偵測預設路徑
  @dbname NVARCHAR(4000) = ''        -- 指定資料庫名稱（支援 LIKE 模糊），空值則備份所有使用者資料庫
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql VARCHAR(8000), @strdate NVARCHAR(20), @dbname_loop NVARCHAR(128);

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

  -- 參數檢查：未指定路徑時自動偵測 master 資料庫所在目錄下的 BACKUP 資料夾
  IF ISNULL(@bkpath, '') = ''
  BEGIN
    DECLARE @master_path NVARCHAR(260);
    SELECT @master_path = REVERSE(SUBSTRING(REVERSE(filename), 
                     CHARINDEX('\', REVERSE(filename)) + 1, 4000))
    FROM master..sysfiles WHERE name = 'master';
    SET @bkpath = @master_path + 'BACKUP\';
  END
  ELSE IF RIGHT(@bkpath, 1) <> '\'
    SET @bkpath = @bkpath + '\';

  -- 取得要備份的資料庫清單
  DECLARE db_cursor CURSOR LOCAL FOR
    SELECT name FROM master..sysdatabases
    WHERE name NOT IN ('master','tempdb','model','msdb')
      AND (ISNULL(@dbname, '') = '' OR name LIKE '%' + @dbname + '%');

  OPEN db_cursor;
  FETCH NEXT FROM db_cursor INTO @dbname_loop;

  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @sql = 'BACKUP DATABASE ' + QUOTENAME(@dbname_loop) +
               ' TO DISK = ''' + @bkpath + @dbname_loop + '_' + @strdate + '.bak'' WITH FORMAT, INIT, STATS = 10';
    PRINT 'Executing: ' + @sql;
    EXEC(@sql);
    FETCH NEXT FROM db_cursor INTO @dbname_loop;
  END

  CLOSE db_cursor;
  DEALLOCATE db_cursor;
  SET NOCOUNT OFF;
END;
GO

呼叫範例

-- 備份所有使用者資料庫到 D:\BACKUP\
EXEC p_backupdb @bkpath = 'D:\BACKUP', @dbname = '';

-- 僅備份名稱含 'Sales' 的資料庫
EXEC p_backupdb @bkpath = 'D:\BACKUP', @dbname = 'Sales';

-- 使用預設路徑（自動偵測）
EXEC p_backupdb;

關鍵功能解析

部署建議

1. 在 master 或專用管理資料庫建立此程序。
2. 建立 SQL Agent 作業，排程每日/每週執行。
3. 設定作業通知（成功/失敗郵件）。
4. 定期驗證備份檔可還原（RESTORE VERIFYONLY）。
5. 配合異地複製（Robocopy / Azure Blob / NAS）實現 3-2-1 備份策略。

部署此自動化備份程序，結合異地備份與定期還原演練，即可建立可靠的資料庫資料保護機制。