[SQL] 使用變數進行備份

以往把 BACKUP T-SQL 寫的彈性一些，都是利用組字串來進行，閱讀 MSDN BACKUP 文件時，發現 BACKUP T-SQL 的 DatabaseName 和 Device 參數可以直接用變數帶入。

{ database_name| @database_name_var }

這是要備份交易記錄、部分資料庫或完整資料庫的來源資料庫。 如果這個名稱是以變數 (@database_name_var) 的形式提供，則除了 ntext 或 text 資料類型之外，您可以將這個名稱指定為字串常數 (@database_name_var=database name)，或指定為字元字串資料類型的變數。

TO backup_device [ ,...n]

指出隨附的備份裝置集合是未鏡像的媒體集，或是鏡像媒體集內的第一個鏡像 (如果宣告了一個或多個 MIRROR TO 子句)。

• backup_device：指定備份作業要使用的邏輯或實體備份裝置。
• { logical_device_name | @logical_device_name_var }
這是用來備份資料庫之備份裝置的邏輯名稱。邏輯名稱必須遵照識別碼的規則。如果備份裝置名稱是以變數 (@logical_device_name_var) 的方式來提供，則除了 ntext 或 text 資料類型之外，您可以將這個名稱指定為字串常數 (@logical_device_name_var = 邏輯備份裝置名稱)，或指定為字元字串資料類型的變數。
• { DISK | TAPE } = { 'physical_device_name' | @physical_device_name_var }
  指定磁碟檔案或磁帶裝置。在 BACKUP 陳述式內指定磁碟裝置之前，該裝置不需要存在。 如果實體裝置存在，且 BACKUP 陳述式並未指定 INIT 選項，就會將備份附加至裝置中。
  
• n：這是一個預留位置，表示可以在逗號分隔清單中指定最多達 64 個備份裝置。

• 利用 AdventureWorks 來 Demo

DECLARE @DBName AS nvarchar(100)
DECLARE @bakPath AS nvarchar(4000)
SET @DBName  = N'AdventureWorks'
SET @bakPath = N'C:\BackupVariable' + @DBName + '.bak'

BACKUP DATABASE @DBName TO DISK = @bakPath

• 對全部的使用者自訂資料庫進行備份

DECLARE curDB CURSOR   -- 宣告 Cursor 及其資料來源
  FOR
      (
        SELECT name
        FROM sys.databases
        WHERE database_id > 4
            AND CHARINDEX('report',Name) = 0
            AND state_desc NOT IN ('RESTORING', 'OFFLINE')
      )
 
OPEN curDB -- 打開 Cursor，並建立 Cursor 與資料表關連
 
DECLARE @DB AS nvarchar(100)
DECLARE @bakPath AS nvarchar(4000)
 
FETCH NEXT FROM curDB INTO @DB -- 將資料存進變數中
  WHILE (@@FETCH_STATUS = 0) -- 檢查是否有讀取到資料
    BEGIN  
  
      SET @bakPath = N'C:\BackupVariable\' + @DB + CONVERT(char(8),GETDATE(),112) + '.bak'
      BACKUP DATABASE @DB TO DISK = @bakPath
 
      FETCH NEXT FROM curDB INTO @DB
    END
 
CLOSE curDB -- 關閉 Cursor，並關閉 Cursor 與資料表連結
DEALLOCATE curDB -- 將 Cursor 物件移除

參考資料
• MSDN BACKUP