SQL Serverのバックアップとリカバリ：完全バックアップ、差分バックアップ、トランザクションログバックアップを徹底解説

データベース管理者にとって、バックアップとリカバリは避けて通れない重要な課題です。
SQL Serverでは、さまざまなバックアップ方法を組み合わせることで、データを効率的に保護できます。

本記事では、完全バックアップ、差分バックアップ、トランザクションログバックアップの仕組みと用途について詳しく解説します。さらに、実践的な例を交えながら、それぞれの利点や適切な使用方法を紹介します。

---

SQL Serverのバックアップの基本

SQL Serverのバックアップは、データの保護と障害からの復旧を可能にする重要なプロセスです。以下は、バックアップに関する基本事項です。

次に、これら3つのバックアップタイプについて具体的に見ていきましょう。

---

完全バックアップ

完全バックアップは、データベース全体をコピーするバックアップ形式です。データベースの現在の状態を完全に保存するため、バックアップの基盤となる重要な種類です。

メリット

• データベース全体を完全に復元可能
• 単独で復元可能

デメリット

• ファイルサイズが大きくなる
• 取得に時間がかかる

実行例

BACKUP DATABASE TestDB
TO DISK = 'C:\Backups\TestDB_Full.bak'
WITH FORMAT, NAME = 'Full Backup of TestDB';

適用場面

• 初めてのバックアップ
• 定期的なフルバックアップとして（例えば、週に1回）

---

差分バックアップ

差分バックアップは、直近の完全バックアップ以降に変更されたデータだけを保存します。これにより、バックアップ時間やストレージの効率が向上します。

メリット

• フルバックアップよりもバックアップサイズが小さい
• バックアップ時間が短縮される

デメリット

• フルバックアップとセットでないと復元できない

実行例

BACKUP DATABASE TestDB
TO DISK = 'C:\Backups\TestDB_Diff.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup of TestDB';

適用場面

• 日々のバックアップ（例：毎日夜間に差分バックアップを作成）
• データ変更が多い場合の効率的なバックアップ

---

トランザクションログバックアップ

トランザクションログバックアップは、データベースで発生したすべてのトランザクションのログを保存します。このバックアップは、ポイントインタイムリカバリを可能にするため非常に重要です。

メリット

• 障害発生時に直前の状態まで復元可能
• データ損失を最小限に抑えられる

デメリット

• ログが蓄積し続けるため、定期的に削除が必要

実行例

BACKUP LOG TestDB
TO DISK = 'C:\Backups\TestDB_Log.bak'
WITH NAME = 'Transaction Log Backup of TestDB';

適用場面

• 重要なデータベースで頻繁にトランザクションが発生する場合
• ポイントインタイムリカバリが必要な場合

---

バックアップ戦略の設計

バックアップ戦略を設計する際には、次のポイントを考慮する必要があります。

1. RPO（目標復旧時点）とRTO（目標復旧時間）を設定
   • データ損失をどれだけ許容できるか？
   • 復旧にどれくらいの時間がかけられるか？
2. バックアップのスケジュール
   • 例：週に1回の完全バックアップ、毎日の差分バックアップ、30分ごとのトランザクションログバックアップ。
3. バックアップの保存場所
   • ローカルディスク、ネットワークストレージ、クラウドなど。
4. バックアップのテスト
   • 実際にバックアップから復旧できるか定期的に確認。

---

演習問題

問題 1

次のシナリオに基づいて、適切なバックアップ戦略を設計してください。

「会社では、SQL Serverデータベースを使用して日々の顧客データを管理しています。業務時間中（9:00～18:00）は頻繁にトランザクションが発生し、障害が発生しても直近の状態に復元したいと考えています。一方、業務時間外はデータ変更がほとんどありません。」

---

問題 2

次のSQLコードを実行するとどうなりますか？説明してください。

BACKUP DATABASE TestDB
TO DISK = 'C:\Backups\TestDB_Full.bak'
WITH FORMAT, NAME = 'Full Backup of TestDB';

BACKUP DATABASE TestDB
TO DISK = 'C:\Backups\TestDB_Diff.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup of TestDB';

BACKUP LOG TestDB
TO DISK = 'C:\Backups\TestDB_Log.bak'
WITH NAME = 'Transaction Log Backup of TestDB';

---

解答例

問題1の解答

以下の戦略が適切です。

1. 業務時間中（9:00～18:00）
   • 30分ごとのトランザクションログバックアップを実施。
2. 業務終了後（18:00以降）
   • 週に1回の完全バックアップを実施（例：金曜日の夜）。
   • 他の日は差分バックアップを実施（例：月～木曜日）。

問題 2の解答

• 最初のコマンドで完全バックアップが作成されます。
• 2つ目のコマンドで、最初の完全バックアップ以降の変更分のみを保存する差分バックアップが作成されます。
• 3つ目のコマンドで、トランザクションログのバックアップが保存されます。これにより、障害発生時にポイントインタイムリカバリが可能になります。

---

まとめ

SQL Serverのバックアップは、完全バックアップ、差分バックアップ、トランザクションログバックアップを組み合わせて実行することで、効率的かつ柔軟なデータ保護を実現します。

各バックアップの特徴や用途を理解し、自社の要件に合った戦略を設計しましょう。