SQL ServerのDB復元の自動化（オンプレ）- ほのぼの万事屋

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

ユーザー作成
DB復元
TIPS

ユーザー作成

batファイルは以下のように作成します。

@echo off

set CWD=%~dp0

REM DBサーバー名
Set ServerName=localhost\<DBインスタンス名(e.g. SQLEXPRESS)>

REM ユーザー情報
Set UserID=<作成したいユーザー名>
Set Password=<作成したいユーザーのパスワード>

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 データベース-セキュリティ-ログイン-作成
sqlcmd -S %ServerName% -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=[English], 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)"

REM VIEW SERVER STATE権限付与
If %errorlevel%==0 (
   sqlcmd -S %ServerName% -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%

DB復元

batファイルは以下のように作成します。

@echo off
set CWD=%~dp0

REM DBサーバー名
Set ServerName=localhost\<DBインスタンス名(e.g. SQLEXPRESS)>

REM ユーザー情報
Set UserID=<上記で作成したユーザー>
Set Password=<上記で作成したユーザーのパスワード>

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

REM DBファイル
Set inFilePath=<復元したいDBのファイルパス(e.g. C:\sqlserver\db.bak)>

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

REM mdf,ldfファイル
Set sqlserverDataPath=<mdf,ldfファイルの保管先(e.g. C:\sqlserver\data)>

REM mdf,ldfの論理名
Set DBFileLogicalName=<mdf,ldfの論理名(e.g. データベース名と同じ)>

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

REM ログ出力
Set sqllog="%LogPath%\RestoreSql.log"
Set cmdlog="%LogPath%\RestoreBat.log"

REM 絶対パスに変換
call :GET_FILENAME "%inFilePath%"
Set inFilePath=%TmpPath%

call :GET_FILENAME "%sqlserverDataPath%"
Set sqlserverDataPath=%TmpPath%

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

REM mdf,ldf格納先存在チェック
if not exist "%sqlserverDataPath%" (
    mkdir "%sqlserverDataPath%"
)

REM echo "EXIT(USE [master] BEGIN TRY RESTORE DATABASE [%Database%] FROM DISK = N'%inFilePath%' WITH FILE = 1,  MOVE N'%DBFileLogicalName%' TO N'%sqlserverDataPath%\%Database%.mdf',  MOVE N'%DBFileLogicalName%_log' TO N'%sqlserverDataPath%\%Database%_log.ldf',  NOUNLOAD,  STATS = 5 SELECT 0 END TRY BEGIN CATCH SELECT ERROR_NUMBER(),ERROR_MESSAGE() END CATCH)"

REM 復元開始
sqlcmd -S %ServerName% -U %UserID% -P %Password% -d "master" -o %sqllog% -Q "EXIT(USE [master] BEGIN TRY RESTORE DATABASE [%Database%] FROM DISK = N'%inFilePath%' WITH FILE = 1,  MOVE N'%DBFileLogicalName%' TO N'%sqlserverDataPath%\%Database%.mdf',  MOVE N'%DBFileLogicalName%_log' TO N'%sqlserverDataPath%\%Database%_log.ldf',  NOUNLOAD,  STATS = 5 SELECT 0 END TRY BEGIN CATCH SELECT ERROR_NUMBER(),ERROR_MESSAGE() END CATCH)"

REM PWSP-セキュリティ-ユーザー-削除
If %errorlevel%==0 (
   sqlcmd -S %ServerName% -U %UserID% -P %Password% -d "master" -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)"
)

REM データベース-セキュリティ-ログイン更新・マッピング
If %errorlevel%==0 (
   sqlcmd -S %ServerName% -U %UserID% -P %Password% -d "master" -o %sqllog% -Q "EXIT(USE [Master] 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)"
)

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)"
)

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

GOTO :END

:GET_FILENAME
rem -％変数 を完全修飾パス名に展開します。
Set TmpPath=%~f1
GOTO :EOF

:END
exit /b %errorlevel%