SQL Server 2000バックアップとリストア
SQL Serverのバックアップとリストアにトライしてみた。
バックアップ方式はフルバックアップだけだと、夕方に障害が起きた場合など一日分の更新が無効になってしまうので、トランザクションログをとり、なるべく直近のデータベースの状態に戻せるようにしました。
まず、トランザクションログを取りたいデータベースのプロパティの復旧モデルはフルに設定します。バックアップはEドライブの外付けディスクに取るとします。
[バックアップ]
フルバックアップ
BACKUP DATABASE test_db TO disk='e:\backup\full.bak' WITH INIT
BACKUP DATABASE master TO disk='e:\backup\full.bak' WITH NOINIT
BACKUP DATABASE model TO disk='e:\backup\full.bak' WITH NOINIT
BACKUP DATABASE msdb TO disk='e:\backup\full.bak' WITH NOINIT
トランザクションバックアップ 一日の最初の一回目
BACKUP LOG test_db TO disk='e:\backup\log.bak' WITH INIT
トランザクションバックアップ 一日の２回目以降
BACKUP LOG test_db TO disk='e:\backup\log.bak' WITH NOINIT
WITH INIT は ファイルを初期化するという意味です。一日の最初のバックアップでは WITH INIT オプションをつけます。これらのコマンドはジョブに登録してフルバックアップならば夜に一回、トランザクションバックアップならば、一日に一定間隔でとうるようにスケジューリングします。WITH NOINITオプションは同じファイルにバックアップデータを追加する場合です。
[リストア]
障害が起きた場合は、バックアップファイルよりデータを元に戻す復旧作業を行います。クエリーアナライザなどにより、以下のようなコマンドを実行します。この場合はフルバックアップ後、トランザクションログが３回実行された時点まで、test_dbのデータを元に戻します。リストアの最中はデータベースにアクセスするアプリケーションを停止したりLANを抜く、無効化する等してデータベースのアクセスを阻止します。
restore database test_db from disk='e:\backup\full.bak'
with replace, norecovery,file=1
go
restore log test_db from disk='e:\backup\log.bak'
with replace, norecovery,file=1
go
restore log test_db from disk='e:\backup\log.bak'
with replace, norecovery,file=2
go
restore log tet_db from disk='e:\backup\log.bak'
with replace, recovery,file=3
go
fileオプションはファイルナンバーと呼ばれるもので、同じファイルにバックアップした順に付与される番号です。この例ですとフルバックアップはtest_dbが最初に実行されています。またトランザクションログはlog.bakファイルに取られた順にファイルナンバーが付与されます。ファイルナンバーを確認したい場合は以下のコマンドを実行してみてください。
restore headeronly
from disk = N'e:\backup\log.bak'
with nounload;
go
列Positionにfile=のオプションで使用されるファイルナンバーが表示されます。
recoveryオプションはバックアップがとられた時点で中途半端で実行されたかった（コミットされなかった）データ更新を切り捨てます。リストアはこの例のように restore database と restore log を何回か使ってデータの復元を行い一番最後は recoveryオプションで それ以前は norecoveryオプションでむやみに中途半端なデータ更新を切り捨ててしまうことを防止します。replaceオプションはデータベースを強制上書きするものです。
[osql]
osqlバッチコマンドでコマンドプロンプトからもSQL Serverのコマンドを実行できます。
osql -Usa -Ppassword -S%COMPUTERNAME% -iコマンドファイル.txt
のようなバッチファイルを作り、コマンドファイル.txtにSQL Serverのコマンドを記述し、コマンドプロンプトからバッチファイルを実行します。-Uオプションはログイン名です。-Sオプションはサーバー名です。バッチコマンドの%COMPUTERNAME%はコンピュータ名を示します。
[masterデータベースのリストア]
masterデータベースをリストアする場合はシングルユーザーモードという特殊な起動方法を使います。SQL Severが起動されている場合には以下のようなコマンドで一旦 SQL Server を停止させてから、シングルユーザーモードの起動を行います。その後通常のリストア操作を行います。
NET STOP MSSQLSERVER
"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr" -c -m
他のシステムDBやユーザーDBは以下のコマンドにより、SQL Serverを起動してからリストアを行います。
NET START MSSQLSERVER
最後にスケジュールジョブを使う場合は以下のコマンドでSQL SERVER AGENT を起動させます。
NET START SQLSERVERAGENT
[データベースの移行、移植]
インポートする手もありますが、同じSQL Server間でデータベースの移植を行うには、restoreでバックアップファイルからデータベースが移植できます。以下の例では、移植元と移植先のデータベースファイル場所が違う場合でmoveオプションを使用しています。
use master
go
restore database test_db from disk='e:\backup\full.bak'
with replace, recovery,file=1,
move 'test_db_dat' to 'C:\Program Files\Microsoft SQL Server\MSSQL$マシン名\Data\test_db.mdf',
move 'test_db_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL$マシン名\Data\test_db.LDF'