SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

第 1 回 Get The Fact セミナーの振り返り その 8

leave a comment

今回は [データベースのリストア] について振り返っていきたいと思います。

バックアップポリシーの中で、以下のような図を使用してバックアップについての説明がありました。
image

セミナーの中で、障害発生のタイミングでデータベースの障害が発生した場合どのタイミングまでリストアすることが可能なのかという質問がありました。

この質問の内容を元にリストアについてまとめていきたいと思います。

■環境の準備


障害発生の検証を行うために以下のような環境を用意しました。
image

[RESTORE_TEST] というデータベースに [TEST] というテーブルを作成しています。
# 復旧モデルは [完全復旧モデル] としています。

このテーブルは単一の列を持つ単純な構造となっています。
image

このテーブルに以下のようなクエリを実行してデータを挿入します。

SET NOCOUNT ON
GO
USE [RESTORE_TEST]
GO
INSERT INTO
    [TEST]
VALUES
    (REPLICATE(‘A’, 10))
GO 5

クエリを実行すると以下のデータが挿入された状態となります。
image

この状態を [完全バックアップ] で取得します。
image
取得したバックアップを最初の状態の [月 22:00] に取得したものとします。

続いて、以下のクエリを実行します。

SET NOCOUNT ON
GO
USE [RESTORE_TEST]
GO
INSERT INTO
    [TEST]
VALUES
    (REPLICATE(‘B’, 10))
GO 5

このクエリを実行した後のデータ状態は以下のようになります。
image

それでは、この状態のバックアップを [トランザクションログ バックアップ] で取得します。
image

このバックアップを [火 12:00] に取得したものとします。

もう一つ、ログのバックアップを取得するために以下のクエリを実行してデータを挿入します。

SET NOCOUNT ON
GO
USE [RESTORE_TEST]
GO
INSERT INTO
    [TEST]
VALUES
    (REPLICATE(‘C’, 10))
GO 5

このクエリを実行すると以下のデータの状態となります。
image

それでは、この状態をトランザクションログのバックアップとして取得します。
image

このバックアップが [火 18:00] に取得されたものとします。

これで、[一つの完全バックアップ] と [二つのトランザクション ログバックアップ] が取得された状態となります。
image

最後に以下のクエリを実行して [火 18:00] 以降に追加されたデータとして挿入します。

SET NOCOUNT ON
GO
USE [RESTORE_TEST]
GO
INSERT INTO
    [TEST]
VALUES
    (REPLICATE(‘D’, 10))
GO 5

データとバックアップの関係は以下のようになります。
image

最後の [D] のデータはどのバックアップにも含まれていないものになります。

■障害を発生させる


[火 19:00] に発生した障害として、一度 SQL Server のサービスを停止して、[RESTORE_TEST] データベースのプライマリデータベースファイル (mdf) を破損させたいと思います。

破損ですが、バイナリエディタで mdf ファイルを開いてすべての内容を 0 で初期化しています。
# これでデータファイルには何もデータが入っていない状態となります。
image

この状態では、データベースがファイルとして成り立っていないため Management Studio からデータベースを展開することもできない状態となっています。
image

■ログ末尾のバックアップの取得


この状態になった場合、[C] のデータが入ったバックアップまではファイルとして取得されているため、既存のバックアップを使用すると、[火 18:00] の状態まではリストアすることが可能です。

[D] のデータに関しては、どのバックアップにも含まれていませんので、既存のバックアップからのリストアだけでは消失 (ロスト) するデータが出てしまいます。

このようなときに取得するのが [ログ末尾のバックアップ] (Tail log Backup) になります。

トランザクション ログが破損している場合は取得ができないのですが、データファイルが破損しておりログが正常な場合にはログ末尾のバックアップ (最後に取得したトランザクション ログのバックアップから現時点のログに含まれている内容) を取得することができます。

ログ末尾のバックアップを取得する場合のクエリは以下のようになります。

BACKUP LOG [RESTORE_TEST]
TO  DISK = N’H:SQL2008R2RESTORE_TEST_TailLog.trn’
WITH  NO_TRUNCATE , NOFORMAT, NOINIT, 
NAME = N’TEST-トランザクション ログ  バックアップ’,
SKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 10
GO

[NO_TRUNCATE] (ログを切り捨てないで取得するバックアップ) を使用することで、ログ末尾のバックアップが取得できます。
# ログ自体が破損している場合は [CONTINUE_AFTER_ERROR] を指定することで取得できる可能性があります。

ログ末尾のバックアップと故障していますがバックアップの種別としては、通常のトランザクション ログのバックアップになります。

取得したログ末尾のバックアップをバイナリエディタで開いてみます。
image

[INSERT] で [DDDDDDDDDD] のデータを挿入した際に記録されたと思われるログがバックアップされているのが確認できますね。

■データベースのリストア


実際のデータベースのリストアの流れを見ていきたいと思います。
今回の環境ではリストアは以下の順番で実施します。

image

 

  1. MON_2200.bak をリストア
    データベースが破損している状態では、Management Studio の GUI からはリストアが選択できないことがあります。
    image

    このような場合は、クエリでリストアを行います。

    RESTORE DATABASE [RESTORE_TEST] FROM 
    DISK = N’H:SQL2008R2MON_2200.bak’
    WITH FILE = 1,
    NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10

    [NORECOVERY] を指定して復元をすることで、追加のバックアップをリストアできる状態になります。
    image

  2. TUE_1200.trn / TUE_1800.trn / ログ末尾のバックアップをリストア
    完全バックアップのリストアができると、Management Studio の GUI からリストアができるようになります。
    image
    [msdb] の [dbo.backupset] テーブルにバックアップのレコードが残っている場合は、以下のように取得しているログのバックアップが自動的に選択されるので選択されたバックアップをリストアします。
    # backupset にレコードが無い場合は、手動でバックアップを選択します。
    image

以上でリストアは完了です。
データを確認してみると、すべてのデータを復元でき手いることが確認できます。
image

このようなリストアをする場合はトランザクション ログのバックアップが必要となるため、復旧モデルを [完全] または、[一括ログ] にする必要があります。
単純復旧モデルでは、トランザクション ログのバックアップを取得できないため、トランザクション ログ / ログ末尾のバックアップを利用したリストアをすることができません。
# 単純復旧モデルでは完全バックアップを取得したタイミングまでしかリストアができません。

 

■トランザクション ログの切り捨て


Twitter で質問があった内容なのですが、復旧モデルが [完全] [一括ログ] の場合は、トランザクション ログのバックアップを取得しないとログが蓄積された状態となります。

トランザクション ログの記録ができないと以下のエラーが発生します。

メッセージ 9002、レベル 17、状態 2、行 1
データベース ‘TEST’ のトランザクション ログがいっぱいです。ログの領域を再利用できない理由を確認するには、
sys.databases の log_reuse_wait_desc 列を参照してください。

トランザクション ログがいっぱいになった場合、以下の方法のどちらかでトランザクション ログを切り捨てる必要があります。

  1. トランザクション ログのバックアップを取得
  2. 復旧モデルを [単純] に変更

SQL Server 2005 までは、[BACKUP LOG] を [WITH TRUNCATE_ONLY] で実行することでバックアップを取得しないでログを切り捨てることができましたが、SQL Server 2008 以降では、[TRUNCATE_ONLY] のオプションは廃止されているため使用することができません。

今回は、復旧モデルを [単純] にしてトランザクション ログの切り捨てを実行してみたいと思います。
[TEST] というデータベースのトランザクション ログをフルの状態にしてみました。
image

この状態で、復旧モデルを [単純] に変更します。
image

変更後に再度トランザクション ログの使用状況を確認してみます。
image

[Log Space Used (%)] が減っている (トランザクション ログが切り捨てられている) ことが確認できます。

SQL Server 2008 以降は [TRUNCATE_ONLY] が使えないため、バックアップの取得領域を確保できない場合には復旧モデルを変更して、ログの切り捨てを実行する必要があります。

 

可能な限りのデータリストアを可能とするためには [ログ末尾のバックアップ] を取得する必要がありますので、[完全] [一括ログ] のいずれかを設定しておく必要があります。

この後はデータベースのデタッチ / アタッチを使用したデータベースの移動についてのお話がありました。

次の投稿では、デタッチ / アタッチを使用したデータベースの移動についてまとめていきたいと思います。

Written by masayuki.ozawa

1月 1st, 2011 at 7:29 pm

Leave a Reply

*