第7回では「データベースの復旧モデル」についてまとめてみました。今回は SQL Serverの バックアップ／リストア について、自分の整理も兼ねて、まとめてみようと思います。
バックアップの種類について
バックアップの種類として、以下のようなものがあります。
- 完全バックアップ
- 差分バックアップ
- トランザクションログ バックアップ
- ファイル バックアップ
- 部分バックアップ
各バックアップの特徴についてまとめてみようと思います。
完全バックアップ
データベース全体 (データベース物理ファイル (.mdf\.ndf)、トランザクションログファイル (.ldf) を含む) のバックアップ。
完全バックアップはオンラインで採取することが可能であり、データの整合性は、完全バックアップの処理が完了した時点で保証されます。
例えば、バックアップの開始時間が 10:00 am、バックアップの完了時間が 10:00 pm の場合、バックアップの採取期間に実行されたトランザクション情報も完全バックアップに反映され、バックアップの完了時間である 10:00 pm 時点のデータの整合性が保証されるようになります。
差分バックアップ
最新の完全バックアップ時点から変更されたデータの差分のみのバックアップ。
バックアップは、変更されたデータページを含むエクステント単位で行われます。完全バックアップを採取以降、データベースを構成するほとんどのデータページの変更が行わている場合、バックアップ ファイルサイズは、完全バックアップと同等のサイズになります。
差分バックアップを複数回採取した場合でも、最も最新の状態にデータベースをリストアするために使用するバックアップは、最新の完全バックアップ ＋ 最新の差分バックアップ のみになります。
トランザクションログ バックアップ
トランザクション ログ上に書き込まれた ログ レコード のバックアップ。
トランザクションログ バックアップはオンラインで採取することが可能であり、データの整合性は、完全バックアップとは異なり、バックアップを開始した時点で保証されます。
[トランザクション ログ内のログ レコード 例]
ログ レコードには、DML操作 (INSERT, UPDATE, DELETE)、DDL操作、チェックポイント、コミットなどの情報が保持されます。
ファイル バックアップ
複数のファイル グループにデータベース物理ファイルを分割して配置している場合、 データベースファイル、ファイル グループ単位でバックアップを実施することが可能であり、データベース物理ファイルが破損した場合、破損したファイルのみを復元することが出来ます。
大規模データベースの場合、完全バックアップに多大な時間を要したり、データベースファイルが破損した場合、完全バックアップからデータベース全体のリストアを実施すると、復旧までに多大な時間を要する可能性があります。
ファイル バックアップでは、特定のデータベースファイル、ファイルグループ毎にバックアップを実施することで、バックアップに要する時間を短縮したり、破損したファイルのみを復元することで、復旧までの時間を短縮することが可能になります。
しかしながら、バックアップ計画、リストア手順が複雑になるため、大規模データベース以外は、完全バックアップ、差分バックアップ、トランザクション ログ バックアップを組み合わせたバックアップ計画を立てたほうが運用は楽になるかと思います。
部分バックアップ
完全バックアップは、すべてのファイルグループ配下のすべてのデータベース物理ファイルがバックアップされますが、部分バックアップでは、読み取り専用のファイル グループをバックアップ対象から除外するなど、一部のファイルグループを含まれないバックアップ。
ファイルバックアップと同様に、バックアップ計画、リストア手順が複雑になるため、大規模データベース以外は恐らく選択することのないバックアップ方式になるかと思いいます。
データベースのリストア (例)
データベースの復旧モデルが「完全 (Full)」 のデータベースに対して、以下のようにバックアップを採取している状態で、該当データベースが破損したが、ログ末尾のバックアップを採取できたという前提において、障害直前の状態にデータベースを復旧する必要がある場合に、リストアに利用するデータベースのバックアップのパターンについて確認してみたいと思います。
パターン１
完全バックアップ (t1) + 差分バックアップ (t5) + トランザクション ログ バックアップ (t7) + ログ末尾のバックアップ (t8)
※ 差分バックアップは、完全バックアップ時点から変更されたデータの差分のみが含まれており、完全バックアップ時点から最も最新の差分バックアップがあればデータベースを最新の状態に復元することが可能となるため、差分バックアップ (t3) は使用しません。
パターン２
完全バックアップ (t6) + トランザクション ログ バックアップ (t7) + ログ末尾のバックアップ (t8)
※ 今回の場合、最もリストアに必要なバックアップファイルが少なく、復旧までの時間が短くなることが期待できるパターンかと思います。
パターン 3
完全バックアップ (t1) + トランザクション ログ バックアップ (t2) + トランザクション ログ バックアップ (t4) + トランザクション ログ バックアップ (t7) + ログ末尾のバックアップ (t8)
※ 仮に、差分バックアップ (t3)、差分バックアップ (t5)、完全バックアップ (t6) が正しくバックアップされておらず、使用できない場合においても、完全バックアップ (t1) 以降のトランザクション ログのバックアップで、ログ チェーンを維持することが出来る場合、最新の状態まで復元することが可能となります。
パターン 4
完全バックアップ (t1) + 差分バックアップ (t3) + トランザクション ログ バックアップ (t4) + トランザクション ログ バックアップ (t7) + ログ末尾のバックアップ (t8)
※ 仮に、差分バックアップ (t5)、完全バックアップ (t6) が正しくバックアップされておらず、使用できない場合においても、完全バックアップ (t1) + 差分バックアップ (t3) 以降のトランザクション ログのバックアップで、ログ チェーンを維持することが出来る場合、最新の状態まで復元することが可能となります。
まとめ
今回は、SQL Serverの バックアップ／リストア についてまとめてみました。Azure上のPaaS データベース (Azure SQL Database/Azure SQL Database Managed Instance など) の場合、自動的にバックアップが採取されますが、オンプレミス上、Azure 仮想マシン上で SQL Server を運用する場合、バックアップ計画をしっかりと立てる必要があるかと思います。
ファイル バックアップ、部分バックアップからのデータベースのリストアは、手順が複雑になるため、 特に要件がない場合は、完全バックアップ、差分バックアップ、トランザクション ログのバックアップを組み合わせたバックアップ／リストア計画を立てると良いかと思います。
※ Azure 上の場合は、Azure Blob ストレージを使用したスナップショットなどの使用を検討することも良いかと思います。
【第9回】基本から始める SQL Server【チェックポイント】へ
