今回は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サーバー側に、リストア対象のデータベースを予め手動で作成しておきます。
その際には、テーブルなどを作る必要はなく、空のデータベースがあれば良いです。
この作業は初回のみ必要です。
また、作成するデータベースの照合順序や互換性レベルなどの基本的な設定は、本番側のデータベースと合わせておきます。
本番DBサーバにてデータベースダンプ用ジョブ作成
本番DBサーバのEMを起動し「SQL Server エージェント」内のジョブを表示させます。
新規ジョブの設定画面を表示させ、設定項目の種類には「Transact-SQL スクリプト(TSQL)」を選択し、コマンドは以下の構文で記述してください。
■コマンド
BACKUP DATABASE [対象のデータベース名] TO DISK = N'バックアップファイル出力先フルパス※ファイル名を含む' WITH INIT , NOUNLOAD , 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サーバーと同じ名前、同じパスワード、同じロールを設定してください。
データベース個々のユーザーはデータベースのバックアップデータ内に含まれていますが、インスタンス自体の「ログイン」プリンシパルの情報は別で管理しています。
インスタンス側の「ログイン」にユーザーを作成しておかないと、上記の「sp_change_users_login」実行時に「ログイン」ユーザーが存在せずにエラーになります。
後、データベースサーバのバックアップとリストアは一つ作業を間違えると大惨事に繋がる為、もし実際に今回の処理を構築する場合は、ミスをしないようにくれぐれもご注意ください。
では今回も読んで頂きましてありがとうございました。
