データベース内の個々のページを復元する

まえがき

Gail Shaw の記事「ヘルプ、私のデータベースは壊れています。 さて 、先週私が投稿した翻訳は 、ある種の興味をそそりましたが、残念ながら「練習」は含まれていませんでした。 はい、データを保存する方法を説明していますが、例はありません。
最初は同じ著者の別の翻訳を作成したかったのですが、考えて、「動機に基づいて」のように「自分で」投稿を書くことにしました。 そうするように私を促した理由は、ポストの終わりに、ノートで説明します。

SQL Serverでのデータベースの回復

前の記事で既に述べたように、クラスター化インデックスまたはヒープページが破損した場合、これらのページに含まれるデータは失われ、それらを復元する唯一のオプションはデータベースを直接復元することです。

SQL Serverは、多くのデータベース回復機能を提供します。 まず、データベース全体の復元です-多くの時間がかかります（データベースのサイズとハードドライブの速度に依存します）。 次に、データベースが複数のファイルグループ（またはそれぞれファイル）で構成されている場合、個々のファイルグループまたはファイルの復元。 この場合、データベースの損傷部分のみを復元し、残りに影響を与えることはありません。 これらの2種類のデータベース回復は非常に頻繁に使用され、将来的には影響を受けません。
第三に、SQL Server 2005では、個々のデータベースページを復元できるようになりました-この場合、指定したページのみがバックアップから復元されます。 DBCC CHECKDBが、巨大なファイルの「横たわる」いくつかの巨大なテーブルでいくつかの破損したページを検出した場合、このようなリカバリは特に重要になります。 ファイル全体が復元されるわけではなく、テーブル全体が復元されるわけではなく、数ページのみが復元されるため、ダウンタイムを大幅に削減できます。

要件と制限

復旧モデルとトランザクションログバックアップの可用性

覚えておくべき最も重要なことは、個々のページを復元することです。データベースは完全（完全）復旧モデル、またはログが不完全な（一括ログ）復旧モデルを使用する必要があります。 データベースが単純復旧モデルの場合、これ以上読むことはできません。
2番目の要件は、完全バックアップとトランザクションログのバックアップが、切っても切れない一連のログ（ ログチェーン ）を形成する必要があることです。 BACKUP LOG WITH TRUNCATE_ONLY（NO_LOG）コマンドを実行せず、トランザクションログを削減するために単純な復旧モデルに切り替えず、破損したページを含まない最後の完全バックアップ以降のすべてのトランザクションログバックアップがある場合（これを含む）最も完全なバックアップ）-雑誌のチェーンについて心配する必要はありません。
理論的には、ロギングが不完全なリカバリモデルでは、上記の条件が満たされ、最小ロギングで実行される操作によって復元されるページが変更されない場合、個々のページのリカバリは正常に機能します。

SQL Serverのエディション

SQL Serverのどのエディションでもページの復元が可能ですが、エディションEnterprise EditionおよびDeveloper Editionでは、破損したページをオンラインで復元できます。 リカバリ中にデータベースにアクセスできます（さらに、これらのページ自体が「影響を受けない」場合、現在復元中のページが属するテーブルを参照することもできます。そうでない場合、リクエストは失敗します）。 Enterprise Editionより下のエディションでは、ページのリカバリがオフラインで行われ、リカバリ中にデータベースが使用できなくなります。

破損したページタイプ

インデックスまたはデータページが破損した場合、Enterprise Editionでオンラインで回復できます。
重要なシステムテーブルにアタッチするページは復元できますが、データベースを復元すると、SQL Serverのどのエディションでも使用できなくなります。
「プレースメントカード」は「個別に」復元できません。 GAM、SGAM、PFS、ML、DIFFページが破損している場合は、データベース全体を復元する必要があります。 唯一の例外はIAMページです。 それらは「サイトマップ」を指しますが、データベース全体ではなく、1つのテーブルのみを記述し、それらの回復は可能です。
データベースの読み込みページ（最初のデータベースファイルの9ページ目）とファイルヘッダーページ（各ファイルの0ページ目）を個別に復元することはできません。破損している場合は、データベース全体を復元する必要があります。

実際には、回復

さて、最後に、理論から実践に移ります。
まず、トレーニングには、破損したデータベースが必要です。

ポートdb

実験には、SQL Serverに付属のAdventureWorksデータベースを使用します。 インストールしなかった場合、必要に応じて、 ここからダウンロードできます。 完全復旧モデルに転送します。

ALTER DATABASE AdventureWorks SET RECOVERY FULL 

私はまだエラーがないと確信しています：

 DBCC CHECKDB('AdventureWorks') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY 

完全バックアップを作成します。

 BACKUP DATABASE AdventureWorks TO DISK = 'D:\tmp\aw_backups\aw_full_ok1.bak' 

このデータベースでは、クラッシュテーブルを作成します。

 CREATE TABLE crash (txt varchar(1000)) 

varchar型フィールドを台無しにして、突然SQL Serverが彼自身が書き込んだデータではない場合に何が起こるかを確認します。
何かを台無しにする前に、それを何かで埋める必要があります。 作成したテーブルに左データを打ち込みます。

 SET NOCOUNT ON DECLARE @i INT SET @i = 1 WHILE @i<100000 BEGIN INSERT INTO crash SELECT REPLICATE('a', 1000) SET @i = @i + 1 END SET NOCOUNT OFF 

現在、トランザクションログをバックアップしています。

 BACKUP LOG AdventureWorks TO DISK = 'D:\tmp\aw_backups\aw_log_ok1.trn' 

次に、データを少し変更してみましょう。

これで、すべての準備が整いました。 データベースを切断し、FAR（またはより便利なもの）でmdfファイルを開き、その中の文字列「zzzzzzz」を探し、いくつかの「z」を任意の文字に置き換えます。

データベースが破損したため、接続します。 そして、はい、前の記事で、データベースを切断/接続する価値はないとはっきりと言われたことを覚えています。 しかし、私たちの場合、それは絶対に「安全」です-「疑わしい」データベースは落ちません。

エラーを探す

したがって、破損したデータベースは正常に動作に戻りました。 整合性チェックを再度実行します。

 DBCC CHECKDB('AdventureWorks') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY 

その結果、私たちが待っていたもの（破損したページの数を忘れないでください！ ）：

Msg 8928, Level 16, State 1, Line 1
Object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data): Page (1:20455) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data), page (1:20455). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'crash' (object ID 1883153754).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'AdventureWorks'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (AdventureWorks).
この場合、ヒープ（インデックスID = 0）上のデータ自体が破損しているため、SQL Serverはこのデータを回復できません。
次の3つのオプションがあります。

1. データ損失に対処し、DBCC CHECKDB（ 'AdventureWorks'、REPAIR_ALLOW_DATA_LOSS）を実行します
2. トランザクションログのアクティブな部分のバックアップを作成し、データベース全体を復元します。その結果、データの損失はありませんが、時間がかかります。
3. トランザクションログのアクティブな部分のバックアップを作成し、破損したページを1つだけ（！）復元します

2番目のオプションを使用すると、すべてが明確になりますが、DBCC CHECKDBを起動した場合、または個々のページがどのように復元されるかはどうなりますか。

破損したページを復元する

まず、トランザクションログの最後のフラグメントをバックアップする必要があります（ テールバックアップ ）。 同時に、Enterprise Editionを使用している場合は、NORECOVERYパラメーターを追加できません。このパラメーターは、データベースを「復元」状態にします。これは、このエディションがオンラインページ回復をサポートしているためです。 さらに、Enterprise Editionでは、ログチェーンを中断させないためにトランザクションログのバックアップを定期的に実行している場合、COPY_ONLYバックアップを作成できます。
私はオフライン回復の道をたどり、次のことを行います。

 BACKUP LOG AdventureWorks TO DISK = 'D:\tmp\aw_backups\aw_log_fail3.trn' WITH NORECOVERY 

これで、破損したページを修復できます。 まず、完全バックアップ（aw_full_ok1.bak）を使用します。

 RESTORE DATABASE AdventureWorks PAGE = '1:20455' FROM DISK = 'D:\tmp\aw_backups\aw_full_ok1.bak' WITH NORECOVERY 

その結果、次のことができます。

トランザクションログをロールバックする必要があるため、NORECOVERYオプションを使用する必要があることに注意してください。

 RESTORE LOG AdventureWorks FROM DISK = 'D:\tmp\aw_backups\aw_log_ok1.trn' WITH NORECOVERY 

そして

 RESTORE LOG AdventureWorks FROM DISK = 'D:\tmp\aw_backups\aw_log_fail3.trn' WITH RECOVERY 

すべてがうまくいったようで、DBCC CHECKDBを起動して...

回復に成功しました。
完全なバックアップからデータベース全体を復元するのではなく、破損したページのみを復元するため、ダウンタイムが短縮されることに注意してください（バックアップ全体を復元した場合、バックアップは8.5秒で回復しますが、データベースが大きいほど、時間差があります）。 オンラインリカバリを使用したSQL Server Enterprise Editionのラッキーなものは、ログバックアップからのリカバリ時間を節約し、オフラインリカバリも悲しいことに、ログは完全に処理されます。
また、SQL Server 2005、2008、2008 R2では、T-SQLを使用した場合にのみ単一ページの復元が可能であることを追加する価値があります; DenaliはGUIを介してこれを実行できるようになりました。

しかし、DBCC CHECKDBの場合はどうでしょうか？

念のため、REPAIR_ALLOW_DATA_LOSSパラメーターを指定してDBCC CHECKDBを実行した場合にSQL Serverが何を実行するかを確認することにしました。 すべて同じエラー：

まず、データベースをSINGLE_USERモードにします。

 ALTER DATABASE AdventureWorks SET SINGLE_USER 

次に、リカバリを開始します。

 DBCC CHECKDB('AdventureWorks', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY 

要約すると：
Repair: The page (1:20455) has been deallocated from object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data).
ええ、SQL Serverは「破損」ページを削除しました。 誰でもアクセスできるようにデータベースをMULTI_USERモードにし、不足しているものを確認します。

SQL Serverのページサイズが8KBであり、ユーザーデータについては使用できるデータが少し少ない場合、すべてが自然であるため、テーブルは7レコードずつ「減量」されます（最初は99999でした）。 このテーブルにはクラスター化インデックスがなかったため、データはランダムな順序で保存できます。 どのデータが失われるのかを知ることさえできませんでした。

なぜ、翻訳ではないのでしょうか？

なぜそれがまだ翻訳ではなく、「動機に基づく」投稿なのでしょうか。 事実、パブリックドメインには、Gail Shawによる記事「ページの復元」はありません。 SQL Server MVPの書籍Deep Dives vol.2にはそのようなセクションがあり、かなり具体的なお金で販売されています（ただし、当然、インターネットで簡単に見つかります）。翻訳を公開するのが正しいかどうかはわかりません。
一般に、私はこの記事を読み、主要な点に注意し、それから自分でテキストを書き、その過程で修復の実験を行いました。 この経験が誰かに役立つことを願っています。
そして、紳士、この実験を繰り返すことを決定した場合、あなたが非常に慎重になることを心から願っています（例えば、本番サーバーのメインデータベースで実験しないこと）。 私はあなたの行動に一切の責任を負いません。