SQLServerでデータベースのリストアを自動化する

システム保守をやっていると本番データベースと同じデータの状態でシステム操作を試してみたいことがあります。

お客様のデータである本番データベースをいじることはできないので、こんな時は日次で取得しているデータベースバックアップを別サーバー(検証用)にコピーして復元できるようにすると便利です。

ただ、毎回本番バックアップをコピー→リストア→ユーザーマッピングの操作を手動でしているので面倒くさいと思っていました。そこで今回はDBバックアップのコピー→リストア→ユーザーマッピングの一連の作業をバッチ化して自動化してみました。

以下のバッチでデータベースのバックアップを本番環境から検証環境にコピーしてリストアとユーザーマッピングをすべて一括で実施して検証環境に復元してくれます。

※くれぐれも復元先を誤るととんでもないことになるので注意してください。

前提条件
自動リストアバッチ

前提条件

本番環境(サーバー）の所定の場所に決められたファイル名でDBバックアップが取得されている。
本番環境と検証環境のサーバーが別々になっている。
本番環境と同じ環境のDBが検証環境にも既に存在しており、データベースが最新に置き換わっても問題ない。
検証サーバから本番サーバにネットワーク接続ができる状態であること。
本番＆開発環境共にDB名などの構成が同じであること
リストア後にマッピングできずに不明ユーザーになるユーザーを把握していること。

※把握していない場合は1度手動でリストアして不明ユーザーを確認してください。
確認方法は以下の記事にも記載しています。

自動リストアバッチ

バッチの内容

バッチを格納したフォルダにディレクトリを移動
本番サーバーのDBバックアップファイルを検証用サーバにコピーしてくる。
osql(sqlcmd)でDB_RESTORE.sqlを実行。
データベースを強制的にシングルユーザーモードに変更
リストアの実行
データベースをマルチユーザーモードに戻す
ユーザーマッピングでリストア後の不明ユーザーを解消する

echo off

rem バッチが格納されているディレクトリに移動
PUSHD X:xxxx\xxxx\xxxx\
rem 本番サーバーから検証環境のバッチが格納されているディレクトリにバックアップファイルをコピー
robocopy \\xxxxx\xxxx(本番サーバのディレクトリ) \\xxxxx\xxxx(検証サーバのディレクトリ) xxxxxx.bak

echo リストア開始 %DATE% %TIME%
echo リストア中です.....
osql -S xxxxx\xxxx(サーバホスト名\インスタンス名) -U (ユーザー名) -P (パスワード) -i DB_RESTORE.sql -o DB_RESTORE.log
echo リストア完了 %DATE% %TIME%
pause

select GETDATE() AS 開始時刻
go
--シングルユーザーモードにする
ALTER DATABASE [xxxx(DB名)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--リストア
RESTORE DATABASE [xxxx(DB名)] FROM  DISK = N'X:\xxxx\xxxx.bak' WITH  FILE = 1, REPLACE,  STATS = 5
--マルチユーザーモードに戻す
ALTER DATABASE [xxxx(DB名)] SET MULTI_USER
go


USE [xxxx(DB名)]
GO
--不明ユーザーのマッピング
EXEC sp_change_users_login 'Update_One', 'xxxx1(不明なユーザー)', 'xxxx1(不明なユーザー)'
EXEC sp_change_users_login 'Update_One', 'xxxx2(不明なユーザー)', 'xxxx2(不明なユーザー)''

GO

select GETDATE() AS 終了時刻
go

上記バッチファイルとsqlファイルをPUSHDで指定したフォルダに格納したらOKです。

バッチの簡単な解説

PUSHDは以下の構文で引数にしたフォルダにカレントディレクトリを変更します。

PUSHD [パス | ..]

robocopyは、以下の構文でファイルをコピーしてくるコマンドです。

ROBOCOPY コピー元(本番サーバディレクトリ) コピー先(検証サーバディレクトリ) ファイル名(データベースのバックアップファイル)

osql(sqlcmd)は、バッチでsqlを実行するコマンドです。ここでは-S (サーバー名\インスタンス) -U (ユーザー名) -P (パスワード) -i DB_RESTORE.sql(実行するSQLが書かれたファイル)-o 実行結果を出力するファイル）

※osqlは、古いコマンドなので通常はsqlcmdを使用します。ただし、-oで出力している場合は、なぜかsqlcmdだとリアルタイムにログが出力されません。なので私はosqlcmdを利用しています。指定するオプションなどは双方ともに同じです。osqlは将来的になくなる可能性もあるので注意してください。

SQLの簡単な解説

ALTER DATABASE [xxxx(DB名)] SET SINGLE_USER WITH ROLLBACK IMMEDIATEは、DBにほかのユーザーが接続していたり、接続情報が残っていると
リストアが失敗するので強制的にマルチユーザーモードからシングルユーザーモードにしてほかに接続しているユーザーを強制的に切断します。
くれぐれも本番環境でやってしまうと、大問題になりますので気を付けてください。他のユーザーの接続情報が残っていた場合に起きる事象は
以下の記事を参考にしてください。

【SQLServer】リストア時に'排他アクセスを獲得できませんでした。'が表示される

【SQLServer】データベースの復元に失敗しましたとなった場合の対処法。追加情報：データベースは使用中なので、排他アクセスを獲得できませんでした。原因はリストアデータベースが他のユーザーに使われているからです。

RESTORE DATABASE～は、データベースをるストアするコマンドです。指定したオプションはファイル識別番号1を上書きでリストアするオプションでログに出力される進捗状態を5%ごとに表示しています。FILE = 1は追記でDBバックアップを取得していたり、差分バックアップなどのリストアをしない限りは1で問題ありません。

ALTER DATABASE [xxxx(DB名)] SET MULTI_USERは、リストア前にシングルユーザーモードにした設定を元に戻す設定です。
このコマンドは通常であれば、自動でマルチユーザーモードに戻るみたいですが、戻らないことがあるため念のために記載しています。

【SQLServer】本番環境のデータベースを別のサーバーに自動でリストアさせる