Zenn
🤖

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

2020/10/02に公開
SQL Server
tech

本ドキュメントはミラーです。最新の情報は以下Qiitaのドキュメントをご確認ください。：
https://qiita.com/tfukumori/items/228fc80f79250062bc88

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')の部分で、バックアップ対象から除きたいデータベースを指定する。現在はシステムデータベースを想定している。

スクリプト

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]

Discussion