今回はSQL Serverのバックアップとリストアを自動化する方法を紹介いたします。
バックアップとリストアを自動化することで、本番環境のDBサーバのデータをテスト環境のDBサーバに日次で上書きするといった環境も作れるので、色々と便利です。
ただ、私の会社の環境は「SQL Server 2000」がガッツリ稼働している残念な環境なので、2000環境を前提としています。
ただ、おそらく最新の環境じゃなければ、もう少し新しいSQL Server環境でも動くと思います。
2020年1月追記
当記事の内容はSQL Serverの2008及び2017でも問題なく対応出来ることを確認しました。
実装構成
- 本番DBとテストDBは異なるインスタンス(更に筐体も別)に構築。
- データのダンプ対象はデータベース単位でフルバックアップ。
- リストア対象はデータベース単位で上書きリストア。
- バックアップ処理及びリストア処理はEnterprise Manager(EM)のジョブからコマンド実行。
こんなような前提です。
処理の流れは以下です。
- 本番DBサーバのEMのジョブからデータベースを指定してダンプを実行。
- 本番DBサーバからダンプしたバックアップファイルをテストDBサーバにバッチ処理で転送。
- テストDBサーバのEMのジョブから、対象のデータベースとバックアップファイルを指定してリストア。
- テストDBサーバのリストア後のデータベースではデータベース内のユーザー情報紐付けが外れるので、再紐付け処理をバッチで実施。
といった流れです。
では、以下の項目から順番に処理を作っていきましょう。
環境構築手順
本番DBサーバにてデータベースダンプ用ジョブ作成
本番DBサーバのEMを起動し「SQL Server エージェント」内のジョブを表示させます。
新規ジョブの設定画面を表示させ、設定項目の種類には「Transact-SQL スクリプト(TSQL)」を選択し、コマンドは以下の構文で記述してください。
■コマンド
BACKUP DATABASE [対象のデータベース名] TO DISK = N'バックアップファイル出力先フルパス※ファイル名を含む' WITH INIT , NOUNLOAD , NAME = N'設定したジョブ名称', NOSKIP , STATS = 10, NOFORMAT
後は実行時間などその他の項目も設定し、スケジューリングを有効化しておいてください。
ダンプしたバックアップファイルの転送処理
これはbatファイルやvbsなどでファイルコピーを行うバッチ処理を作ってタスクスケジューラーで自動実行されるように設定する工程ですが、ファイルコピーのバッチ処理は当記事の主題ではないので割愛します。
テストDBサーバにてデータベースリストア用ジョブ作成
テストDBサーバのEMを起動し「SQL Server エージェント」内のジョブを表示させます。※本番DBサーバの画面と同じなので画像は割愛
新規ジョブの設定画面を表示させ、設定項目の種類には「Transact-SQL スクリプト(TSQL)」を選択し、コマンドは以下の構文で記述してください。※画像割愛
■コマンド
RESTORE DATABASE データベース名 FROM DISK = N'リストア対象のバックアップファイルのフルパス' WITH REPLACE
後は実行時間などその他の項目も設定し、スケジューリングを有効化しておいてください。
リストア後のDBユーザーマッピング用バッチ処理作成
SQL Serverではリストア直後のデータベースに対して、元々使用していたユーザーで接続はできません。
これはリストア時にデータベース内のユーザー情報も復元され、内部的にはユーザーは存在するのですが、データベースとユーザーの内部的な紐付けが外れてしまっている状態になるからです。※新規に同じ名前のユーザーを作成しようとしても、内部的にはそのユーザーが存在するのでエラーになります。
その為、再度紐付け(マッピング)を行う処理を実施する必要があります。
ファイルを二つ作成します。
User_Mapping_Script.sql ←再マッピングするT-SQLを記述します。
DB_User_ReMapping.bat ←SQL Serverにコマンドでログインし上記スクリプトを流し込みます。
尚、上記のファイル名は仮で定義していますが、任意の名称で結構です。
ファイルに記述するコマンドは以下です。
■User_Mapping_Script.sql ※以下の’DBユーザー名’には同じ値を設定します。
EXEC sp_change_users_login 'Update_One', 'DBユーザー名', 'DBユーザー名' GO
■DB_User_ReMapping.bat
CD "User_Mapping_Script.sqlが置いてあるディレクトリへ移動" osql -U sa -P saのパスワード -S localhost※インスタンス名 -d データベース名 -i User_Mapping_Script.sql -o 実行ログを出したい場合はoオプションでパス指定￥Mapping.log exit
DB_User_ReMapping.batをタスクスケジューラーに設定しておくことで、リストア実行後にDBユーザーの再紐づけ処理が実行され、本番DBで使用しているユーザーでデータベースに接続することが出来ます。
注意事項
今回紹介した処理では、初回実行時だけはデータベースをリストアする際に、テストDBサーバ側にリストア対象のデータベースと同じ名称の空のデータベースを作っておく必要があったような気がします。
今回紹介した処理自体は今も日次で動いているのですが、当処理を運用に載せたのはかなり以前なので記憶が曖昧です。すみません・・・。
2020年1月追記
新しく構築したSQL Serverのインスタンスに空のデータベースを作成して、初回のリストアを実施した場合、リストア後にデータベースのログインユーザーだけは手動で作成する必要が有ります。
手動で作成するログインユーザーは、ユーザー名とパスワードはパックアップ元と合わせておき、それ以外の設定項目はその後に再マッピングのコマンドを流せば、バックアップ元の設定で反映されます。
以降のリストアでは当処理は不要です。
後、データベースサーバのバックアップとリストアは一つ作業を間違えると大惨事に繋がる為、もし実際に今回の処理を構築する場合は、ミスをしないようにくれぐれもご注意ください。
では今回も読んで頂きましてありがとうございました。
