SQL Server データベースのバックアップと復元

  • [アーティクル]

適用対象: SQL Server

この記事では、SQL Server データベースをバックアップする利点、バックアップと復元に関する基本的な用語、SQL Server のバックアップと復元の方法を紹介します。SQL Server のバックアップと復元のセキュリティに関する考慮事項についても取り上げます。

この記事では SQL Server のバックアップについて説明します。 SQL Server データベースをバックアップする具体的な手順については、「バックアップの作成」を参照してください。

SQL Server のバックアップと復元コンポーネントは、SQL Server データベースに格納されている重要なデータを保護するために不可欠なセーフガードを提供します。 致命的なデータ損失のリスクを最小限に抑えるには、データベースを定期的にバックアップして、データの変更を保持する必要があります。 十分に計画されたバックアップおよび復元戦略は、さまざまな障害が原因で発生するデータ損失からデータベースを保護します。 一連のバックアップの復元とデータベースの回復を実行することでご自分の戦略をテストして、災害に効率的に対応するための準備を整えてください。

バックアップを格納するローカル ストレージに加えて、SQL Server では、バックアップおよび Azure Blob Storage からの復元がサポートされます。 詳しくは、「Microsoft Azure Blob Storage を使用した SQL Server のバックアップと復元」をご覧ください。 Azure Blob Storage を使用して格納したデータベース ファイルの場合、SQL Server 2016 (13.x) では、Azure スナップショットを使用してほぼ瞬時にバックアップし、迅速に復元するためのオプションが用意されています。 詳細については、「 Azure でのデータベース ファイルのファイル スナップショット バックアップ」を参照してください。 Azure では、Azure VM で実行されている SQL Server 向けのエンタープライズ クラスのバックアップ ソリューションも提供されています。 フル マネージドの バックアップ ソリューションで、Always On 可用性グループ、長期保有、特定の時点に復旧、一元的な管理と監視がサポートされています。 詳細については、Azure VM での SQL Server 用の Azure Backup に関する記事をご覧ください。

バックアップする理由

  • SQL Server データベースをバックアップしたり、既存のバックアップの復元テストを実行したりできるほか、離れた安全な場所にバックアップのコピーを保管することによって、致命的な損失からデータを保護することができます。 バックアップは、データを保護できる唯一の方法です。

    データベースの有効なバックアップがあれば、次に示したようなさまざまな障害からデータを復旧することができます。

    • メディアの障害
    • ユーザー エラー (テーブルの誤削除など)
    • ハードウェア障害 (ディスク ドライブの損傷や、復旧の可能性のないサーバー障害など)
    • 自然災害。 Azure Blob Storage への SQL Server バックアップを使用すると、オンプレミスの場所に影響する自然災害が発生した場合に使用できるように、オンプレミスの場所とは異なるリージョンにオフサイト バックアップを作成できます。

  • また、データベースのバックアップは、サーバー間でのデータベースのコピー、Always On 可用性グループやデータベース ミラーリングの設定、およびアーカイブなど、日常的な管理作業を行ううえでも便利です。

バックアップの用語集

バックアップ (back up) (動詞)
SQL Server データベースのデータ レコードまたはそのトランザクション ログのログ レコードをコピーすることによって、バックアップ [名詞] を作成するプロセス。

バックアップ (backup) (名詞)
障害の発生後、データの復元と復旧に使用できる データのコピー。 データベースのバックアップを使用して、コピー (データベース) を新しい場所に復元することもできます。

バックアップ デバイス (backup device)
SQL Server のバックアップの書き込みと復元に使用されるディスクまたはテープ デバイス。 SQL Server のバックアップは、Azure Blob Storage に書き込むこともできます。バックアップ先とバックアップ ファイルの名前を指定するには URL 形式を使用します。 詳しくは、「Microsoft Azure Blob Storage を使用した SQL Server のバックアップと復元」をご覧ください。

バックアップ メディア (backup media)
バックアップの書き込み先となる 1 つまたは複数のテープまたはディスク ファイル。

データ バックアップ (data backup)
データのバックアップ。データベース全体 (データベース バックアップ)、データベースの一部 (部分バックアップ)、または一連のデータ ファイルやファイルグループ (ファイル バックアップ) の形式で存在します。

データベース バックアップ (database backup)
データベースのバックアップ。 データベースの完全バックアップは、バックアップが完了した時点のデータベース全体を表します。 差分データベース バックアップには、最新の完全バックアップ以降に行われたデータベースへの変更のみが含まれます。

差分バックアップ (differential backup)
データベース全体、データベースの一部、または一連のデータ ファイル (またはファイル グループ) の最新の完全バックアップ (差分ベース) をベースとし、その差分ベース以後に変更されたデータのみを含んだデータ バックアップ。

完全バックアップ (full backup)
特定のデータベース (または一連のファイルやファイル グループ) 内のデータがすべて含まれ、さらに、データを復旧するために必要なログも含んだデータ バックアップ。

ログ バックアップ (log backup)
前回のログ バックアップでバックアップされなかったすべてのログ レコードを含むトランザクション ログのバックアップ (完全復旧モデル)。

recover
安定し一貫した状態にデータベースを戻すこと。

復旧 (recovery)
データベースをトランザクションの一貫性が保たれた状態にする、データベース起動時または RESTORE WITH RECOVERY 時のフェーズ。

復旧モデル (recovery model)
データベースのトランザクション ログのメンテナンスを制御するデータベース プロパティ。 復旧モデルの種類は、単純、完全、一括ログの 3 種類です。 データベースのバックアップと復元の要件は、その復旧モデルによって決まります。

復元 (restore)
データを直近の状態まで戻す複数フェーズから成る処理。指定された SQL Server バックアップからすべてのデータおよびログ ページを指定されたデータベースにコピーするフェーズと、バックアップにログとして記録されているすべてのトランザクションをロールフォワード (ログに記録されている変更を適用) するフェーズとで構成されます。

バックアップと復元の方法

データのバックアップと復元は、特定の環境向けにカスタマイズし、使用可能なリソースと連動させる必要があります。 そのため、バックアップの使用や復旧に向けた復元を確実に行うには、バックアップと復元のストラテジが必要です。バックアップと復元のストラテジが適切にデザインされていれば、バックアップの維持と格納のコストを考慮しながら、データの可用性を最大化し、かつデータ損失を最小化するためのビジネス要件をバランスよく保つことができます。

バックアップと復元のストラテジには、バックアップに関する部分と復元に関する部分があります。 ストラテジで扱うバックアップ部分では、バックアップの種類と頻度、バックアップに必要なハードウェアの性質と速度、バックアップのテスト方法、およびバックアップ メディアの保管場所と保管方法 (セキュリティ上の考慮事項も含む) を定義します。 ストラテジで扱う復元部分では、復元の実行責任者、データベースの可用性やデータ損失の最小化という目標を達成するための復元の実行方法、および復元のテスト方法を定義します。

バックアップと復元について効果的なストラテジをデザインするには、慎重に計画、実装、およびテストする必要があります。 テストは必要です。復元ストラテジに含まれるすべての組み合わせでバックアップを正常に復元し、復元されたデータベースの物理的な一貫性をテストして初めて、バックアップ ストラテジが完成するからです。 さまざまな要因を検討する必要があります。 これには以下が含まれます。

  • 運用データベースに関する組織目標。特に、可用性、およびデータの損失または破損からの保護に関する要件。

  • 各データベースの性質。サイズ、使用パターン、内容の性質、保持しているデータの要件など。

  • リソースについての制約。ハードウェア、スタッフ、バックアップ メディアを保管する場所、保管されたメディアの物理的なセキュリティなど。

ベスト プラクティスの推奨事項

バックアップ操作または復元操作を実行するアカウントには、必要以上の権限を付与してはいけません。 特定のアクセス許可の詳細については、バックアップと復元を確認してください。 バックアップは暗号化し、可能であれば圧縮することをおすすめします。

セキュリティ確保のため、バックアップ ファイルには適切な規則に従う拡張子が必要です。

  • データベースのバックアップ ファイルには .BAK の拡張子が必要です。
  • ログのバックアップ ファイルには .TRN の拡張子が必要です。

別のストレージを使用する

重要

データベースのバックアップは、必ずデータベース ファイルとは物理的に別の場所または別のデバイスに置いてください。 データベースを格納する物理ドライブが誤作動またはクラッシュした場合、復旧できるかできないかは、復元を実行するためにバックアップを格納した別のドライブまたはリモート デバイスにアクセスできるかできないかで決まります。 同じ物理ディスク ドライブから論理ボリュームまたはパーティションを複数作成できることを覚えておきましょう。 バックアップの保存場所を選ぶ前に、ディスク パーティションと論理ボリュームのレイアウトについて慎重に検討してください。

適切な復旧モデルを選択する

バックアップ操作および復元操作は、復旧モデルのコンテキストで発生します。 復旧モデルは、トランザクション ログの管理方法を制御するデータベース プロパティです。 そのため、データベースの復旧モデルでは、そのデータベースでサポートされるバックアップの種類および復元シナリオ、およびトランザクション ログのバックアップのサイズが判断されます。 通常、データベースでは、単純復旧モデルまたは完全復旧モデルが使用されます。 完全復旧モデルは、一括操作を行う前に一括ログ復旧モデルに切り替えることで拡張できます。 これらの復旧モデルの概要とトランザクション ログの管理への影響については、「 トランザクション ログ (SQL Server)」を参照してください。

データベースに対する復旧モデルの最善の選択は、ビジネス要件によって異なります。 トランザクション ログの管理を不要にし、バックアップと復元を簡単にするには、単純復旧モデルを使用します。 作業損失の可能性を最小に抑えるには、管理のオーバーヘッドが発生するという犠牲を払っても、完全復旧モデルを使用します。 一括ログ記録操作中にログ サイズへの影響を最小限に抑えるのと同時にこれらの操作の復旧を可能にするには、一括ログ復旧モデルを使用します。 バックアップおよび復元に対する復旧モデルの影響については、「バックアップの概要 (SQL Server)」を参照してください。

バックアップ戦略を設計する

特定のデータベースに対するビジネス要件を満たす復旧モデルを選択した後、対応するバックアップ ストラテジを計画して実装する必要があります。 最適なバックアップ ストラテジはさまざまな要因に依存しますが、その中でも以下の要因が特に重要です。

  • アプリケーションがデータベースにアクセスする必要があるのは 1 日に何時間か。

    オフピーク時間が予測できる場合は、その時間にデータベースの完全バックアップをスケジュールすることをお勧めします。

  • 変更や更新はどの程度の頻度で行われるか。

    変更が頻繁に行われる場合は、次のことを考慮してください。

    • 単純復旧モデルでは、データベースの完全バックアップの合間に差分バックアップをスケジュールすることを検討します。 差分バックアップは、データベースの最後の完全バックアップ以降の変更だけをキャプチャします。

    • 完全復旧モデルでは、ログ バックアップを頻繁に行うようスケジュールする必要があります。 完全バックアップの合間に差分バックアップを行うようにスケジュールすると、データを復元した後で復元する必要のあるログ バックアップの数が減るので、復元時間を短縮することができます。

  • 変更は、データベースの一部分でのみ行われるか、データベースの大部分で行われるか。

    ファイルまたはファイル グループの一部分に変更が集中する大規模なデータベースでは、部分バックアップまたはフル ファイル バックアップが有効です。 詳細については、「部分バックアップ (SQL Server)」と「完全ファイル バックアップ (SQL Server)」を参照してください。

  • データベースの完全バックアップにはどの程度のディスク領域が必要か。

  • これまで、どの程度ビジネスでバックアップを維持する必要があったか。

    アプリケーションやビジネス要件のニーズに応じて、適切なバックアップ スケジュールが設定されていることを確認します。 障害の発生時点まですべてのデータを再生成する方法がない限り、バックアップが古くなるにつれてデータ損失のリスクが高くなります。 ストレージ リソースの制限を理由に古いバックアップを破棄することを選ぶ前に、これまでそれほどまでに復旧が必要だったかどうかを考えてみてください。

データベースの完全バックアップのサイズの推計

バックアップと復元のストラテジを実装する前に、データベースの完全バックアップで使用するディスク領域を推計する必要があります。 バックアップ操作では、データベース内のデータをバックアップ ファイルにコピーします。 バックアップにはデータベース内の実際のデータだけが入っており、未使用の領域は入っていません。 そのため、通常、バックアップはデータベースそのものよりも小さくなります。 データベースの完全バックアップのサイズは、 sp_spaceused システム ストアド プロシージャを使用して推計することができます。 詳細については、「sp_spaceused(Transact-SQL)」を参照してください。

バックアップのスケジュール

バックアップの実行によって、実行中のトランザクションが受ける影響はわずかです。したがってバックアップは、通常の運用時に実行できます。 実稼働ワークロードへの影響は最小限にとどめて SQL Server バックアップを実行できます。

バックアップ中のコンカレンシーの制限については、「バックアップの概要 (SQL Server)」を参照してください。

必要なバックアップの種類、および各種類のバックアップを実行する必要のある頻度を決定した後、データベースに対するデータベース メンテナンス プランの一部として、定期的なバックアップをスケジュールすることをお勧めします。 メンテナンス プランと、データベース バックアップおよびログ バックアップ用のメンテナンス プランの作成方法については、「 Use the Maintenance Plan Wizard」を参照してください。

バックアップのテスト

バックアップをテストするまでは、復元ストラテジが完成したことにはなりません。 データベースのコピーをテスト システムに復元することで、各データベースに対するバックアップ ストラテジを十分にテストすることが重要です。 使用するすべての種類のバックアップの復元をテストする必要があります。 また、バックアップを復元したら、データベースの DBCC CHECKDB を使用してデータベースの一貫性チェックを実行し、バックアップ メディアが破損していないことを検証することもお勧めします。

メディアの安定性と一貫性を確認する

バックアップ ユーティリティ (BACKUP T-SQL コマンド、SQL Server メンテナンス プラン、お使いのバックアップ ソフトウェアまたはソリューションなど) から提供される認証オプションを使用します。 例については、「[RESTORE VERIFYONLY]」 (../t-sql/statements/restore-statements-verifyonly-transact-sql.md) を参照してください。BACKUP CHECKSUM などの高度な機能を使用して、バックアップ メディア自体の問題を検出します。 詳細については、「バックアップ中および復元中に発生する可能性があるメディア エラー (SQL Server)」を参照してください。

ドキュメントのバックアップと復元ストラテジ

バックアップと復元の手順をドキュメント化し、そのドキュメントを運用手順書に含めて保管することをお勧めします。 また、データベースごとに操作マニュアルを用意しておくことをお勧めします。 この操作マニュアルには、バックアップの場所、バックアップ デバイス名 (ある場合)、およびテスト バックアップの復元に必要な時間を記載しておきます。

XEvent を使用して進行状況を監視する

バックアップと復元の操作は、関連するデータベースのサイズと処理の複雑さによって、相当の時間がかかる場合があります。 いずれの処理で問題が発生した場合は、backup_restore_progress_trace 拡張イベントを使用して進捗状況をライブで監視できます。 拡張イベントの詳細については、「 拡張イベント」を参照してください。

警告

backup_restore_progress_trace 拡張イベントを使用すると、パフォーマンスの問題が発生し、大量のディスク領域を消費する場合があります。 使用するのは短時間にし、慎重に実行し、実稼働環境で実装する前には徹底的なテストを行ってください。

-- Create the backup_restore_progress_trace extended event esssion
CREATE EVENT SESSION [BackupRestoreTrace] ON SERVER 
ADD EVENT sqlserver.backup_restore_progress_trace
ADD TARGET package0.event_file(SET filename=N'BackupRestoreTrace')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

-- Start the event session  
ALTER EVENT SESSION [BackupRestoreTrace]  
ON SERVER  
STATE = start;  
GO  

-- Stop the event session  
ALTER EVENT SESSION [BackupRestoreTrace]  
ON SERVER  
STATE = stop;  
GO

拡張イベントからの出力例

xevent 出力のバックアップの例xevent 出力の復元の例

バックアップ タスクの詳細

バックアップ デバイスとバックアップ メディアの操作

バックアップの作成

Note

部分バックアップまたはコピーのみのバックアップでは、Transact-SQL の BACKUP ステートメントにそれぞれ PARTIAL オプションまたは COPY_ONLY オプションを使う必要があります。

SSMS の使用

T-SQL の使用

データのバックアップを復元する

SSMS の使用

T-SQL の使用

トランザクション ログを復元する (完全復旧モデル)

SSMS の使用

T-SQL の使用

詳細情報とリソース

Backup Overview (SQL Server)
復元と復旧の概要 (SQL Server)
BACKUP (Transact-SQL)
RESTORE (Transact-SQL)
Analysis Services データベースのバックアップと復元
フルテキスト カタログとフルテキスト インデックスのバックアップおよび復元
レプリケートされたデータベースのバックアップと復元
トランザクション ログ (SQL Server)
復旧モデル (SQL Server)
メディア セット、メディア ファミリ、およびバックアップ セット (SQL Server)