SQL ServerのDB復元の自動化（AWS）

sqlserver-auto-restore-awsのアイキャッチ画像 Windows
2021.09.07

Windowsのバッチファイルを使い、SQL Serverのユーザーを作成し、そのユーザーを使ってDBを復元する方法を解説します。AWS向けです。
山括弧<…>で囲まれている箇所は、ご自身の環境に合わせて書き換える必要があります。

前提

  • Amazon RDS for SQL Serverが作成されていること。
  • EC2（Windows Server）が作成されていること。

S3へのDBダンプ格納

S3へDBダンプ（bakファイル）をアップロードします。
アップロード後のAmazon リソースネーム（ARN）を控えておきます。

ユーザー作成

batファイルを以下のように作成し、EC2インスタンス上で実行します。

@echo off
set CWD=%~dp0

REM DBサーバー名
Set ServerName=<RDS for SQL Serverのエンドポイント(e.g. *.*.ap-northeast-1.rds.amazonaws.com)>

REM ユーザー情報
Set UserID=<作成したいユーザー名>
Set Password=<作成したいユーザーのパスワード>
Set LoginUserID=<RDS for SQL Serverのマスターユーザー>
Set LoginPassword=<RDS for SQL Serverのマスターユーザーのパスワード>

REM ログ出力先
Set LogPath=%CWD%

REM 起動パラメーター
if "%1"=="" (set ServerName=%ServerName%) Else (set ServerName=%1)
if "%2"=="" (set UserID=%UserID%) Else (set UserID=%2)
if "%3"=="" (set Password=%Password%) Else (set Password=%3)
if "%~4"=="" (set LogPath=%LogPath%) Else (set LogPath=%~4)

REM ログ出力
Set cmdlog="%LogPath%\CreateUserBat.log"
Set sqllog="%LogPath%\CreateUserSql.log"


echo User Create start. > %cmdlog%
echo ServerName=%ServerName% >> %cmdlog%
echo UserID=%UserID% >> %cmdlog%

REM データベース-セキュリティ-ログイン-作成
REM sqlcmd -S %ServerName% -U %LoginUserID% -P %LoginPassword% -o %sqllog% -Q "EXIT(USE [Master] BEGIN TRY IF NOT EXISTS (SELECT * FROM sys.syslogins where name = N'%UserID%') CREATE LOGIN [%UserID%] WITH PASSWORD=N'%Password%',  DEFAULT_DATABASE=[master] , DEFAULT_LANGUAGE=[日本語], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON ; ALTER SERVER ROLE [sysadmin] ADD MEMBER [%UserID%] ; SELECT 0 END TRY BEGIN CATCH SELECT ERROR_NUMBER(),ERROR_MESSAGE() END CATCH)"
sqlcmd -S %ServerName% -U %LoginUserID% -P %LoginPassword% -o %sqllog% -Q "EXIT(USE [Master] BEGIN TRY IF NOT EXISTS (SELECT * FROM sys.syslogins where name = N'%UserID%') CREATE LOGIN [%UserID%] WITH PASSWORD=N'%Password%',  DEFAULT_DATABASE=[master] , DEFAULT_LANGUAGE=[日本語], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON ; ALTER SERVER ROLE [setupadmin] ADD MEMBER [%UserID%] ; ALTER SERVER ROLE [processadmin] ADD MEMBER [%UserID%] ; SELECT 0 END TRY BEGIN CATCH SELECT ERROR_NUMBER(),ERROR_MESSAGE() END CATCH)"

REM VIEW SERVER STATE権限付与
If %errorlevel%==0 (
   sqlcmd -S %ServerName% -U %LoginUserID% -P %LoginPassword% -o %sqllog% -Q "EXIT(USE [master] BEGIN TRY GRANT VIEW SERVER STATE TO [%UserID%] ; SELECT 0 END TRY BEGIN CATCH SELECT ERROR_NUMBER(),ERROR_MESSAGE() END CATCH)"
)

REM 終了
echo User Create Result=%errorlevel% >> %cmdlog%
exit /b %errorlevel%

RDS for SQL Serverのエンドポイントは以下で確認することができます。

DB復元

batファイルを以下のように作成し、EC2インスタンス上で実行します。 

@echo off
set CWD=%~dp0

REM DBサーバー名
Set ServerName=<RDS for SQL Serverのエンドポイント(e.g. *.*.ap-northeast-1.rds.amazonaws.com)>

REM ユーザー情報
Set UserID=<上記で作成したユーザー>
Set Password=<上記で作成したユーザーのパスワード>
Set LoginUserID=<RDS for SQL Serverのマスターユーザー>
Set LoginPassword=<RDS for SQL Serverのマスターユーザーのパスワード>

REM データベース情報
Set Database=<データベース名>

REM 入力ファイルパス
Set inFilePath=<S3のbakファイルのARN>

REM ログ出力先
Set LogPath=%CWD%

REM 起動パラメーター
if "%1"=="" (set ServerName=%ServerName%) Else (set ServerName=%1)
if "%~2"=="" (set inFilePath=%inFilePath%) Else (set inFilePath=%~2)
if "%3"=="" (set LoginUserID=%LoginUserID%) Else (set LoginUserID=%3)
if "%4"=="" (set LoginPassword=%LoginPassword%) Else (set LoginPassword=%4)
if "%5"=="" (set UserID=%UserID%) Else (set UserID=%5)
if "%6"=="" (set Password=%Password%) Else (set Password=%6)
if "%7"=="" (set Database=%Database%) Else (set Database=%7)

REM ログ出力
Set sqllog="%LogPath%\RestorePctSql.log"
Set cmdlog="%LogPath%\RestorePctBat.log"

echo DB setup start. > %cmdlog%
echo ServerName=%ServerName% >> %cmdlog%
echo inFilePath=%inFilePath% >> %cmdlog%
echo UserID=%UserID% >> %cmdlog%
echo Database=%Database% >> %cmdlog%

REM 存在有無
sqlcmd -S %ServerName% -U %LoginUserID% -P %LoginPassword% -o %sqllog% -Q"EXIT(USE [msdb] BEGIN TRY IF EXISTS ( SELECT * FROM [sys].[databases] WHERE name = '%Database%') SELECT 1 ELSE SELECT 0 END TRY BEGIN CATCH SELECT ERROR_NUMBER(),ERROR_MESSAGE() END CATCH)"
echo errorlevel=%errorlevel%. >> %cmdlog%
IF NOT %errorlevel% == 0 (
   echo DB already exist. >> %cmdlog%
   exit /b 300
)

REM 復元開始
sqlcmd -S %ServerName% -U %LoginUserID% -P %LoginPassword% -o %sqllog% -Q"EXIT(USE [msdb] BEGIN TRY DECLARE @restored_data TABLE ( task_id int, task_type nvarchar(128), lifecycle nvarchar(128), created_at datetime, last_updated datetime, database_name nvarchar(128), S3_object_arn nvarchar(128), overwrite_S3_backup_file int, KMS_master_key_arn nvarchar(128), task_progress int, task_info nvarchar(128)); INSERT INTO @restored_data EXEC [dbo].[rds_restore_database] [%Database%],[%inFilePath%] SELECT task_id FROM @restored_data END TRY BEGIN CATCH SELECT ERROR_NUMBER(),ERROR_MESSAGE() END CATCH)"
SET ID=%errorlevel%
echo ID=%ID% >> %cmdlog%

Set /A COUNT=1

:CHECK
   sqlcmd -S %ServerName% -U %LoginUserID% -P %LoginPassword% -o %sqllog% -Q"EXIT(USE [msdb] BEGIN TRY DECLARE @restoring_status TABLE ( task_id int, task_type nvarchar(128), database_name nvarchar(128), complete int, duration int, lifecycle nvarchar(128), task_info nvarchar(max), last_updated datetime, created_at datetime, S3_object_arn nvarchar(128), overwrite_S3_backup_file int, KMS_master_key_arn nvarchar(128), filepath nvarchar(128), overwrite_file int ); INSERT INTO @restoring_status EXEC [dbo].[rds_task_status] [%Database%],[%ID%] SELECT CASE WHEN lifecycle = 'SUCCESS' THEN 1 WHEN lifecycle = 'ERROR' THEN 2 ELSE 3 END FROM @restoring_status END TRY BEGIN CATCH SELECT ERROR_NUMBER(),ERROR_MESSAGE() END CATCH)"
   SET LifeCycle=%errorlevel%
   echo LifeCycle=%LifeCycle% >> %cmdlog%

   If %LifeCycle%==1 (
      echo DB restore suucess. >> %cmdlog%
   ) Else If %LifeCycle%==2 (
      echo DB restore error. >> %cmdlog%
      exit /b 100
   ) Else (
      If %COUNT% gtr 120 (
         echo DB restore timeout. >> %cmdlog%
         exit /b 200
      )
      If %COUNT%==1 (
         @echo|set /p="wait"
      )
      echo Restoring %Database% count=%COUNT% >> %cmdlog%
      @echo|set /p="."
      Set /A COUNT+=1
      powershell sleep 5
      GOTO CHECK
   )

REM PWSP-セキュリティ-ユーザー-削除
sqlcmd -S %ServerName% -U %LoginUserID% -P %LoginPassword% -d "msdb" -o %sqllog% -Q "EXIT(USE [%Database%] BEGIN TRY DROP USER IF EXISTS [%UserID%] SELECT 0 END TRY BEGIN CATCH SELECT ERROR_NUMBER(),ERROR_MESSAGE() END CATCH)"
echo Drop User %UserID%. %errorlevel% >> %cmdlog%

REM データベース-セキュリティ-ログイン更新・マッピング
If %errorlevel%==0 (
   sqlcmd -S %ServerName% -U %LoginUserID% -P %LoginPassword% -d "msdb" -o %sqllog% -Q "EXIT(USE [msdb] BEGIN TRY ALTER LOGIN [%UserID%] WITH PASSWORD=N'%Password%'; USE [%Database%] CREATE USER [%UserID%] FOR LOGIN [%UserID%] ALTER USER [%UserID%] WITH DEFAULT_SCHEMA=[dbo] ALTER ROLE [db_owner] ADD MEMBER [%UserID%] ;  SELECT 0 END TRY BEGIN CATCH SELECT ERROR_NUMBER(),ERROR_MESSAGE() END CATCH)"
)
echo Login %UserID%. %errorlevel% >> %cmdlog%

REM 接続確認
If %errorlevel%==0 (
   sqlcmd -S %ServerName% -U %UserID% -P %Password% -o %sqllog% -Q "EXIT(BEGIN TRY SELECT Getdate() SELECT 0 END TRY BEGIN CATCH SELECT ERROR_NUMBER(),ERROR_MESSAGE() END CATCH)"
)
echo Getdate %UserID%. %errorlevel% >> %cmdlog%

REM 終了
echo DB setup Result=%errorlevel% >> %cmdlog%
exit /b %errorlevel%

TIPS

  • オンプレサーバー用のDB復元の自動化方法はこちらをご覧下さい。

コメント

タイトルとURLをコピーしました