SQL Serverの複数のデータベースを一括バックアップする

TL;DR

• SQL Serverの複数のデータベースを一括バックアップするためのT-SQL。移行時にデータベースを一括でバックアップする際などに使用することを想定している。

スクリプトの内容に関する説明

• SQL Server Management Studio、またはBATファイル等からsqlcmd.exeでスクリプト実行する。
• D:\IKOUフォルダに出力することを想定している。
• <インスタンス名>_<データベース名>.bakに出力する。
• オンラインのデータベースのみバックアップする
• name NOT IN ('master', 'msdb', 'model', 'tempdb', 'Resource', 'xstoredb')の部分で、バックアップ対象から除きたいデータベースを指定する。現在はシステムデータベースを想定している。

スクリプト

パターン1：固定した場所（標準バックアップ先以外）へのバックアップ

• バックアップ先は固定（埋め込みD:\IKOU\）
• バックアップファイル名は固定

SET NOCOUNT ON 
USE [master] 
GO 
DECLARE @name sysname 
DECLARE @SQL nvarchar(500) 
DECLARE @BACKUPFILE nvarchar(500) 
DECLARE @BACKUPNAME nvarchar(500) 
DECLARE @BACKUPERRORMESSAGE nvarchar(500) 
DECLARE @INSTANCENAME nvarchar(50) 
DECLARE @backupSetId as int

SET @INSTANCENAME = convert(nvarchar(50), SERVERPROPERTY('instancename'))

SELECT * INTO [#databases] FROM [sys].[databases] WHERE state_desc <> 'OFFLINE' AND name NOT IN ('master', 'msdb', 'model', 'tempdb', 'Resource', 'xstoredb')

DECLARE [table_cursor] CURSOR FOR 
SELECT [name] FROM [#databases] 

OPEN [table_cursor] 
FETCH NEXT FROM [table_cursor] INTO @name 

WHILE @@FETCH_STATUS = 0 
BEGIN 
    SET @BACKUPFILE = N'D:\IKOU\' + @INSTANCENAME + '_' + @name + '.bak'
	SET @BACKUPNAME = @name + N' 完全 データベース バックアップ'
	SET @BACKUPERRORMESSAGE = N'確認に失敗しました。データベース ''' + @name + ''' のバックアップ情報が見つかりません。'
	SET @SQL = N'BACKUP DATABASE [' + @name + '] TO  DISK = ''' + @BACKUPFILE + ''' WITH NOFORMAT, INIT,  NAME = ''' + @BACKUPNAME + ''', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10'

    EXECUTE sp_executesql @SQL 

    select @backupSetId = position from msdb..backupset where database_name=@name and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@name )
    if @backupSetId is null begin raiserror(@BACKUPERRORMESSAGE, 16, 1) end
    RESTORE VERIFYONLY FROM  DISK = @BACKUPFILE WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

    FETCH NEXT FROM [table_cursor] INTO @name 
END 

CLOSE [table_cursor] 
DEALLOCATE [table_cursor]

パターン2：繰り返しバックアップおよび既定バックアップ先へのバックアップを考慮

@knutsn さんに情報提供頂きました。
ありがとうございます！

• バックアップ先は既定バックアップ先
• バックアップファイル名に時刻を付加するしているため上書きされない
• 繰り返し実行時に一時テーブルが残ることへの対処

-- 複数のデータベースをインスタンス既定のバックアップフォルダ内に一括バックアップするスクリプト
SET NOCOUNT ON 

USE [master] 
GO 

DECLARE @name sysname 
DECLARE @SQL nvarchar(500) 
DECLARE @BACKUPFILE nvarchar(500) 
DECLARE @BACKUPNAME nvarchar(500) 
DECLARE @BACKUPERRORMESSAGE nvarchar(500) 
DECLARE @SYSDT nvarchar(14) 
DECLARE @backupSetId as int

-- システム日時を取得。作成するバックアップのファイル名に使用する。
SET @SYSDT = FORMAT(CURRENT_TIMESTAMP , 'yyyyMMddHHmmss')

-- 一時テーブル #databases が既に存在する場合、削除。
-- 次のSELECT INTOが2回目以降の実行時にコケないようにするため。
IF OBJECT_ID(N'tempdb..#databases', N'U') IS NOT NULL
	DROP TABLE #databases;

-- バックアップ対象とするデータベースの一覧を、一時テーブル #databasesに取得する
-- WHERE句のname列に対する条件は適宜編集するとよろし。
SELECT * INTO [#databases]
FROM
	[sys].[databases]
WHERE
	state_desc <> 'OFFLINE'
	AND name NOT IN ('master', 'msdb', 'model', 'tempdb', 'Resource', 'xstoredb')
 
DECLARE [table_cursor] CURSOR FOR 
	SELECT [name] FROM [#databases] 

OPEN [table_cursor] 
FETCH NEXT FROM [table_cursor] INTO @name 

WHILE @@FETCH_STATUS = 0 
BEGIN
    SET @BACKUPFILE = @name + N'_backup_' + @SYSDT + '.bak'
    SET @BACKUPNAME = @name + N' 完全 データベース バックアップ'
    SET @BACKUPERRORMESSAGE = N'確認に失敗しました。データベース ''' + @name + ''' のバックアップ情報が見つかりません。'

	-- バックアップ実行
    SET @SQL = N'BACKUP DATABASE [' + @name + '] TO DISK = ''' + @BACKUPFILE + ''' WITH NOFORMAT, INIT,  NAME = ''' + @BACKUPNAME + ''', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10'
    EXECUTE sp_executesql @SQL 

	-- バックアップセットの位置を取得
    SELECT
		@backupSetId = position
	FROM
		msdb..backupset
	WHERE
		database_name = @name
		AND backup_set_id = (
			SELECT
				MAX(backup_set_id)
			FROM
				msdb..backupset
			WHERE
				database_name = @name
		)
    IF @backupSetId IS NULL BEGIN RAISERROR(@BACKUPERRORMESSAGE, 16, 1) END

	-- 作成したバックアップの整合性チェック
    RESTORE VERIFYONLY FROM DISK = @BACKUPFILE WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

    FETCH NEXT FROM [table_cursor] INTO @name 
END 

CLOSE [table_cursor] 
DEALLOCATE [table_cursor]