2004年2月26日
『一括ログ記録型復旧モデルの注意点』
PASSJ Conference 2004 のビギナートラックでは多勢の方にご参加いただき、
ありがとうございました。
< http://www.sqlpassj.org/conf2004/speaker.aspx#Begin01
>
話すテーマが多くてゆっくりとお話できなくてすいません。
その中で、『一括ログ記録型』の復旧モデルに関する内容は非常に重要です。
当日はデモ操作をしながらお話する予定でしたが、デモを省略したため、
今ひとつその内容が理解できなかった方もおられると思います。
そこでデモで使う予定だったプログラムを解説したいと思います。
ぜひ実際に動かして体験して下さい。
--------------------------------------------------------------------
（１）データベースの新規作成
--------------------------------------------------------------------
CREATE DATABASE TEST
ON PRIMARY
(
NAME
= TEST_DAT ,
-- 論理ファイル名
FILENAME
= 'D:\DB\TEST.MDF' --
物理ファイル名
)
LOG ON
(
NAME
= TEST_LOG , --
論理ファイル名
FILENAME
= 'D:\DB\TEST.LDF'
-- 物理ファイル名
)
まず最初にデータベースを作成します。
ドライブ名やサブディレクトリ名などを書き換えて、実行します。
--------------------------------------------------------------------
（２）データベースの復旧モデルを「一括ログ記録」に変更する
--------------------------------------------------------------------
ALTER DATABASE TEST SET RECOVERY BULK_LOGGED
SELECT DATABASEPROPERTYEX( 'TEST' , 'RECOVERY' )
（１）で作成したTESTデータベースの復旧モデルを、一括ログ記録型（BULK_LOGGED）に変更します。MSDE 2000
で作成したデータベースの復旧モデルの初期値は、シンプル型になっています。
SQL Server 2000 で作成したデータベースの復旧モデルの初期値は、フル型になっております。
このため通常時は、一括ログ記録型に設定することはあまりありません。
DATABASEPROPERTYEX 関数は、データベースのオプション状態などを調べることができます。
--------------------------------------------------------------------
（３）テーブルを作成し、レコードを挿入する
--------------------------------------------------------------------
USE TEST
GO
CREATE TABLE TBL(
ID
INT PRIMARY KEY ,
DT
VARCHAR(10)
)
GO
INSERT INTO TBL(ID,DT) VALUES(1,'A')
INSERT INTO TBL(ID,DT) VALUES(2,'B')
INSERT INTO TBL(ID,DT) VALUES(3,'C')
INSERT INTO TBL(ID,DT) VALUES(4,'D')
INSERT INTO TBL(ID,DT) VALUES(5,'E')
GO
--------------------------------------------------------------------
（４）データベースの完全バックアップを実行する
--------------------------------------------------------------------
BACKUP DATABASE TEST TO DISK='D:\DB\TEST.BAK'
TESTデータベースの完全バックアップを実行します。
このバックアップファイルの中には、(3)で実行したレコード挿入結果が保存されております。
（３）の状態に、データベースを戻すことができます
--------------------------------------------------------------------
（５）SELECT INTO 命令を実行します
--------------------------------------------------------------------
USE TEST
GO
SELECT * INTO TBL_COPY FROM TBL
GO
INSERT INTO TBL(ID,DT) VALUES(10,'X')
INSERT INTO TBL(ID,DT) VALUES(11,'Y')
INSERT INTO TBL(ID,DT) VALUES(12,'Z')
GO
SELECT INTO命令を実行します。
SQL Server 7 では、この命令は「ログに記録されない命令」と 呼ばれており、
この命令の実行は管理者によってその実行が禁止されておりました。
管理者にその命令の実行を許可して頂き、命令の実行が終わった後に
データベースの完全バックアップを実行する必要がありました。
SQL Server 2000では、テーブル作成権限があれば、いつでも SELECT
INTO命令の実行ができるようになりました。
しかしデータベースの復旧モデルが「一括ログ記録型」の場合は、注意が必要になります。
その注意点を理解するのが、これら一連のデモプログラムです。
--------------------------------------------------------------------
（６）ここでデータベースのサービスを止めて、TEST.MDFファイル名の変更
--------------------------------------------------------------------
（５）を実行した後に、データベースが壊れたことを想定します。
データベースを壊す方法で一番簡単な方法は、サービスを止めて、プライマリ
データファイル（MDFファイル）を削除することです。
ここでは、ファイル拡張子を変更します。
D:\DB\TEST.MDF
のファイル名を変更します。例えば、D:\DB\TEST.BAD とします。
--------------------------------------------------------------------
（７）サービス再開後、TESTデータベースが壊れていることを確認
--------------------------------------------------------------------
SELECT DATABASEPROPERTYEX( 'TEST'
, 'Status') AS 現在状態
DATABASEPROPERTYEX関数を使って、データベースが壊れていることを確認します。
SUSPECT と表示されるはずです。
このSUSPECTとは、そのデータベースが壊れていることを意味します。
--------------------------------------------------------------------
（８）トランザクションログファイルのバックアップの実行
WITH NO_TRUNCATEオプション付きで実行
--------------------------------------------------------------------
BACKUP LOG TEST
TO DISK='D:\DB\TESTLOG.BAK'
WITH NO_TRUNCATE
データベースが壊れたら、まず最初に、トランザクションログファイルの
バックアップを実行します（WITH NO_TRUNCATEオプションが必要）
ここで正常にログのバックアップが取れたら、そのログの中には、（５）の
レコード操作記録が含まれます。
そこで、（４）の完全バックアップのファイルと、このログのバックアップファイル
によって、データベースが壊れた直前まで、復元できることになります。
ところが、「一括ログ記録」モードでは、このログのバックアップは失敗します。
このエラーを確認することが、重要です。
「一括ログ記録」のログのバックアップ操作では、そのログの中に、SELECT
INTOなどの命令が実行されていたら、データファイルの内容を参照します。
データファイルの内容が参照できなければ、ログのバックアップは失敗します。
SQLServer7では、SELECT INTO命令を実行した直後にデータベースの
完全バックアップが必要でしたが、SQLServer2000では、そのバックアップを
実行するタイミングがログのバックアップまで遅らせることができるようになりました。
ところがログのバックアップを行なうときに、データファイルの参照ができなければ
ログのバックアップが失敗することを、ご存知無い方もおられます。
このようなリスクが『一括ログ記録型』にはあるということをぜひ覚えて欲しいものです。
データベースをフルモードにすれば、データファイルが壊れても、ログのバックアップができます。
このような理由から、データベースの復旧モードは、フル型にしておきましょう。
|
