SQL Server：復旧モデルとバックアップ基本ガイド

1. この記事の範囲

SQL Serverでは、データベースに格納された重要なデータを保護するためのバックアップおよび復元機能が提供されています。これらの機能は、システム障害、自然災害、またはユーザーエラーなどのトラブル時にデータを安全に復旧するための基本的なセーフガードを提供します。この記事では、バックアップ、リストア、リカバリの基本概念に加え、SQL Serverを使用した戦略的な運用方法について解説します。

2. 内容

2-1. バックアップ: データ保護の第一歩

2-1-1. バックアップの目的

バックアップは、データを安全に保存し、障害時に復旧可能な状態にするプロセスです。SQL Serverのバックアップは単なるデータコピーではなく、データベースの整合性を保ちながら保存する点に特徴があります。

2-1-2. バックアップの種類

SQL Serverでは、運用要件に応じて複数のバックアップ方式を選択できます。

• 完全バックアップ
  データベース全体を保存する基本のバックアップ。完全復元の基盤となるもので、すべてのデータとシステム情報が含まれます。

• 差分バックアップ
  直近の完全バックアップ以降に変更されたデータのみを保存します。バックアップ時間が短縮されるため、効率的な運用が可能です。

• トランザクションログバックアップ
  トランザクションログの内容を保存し、障害発生時に特定の時点までデータを復元するために使用されます。

• コピーオンリーバックアップ
  既存のバックアップチェーンに影響を与えずにバックアップを取得します。

2-1-3. バックアップの保存と運用

• バックアップデバイスと保存場所
  SQL Serverのバックアップは、ローカルディスク、ネットワークストレージ、または Azure Blob Storageに保存可能です。Azure Blob Storageは、自然災害に備えて異なるリージョンにバックアップを保管するオプションを提供します。

• スケジュール設計
  業務時間外やオフピーク時に完全バックアップをスケジュールし、日中の業務への影響を最小限に抑えます。差分バックアップやトランザクションログバックアップを短い間隔で実行することで、障害発生時のデータ損失を最小限にします。

• 推奨事項
  バックアップデータは暗号化と圧縮を行い、適切な命名規則（例: .BAK や .TRN 拡張子）を使用します。また、定期的にバックアップのテスト復元を実施し、データの整合性を確認します。

2-2. リストア: データベースの復旧プロセス

リストアとは、バックアップデータを使用してデータベースを以前の状態に戻すプロセスです。

2-2-1. リストアのステップ

リストアは、データベースの状態を正確に復元するために、適切なバックアップデータと操作手順を必要とします。

1. 完全バックアップの適用
   最初に完全バックアップを復元します。

2. 差分バックアップの適用
   差分バックアップを使用して、完全バックアップ以降の変更を反映します。変更点を反映し、データベースを最新状態に近づけます。

3. トランザクションログの適用
   ポイントインタイムリカバリにより、特定の時点までデータを復元します。

2-2-2. リストアの選択肢

• WITH RECOVERY
  リストアを完了し、データベースをオンライン化します。

• WITH NORECOVERY
  リストア操作を継続可能な状態にします。差分やログバックアップを追加で適用する際に使用します。

2-3. リカバリ: データの一貫性と可用性の確保

リカバリは、リストア後のデータベースをオンライン状態に戻し、通常の操作を可能にするプロセスです。リカバリは、データの一貫性を確保し、ユーザーのアクセスを再開するための最終ステップです。

2-3-1. リカバリの主な手法

SQL Serverでは、トランザクションログを利用したリカバリを行い、障害やクラッシュ直前の状態を再現します。これには、以下の手順が含まれます。

1. ログのロールフォワード
   トランザクションログの適用により、最後のバックアップ以降の変更を反映します。

2. ポイントインタイムリカバリ
   指定した時点の状態にデータベースを復元します。

2-4. バックアップ戦略の設計

2-4-1. 復旧モデルの選択

SQL Serverの復旧モデルは、トランザクションログの管理方法を制御し、データベースの復旧シナリオや運用要件を左右します。以下の3つの復旧モデルがあり、それぞれの特徴と運用上の利点・制約を理解して適切に選択することが重要です。

• 単純復旧モデル
  トランザクションログの管理を最小限に抑えた復旧モデルです。このモデルではトランザクションログの自動切り捨てが行われるため、ログサイズを抑えられるという利点があります。一方で、障害発生時にポイントインタイム復元ができないという制約があり、データ損失のリスクが高まります。主に、データ損失が許容できる環境や非重要なデータベースで使用されます。

• 完全復旧モデル
  完全復旧モデルはすべてのトランザクションログを保持し、障害発生時に任意の時点に復元できる柔軟性を提供します。このモデルはトランザクションログのバックアップを頻繁に取得する必要があり、管理の負担が増加しますが、データ損失を最小限に抑えたい場合には最適です。企業の基幹業務システムや重要なデータを管理するデータベースでは、完全復旧モデルが一般的に採用されます。

• 一括ログ復旧モデル
  大量のデータを一括で処理する際に効果を発揮します。このモデルは、データ移行やETL処理のような大規模な操作でログサイズを抑えつつ、復旧可能な状態を維持します。一括操作後にトランザクションログのバックアップを取得し、完全復旧モデルに切り替えることで、通常の運用を再開できます。ただし、一括操作後のログバックアップが取れないと、その期間のデータが失われる可能性があり、利用シナリオを慎重に検討する必要があります。

復旧モデルを切り替える際は、その変更後に完全バックアップを取得することが推奨されます。たとえば、完全復旧モデルから単純復旧モデルへの切り替えは、データ損失リスクを軽減するバックアップ戦略を見直す良いタイミングでもあります。以下は復旧モデルの変更例です。

ALTER DATABASE [DatabaseName]
SET RECOVERY FULL;

復旧モデルの選択は、データ損失許容度、運用負荷、システムの性質を総合的に考慮して行う必要があります。データベース管理者は、適切なモデルを選択し、運用に適したバックアップ計画を設計することで、システムの可用性とデータの保全性を最大化できます。

2-4-2. スケジュールの設計

1. 業務時間外に完全バックアップを取得
   定期的に完全バックアップを実施します。

2. 差分バックアップとログバックアップの組み合わせ
   差分バックアップで復元時間を短縮し、ログバックアップでデータの一貫性を保ちます。

バックアップスケジュールは、業務の稼働状況やデータの更新頻度に応じて適切に設計する必要があります。適切なスケジュールは、障害発生時の復旧速度を最大化し、運用中のシステム負荷を最小限に抑える鍵となります。

まず、業務時間外に完全バックアップを取得することが推奨されます。完全バックアップは、データベース全体をスナップショットとして保存するプロセスで、データベースサイズに比例して時間がかかります。業務稼働中に実行するとパフォーマンスに影響を与える可能性があるため、一般的にはオフピーク時（深夜や週末）にスケジュールを設定します。SQL Serverでは Maintenance Planを使用して、完全バックアップの定期的な実行を自動化できます。

一方、完全バックアップだけではデータの復旧速度が遅くなる可能性があるため、差分バックアップとログバックアップを組み合わせて運用することが一般的です。差分バックアップは、直近の完全バックアップ以降に変更されたデータのみを保存するため、バックアップサイズを小さく抑えつつ、復元プロセスを効率化します。差分バックアップの頻度はデータ更新の頻度に基づいて設定しますが、1日1回から数時間ごとの頻度が一般的です。

ログバックアップは、トランザクションログの内容を保存し、データの一貫性を保つ役割を果たします。トランザクションログバックアップを定期的に取得することで、障害発生時にはポイントインタイム復元を実現できます。このバックアップの頻度は、許容可能なデータ損失量（Recovery Point Objective, RPO）によって決まります。たとえば、RPOが15分である場合、ログバックアップを15分間隔でスケジュールする必要があります。

以下に例として、完全バックアップ、差分バックアップ、およびログバックアップを組み合わせたスケジュールを示します。

• 完全バックアップ: 毎週日曜日の深夜 2:00
• 差分バックアップ: 平日毎日深夜 2:00
• ログバックアップ: 15分ごと（24時間実行）

これらのスケジュールを自動化するには、SQL Server Agentジョブを設定し、スクリプトを使用して計画を実行します。以下に、差分バックアップとログバックアップをそれぞれ設定するT-SQLスクリプトの例を示します。

差分バックアップ例:

BACKUP DATABASE [DatabaseName]
TO DISK = 'C:\Backups\DatabaseName_diff.bak'
WITH DIFFERENTIAL;

ログバックアップ例:

BACKUP LOG [DatabaseName]
TO DISK = 'C:\Backups\DatabaseName_log.trn';

3. まとめ

SQL Serverのバックアップ、リストア、リカバリは、データベース運用の基盤を支える重要なプロセスです。完全バックアップ、差分バックアップ、ログバックアップを組み合わせることで、効率的なデータ保護と迅速な復旧を実現します。また、バックアップデータを暗号化し、安全なストレージに保存することで、セキュリティを強化できます。

さらに、バックアップ戦略の有効性を確認するために、定期的なテスト復元を行い、業務要件に応じて計画を調整してください。オンプレミスとクラウドの両方を活用した多層バックアップを採用することで、自然災害や重大な障害時にもデータの完全性を維持し、迅速な業務再開を可能にします。

参考資料

• SQL Server Backup and Restore Overview
• SQL Server Recovery Models