はじめに
バックアップとリストアは、データを保護するためにとても重要な仕組みです。
SQL Serverのバックアップとリストアについて、自分なりにまとめてみました。
参考にした書籍、サイトは以下です。
平山 理 著『絵で見てわかるSQL Serverの仕組み』
SQL Server データベースのバックアップと復元 - SQL Server | Microsoft Docs
Sql server のバックアップとリストアの基礎 - Masayuki Ozawa
バックアップする理由
データベースは以下のような障害が、起こる可能性があります。
- メディアの障害
- ユーザエラー（操作ミスによるデータの削除など）
- ハードウェア障害（ディスクドライブの損傷や、サーバー障害など）
- 自然災害
データの損失のリスクを最小限に抑えるには、データを定期的にバックアップする必要があります。
データベースをバックアップすることで、これらの障害からデータを守る事ができます。
また、データベースのバックアップはサーバー間でのデータベースのコピーや、アーカイブなどの日常的な管理にも役立ちます。
バックアップに関連する基本用語
いくつか基本的な用語についてまとめました。
| 用語 | 説明 |
|---|---|
| バックアップ（backup） | 障害が発生した際にデータを復元できるデータのコピー。またはバックアップを作成するプロセス。 |
| リストア（restore） | バックアップから、データを書き戻すなどしてデータを復元するプロセス。 |
| 完全バックアップ（full backup） | バックアップの手法の1つ。 前回のバックアップからの追加・更新の有無に関わらず、すべてのデータのバックアップをとること。 |
| 差分バックアップ（differential backup） | バックアップの手法の1つ。 最新の完全バックアップ以後に追加・更新したデータのみをバックアップすること。 |
| 増分バックアップ（incremental backup） | バックアップの手法の1つ。 直前のバックアップ以後に追加・更新したデータのみをバックアップすること。 ※SQL Serverには増分バックアップはありません。 |
| トランザクション（transaction） | 分ける事の出来ない複数の処理のまとまりのこと。 一連の処理をまとめて1つの処理として扱うことで、結果の整合性を保つことができる。 |
| コミット（commit） | トランザクション処理を終了し、確定すること。 |
| ロールバック（rollback） | ある時点の状態に巻き戻すこと。 |
| ロールフォワード（roll forward） | 障害などが起きた際に、ある時点の状態に巻き戻し、その後ログに記録した処理を適用して、障害発生直前の状態に戻すこと。 |
| チェックポイント（checkpoint） | データの更新を、バッファキャッシュ上から物理ディスク上に書き込む処理や、書き込み処理を行った時点のこと。 ※詳しくはチェックポイントの項目に記載 |
SQL Serverのデータベースのバックアップには増分バックアップがなく、完全バックアップと差分バックアップの2種類です。
SQL Serverの内部動作
バックアップの前に、まずSQL Serverの内部動作について説明します。
SQL Serverのバックアップを理解する上で関連するSQL Serverのデータ構造や処理について、いくつかまとめてみました。
データ構造
SQL Serverはデータファイルとトランザクションログファイルという、2種類の物理ファイルで構成されています。
- データファイル … データ本体が格納されています。拡張子は「.mdf」や「.ndf」です。
- トランザクションログファイル … データの更新内容が記録されています。拡張子は「.ldf」です。
ログ先行書き込み
SQL Serverは、データに対して何らかの更新が行われると、データ自体を更新する前に、更新内容の履歴をすべてトランザクションログファイルに書き込みます。
トランザクションログにすべて正しく書き込んだ後、データ自体の更新処理を行います。
この動作を ログ先行書き込み（Write-Ahead Logging / WAL） と言います。
チェックポイント
SQL Serverのデータを更新すると、まずはバッファキャッシュ上のデータが更新されます。これを論理書き込みといい、この時点ではまだ物理ディスク上のデータは更新されていません。
バッファキャッシュ上に更新したデータは、あるタイミングで物理ディスクに書き込まれます。これを物理書き込みといいます。
論理書き込みが行われていて、物理書き込みが行われていないバッファキャッシュ上のデータをダーティページといいます。
このダーティページに対する物理書き込みの手段をチェックポイントといいます。
チェックポイントプロセスによって、データを定期的にスキャンし、バッファキャッシュ上にダーティページが見つかれば、物理ディスクに書き込みます。
チェックポイントは、一定間隔ごと、そしてALTER DATABASEステートメント実行時やSQL Server終了時に実行されます。CHECKPOINTステートメントを直接実行もできます。
電源障害などでSQL Serverが異常終了するとバッファキャッシュ上のデータは損失してしまいます。
もし、論理書き込みは行われていても、物理書き込みが完了していない時点で何らかの障害が発生した場合、トランザクションログを使用することによって直前の状態まで復旧ができます。
- トランザクション1 … チェックポイント前に処理が完了しているため、回復処理は不要。
- トランザクション2 … チェックポイント時に実行中だったが、障害発生前に処理が完了しているため、ログファイルを使ってロールフォワードを実行。
- トランザクション3 … チェックポイント後にトランザクションが開始されているが、障害発生前に処理が完了しているため、トランザクション2と同様ロールフォワードを実行。
- トランザクション4 … チェックポイント後に開始され、障害発生時にコミットされていないため、トランザクションをロールバックすることで回復処理を行う。
- トランザクション5 … チェックポイント後に開始され、障害発生時にコミットされていないため、トランザクション4と同様ロールバックすることで回復処理を行う。
出典：三好 康之 著『情報処理教科書 データベーススペシャリスト 2020年版』 - p.340-341
ログ復旧モデル
前述したとおり、SQL Serverにはデータファイルとトランザクションログファイルという2つの物理ファイルで構成されています。
SQL Serverでは、このトランザクションログファイルにログを記載する方法や、トランザクションログのバックアップを必須にするかどうかなどを制御するプロパティとして、復旧モデルというものがあります。
復旧モデルの種類は、単純、完全、一括ログの3種類です。
| 復旧モデル | 説明 | トランザクションログの扱い | トランザクションログのバックアップ | 作業の損失の可能性 | 指定日時への復旧 |
|---|---|---|---|---|---|
| 単純 | データバックアップ終了時点にのみ復旧できるモデル | トランザクションログに書き込まれたレコードは次回のチェックポイント時に切捨てが行われるので、ログの肥大化現象が発生しにくい。 | 必要なし | 最新のデータバックアップ以後の情報は損失します。 | 任意の時点には復旧できません。データバックアップの終了時点にのみ復旧できます。 |
| 完全 | 任意の時点に復旧できるモデル | トランザクションログに書き込まれたレコードはトランザクションログのバックアップごとに切捨てられます。定期的にトランザクションログのバックアップをしないとログが肥大化しディスク容量を圧迫します。 | 必要 | 基本的に損失はありません。 | 任意の時点に復旧できます。 |
| 一括ログ | 完全復旧モデルを補完するためのモデル | 一括操作（大量のデータをデータベースに挿入）すると完全復旧モデルではトランザクションログが大量に挿入されますが、一括ログ復旧モデルは「最小ログ記録」によりトランザクションログの書込みを最小限にすることができます。 | 必要 | 基本的に損失はありません。 | 任意の時点には復旧できません。データバックアップもしくはログバックアップの終了時点にのみ復旧できます。 |
出典：復旧モデル (SQL Server) - SQL Server | Microsoft Docs
出典：SQL Server の障害復旧 - マイクロソフト系技術情報 Wiki
一括ログ復旧モデルは、完全復旧モデルを補完するためのモデルです。
完全復旧モデルの時に一括操作を行うと、トランザクションログに大量のデータが記録されます。
一括ログ復旧モデルでは、以下のような一括操作のトランザクションログは記録しないため、ログを最小限にできます。
- bcpコマンド
- BULK INSERT
- INSERT SELECT
- CREATE INDEX
- ALTER INDEX REBUILD など
出典：トランザクション ログ (SQL Server) - SQL Server | Microsoft Docs
出典：一括インポートで最小ログ記録を行うための前提条件 - SQL Server | Microsoft Docs
出典：ログ末尾のバックアップ (SQL Server) - SQL Server | Microsoft Docs
バックアップの種類
前述したバックアップの基本用語で、一般的なバックアップの種類について書きましたが、SQL Serverにおけるバックアップの種類については以下があります。
- 完全バックアップ
- 差分バックアップ
- トランザクションログバックアップ
- ファイルバックアップ
- 部分バックアップ
それぞれの特徴について説明します。
| バックアップの種類 | 特徴 |
|---|---|
| 完全バックアップ | データベース全体のバックアップ。物理ファイル（.mdfや.ndf）とトランザクションログファイル（.ldf）がすべて含まれます。 |
| 差分バックアップ | 最新の完全バックアップをベースとし、それ以後に変更された差分のみのバックアップ。 |
| トランザクションログバックアップ | トランザクションログに書き込まれたログのバックアップ。 |
| ファイルバックアップ | 1つ以上のデータベースファイル、またはファイルグループからなるバクアップ。データベースを複数の物理ファイルに分割している場合、データベースファイル、ファイルグループ単位でバックアップ、リストアすることができます。 |
| 部分バックアップ | データベースの一部のファイルグループのみのバックアップ。読み取り専用のファイルグループをバックアップから除外するなど、部分的にバックアップを行うことができます。 |
出典：バックアップの概要 (SQL Server) - SQL Server | Microsoft Docs
SQL Serverのバックアップとリストアの例
では、前段のSQL Serverの内部動作を理解した上で、バックアップとリストアのパターン例を考えてみました。
バックアップ例
復旧モデルが完全復旧モデルのデータベースに対して、以下のようにバックアップを行っているとします。
リストア例
上記のバックアップスケジュールで、障害が発生した場合のリストアのパターンについて確認していきます。
障害発生直前の状態に戻すためには、まず完全バックアップはF2を使用します。
差分バックアップはD4を使用します。差分バックアップは、前回の完全バックアップ時点から変更されたデータの差分が含まれているため、差分バックアップD3は使用しません。
差分バックアップ以後の更新は、障害発生の直前のトランザクションログバックアップT6を使用してロールフォワードします。
障害発生時にコミットされていなかったトランザクションは、ロールバックされ、実行される前の状態に戻ります。
もし、差分バックアップのD4が破損したなどで使用できない場合、完全バックアップF2と、差分バックアップD3、トランザクションログバックアップのT5とT6を使用して復旧も可能です。
出典：【第8回】基本から始める SQL Server【バックアップ／リストア】 - NOBTAの気ままにITブログ
出典：復元と復旧の概要 (SQL Server) - SQL Server | Microsoft Docs
出典：SQL Server のバックアップの基本パターン at SE の雑記
実行例
実際にバックアップとリストアを実行して、動きを確認してみます。
バックアップを実行する
以下の流れでバックアップを実行していきます。
クエリを実行して、完全バックアップ、差分バックアップ、トランザクションログバックアップを実行していきます。
データベースbackup_testに、テーブルSalesOrderDetailが存在する前提のクエリになっています。
出典：BACKUP (Transact-SQL) - SQL Server | Microsoft Docs
--データの更新
SELECT *
INTO [backup_test].[dbo].[A]
FROM [backup_test].[dbo].[SalesOrderDetail]
GO
--完全バックアップ
BACKUP DATABASE [backup_test]
TO DISK = N'D:\MSSQL\Backup\20220528\F1.BAK'
WITH
INIT
,FORMAT
GO
--データの更新
SELECT *
INTO [backup_test].[dbo].[B]
FROM [backup_test].[dbo].[SalesOrderDetail]
GO
--トランザクションログバックアップ
BACKUP LOG [backup_test]
TO DISK = N'D:\MSSQL\Backup\20220528\T1.BAK'
WITH
FORMAT
GO
--データの更新
SELECT *
INTO [backup_test].[dbo].[C]
FROM [backup_test].[dbo].[SalesOrderDetail]
GO
--差分バックアップ
BACKUP DATABASE [backup_test]
TO DISK = N'D:\MSSQL\Backup\20220528\D1.BAK'
WITH
INIT
,FORMAT
,DIFFERENTIAL --差分
GO
--データの更新
SELECT *
INTO [backup_test].[dbo].[D]
FROM [backup_test].[dbo].[SalesOrderDetail]
GO
--トランザクションログバックアップ
BACKUP LOG [backup_test]
TO DISK = N'D:\MSSQL\Backup\20220528\T2.BAK'
WITH
FORMAT
GO
--データの更新
SELECT *
INTO [backup_test].[dbo].[E]
FROM [backup_test].[dbo].[SalesOrderDetail]
GO
--差分バックアップ
BACKUP DATABASE [backup_test]
TO DISK = N'D:\MSSQL\Backup\20220528\D2.BAK'
WITH
INIT
,FORMAT
,DIFFERENTIAL --差分
GO
--データの更新
SELECT *
INTO [backup_test].[dbo].[F]
FROM [backup_test].[dbo].[SalesOrderDetail]
GO
--トランザクションログバックアップ
BACKUP LOG [backup_test]
TO DISK = N'D:\MSSQL\Backup\20220528\T3.BAK'
WITH
FORMAT
GO
--データの更新
SELECT *
INTO [backup_test].[dbo].[G]
FROM [backup_test].[dbo].[SalesOrderDetail]
GO
クエリ実行後のDBの状態です。
それぞれのバックアップファイルが作成されました。
リストアを実行する
では次に、バックアップファイルを使ってリストアしていきます。
SQL Server Management Studioを使用した操作
SQL Server Management Studioを
使用すれば、視覚的にどの時点に復元するか選択できるので、手順を紹介します。
-
バックアップを実行したDB名上で右クリック→タスク→復元→データベースをクリックします。
-
タイムラインをクリックします。
-
復元したい日時を選択し、OKをクリックします。
-
OKをクリックすれば、リストアが開始されます。必要に応じて、リストア先のDB名を変更したり、既存のDBに上書きするオプションを選択してください。
クエリを使用した操作
次に、クエリを実行して、完全バックアップ、差分バックアップ、トランザクションログバックアップからそれぞれリストアしていきます。
出典：RESTORE (Transact-SQL) - SQL Server | Microsoft Docs
完全バックアップを使用したリストア
完全バックアップF1からリストアする例です。
今回はわかりやすいように、別のDB名（restore01）でリストアを行います。
RESTORE DATABASE [restore01]
FROM DISK = N'D:\MSSQL\Backup\20220528\F1.BAK'
WITH
MOVE N'backup_test' TO N'D:\MSSQL\DATA\restore01.mdf'
,MOVE N'backup_test_log' TO N'D:\MSSQL\DATA\restore01_log.ldf'
,RECOVERY
クエリ実行後のDBの状態です。
Aテーブルのみがリストアされています。
完全＋差分バックアップを使用したリストア
完全バックアップF1と差分バックアップD2からリストアする例です。
--完全バックアップからリストア
RESTORE DATABASE [restore02]
FROM DISK = N'D:\MSSQL\Backup\20220528\F1.BAK'
WITH
MOVE N'backup_test' TO N'D:\MSSQL\DATA\restore02.mdf'
,MOVE N'backup_test_log' TO N'D:\MSSQL\DATA\restore02_log.ldf'
,NORECOVERY
--差分バックアップからリストア
RESTORE DATABASE [restore02]
FROM DISK = N'D:\MSSQL\Backup\20220528\D2.BAK'
WITH
RECOVERY
クエリ実行後のDBの状態です。
Eテーブルまでがリストアされています。
完全＋差分＋ログバックアップを使用したリストア
完全バックアップF1と差分バックアップD2、ログバックアップT3からリストアする例です。
--完全バックアップからリストア
RESTORE DATABASE [restore03]
FROM DISK = N'D:\MSSQL\Backup\20220528\F1.BAK'
WITH
MOVE N'backup_test' TO N'D:\MSSQL\DATA\restore03.mdf'
,MOVE N'backup_test_log' TO N'D:\MSSQL\DATA\restore03_log.ldf'
,NORECOVERY
--差分バックアップからリストア
RESTORE DATABASE [restore03]
FROM DISK = N'D:\MSSQL\Backup\20220528\D2.BAK'
WITH
NORECOVERY
--トランザクションログバックアップからリストア
RESTORE LOG [restore03]
FROM DISK = N'D:\MSSQL\Backup\20220528\T3.BAK'
WITH
RECOVERY
クエリ実行後のDBの状態です。
Fテーブルまでがリストアされています。
完全＋ログバックアップを使用したリストア
完全バックアップF1とログバックアップT1、T2、T3からリストアする例です。
結果は前述のrestore03と同じですが、使用するバックアップファイルの組み合わせを変えてリストアも可能です。
--完全バックアップからリストア
RESTORE DATABASE [restore04]
FROM DISK = N'D:\MSSQL\Backup\20220528\F1.BAK'
WITH
MOVE N'backup_test' TO N'D:\MSSQL\DATA\restore04.mdf'
,MOVE N'backup_test_log' TO N'D:\MSSQL\DATA\restore04_log.ldf'
,NORECOVERY
--トランザクションログバックアップからリストア
RESTORE LOG [restore04]
FROM DISK = N'D:\MSSQL\Backup\20220528\T1.BAK'
WITH
NORECOVERY
RESTORE LOG [restore04]
FROM DISK = N'D:\MSSQL\Backup\20220528\T2.BAK'
WITH
NORECOVERY
RESTORE LOG [restore04]
FROM DISK = N'D:\MSSQL\Backup\20220528\T3.BAK'
WITH
RECOVERY
クエリ実行後のDBの状態です。
Fテーブルまでがリストアされています。
完全＋差分＋ログ＋ログ末尾バックアップを使用したリストア
完全バックアップF1と差分バックアップD2、ログバックアップT3、ログ末尾バックアップT4からリストアする例です。
出典：ログ末尾のバックアップ (SQL Server) - SQL Server | Microsoft Docs
--ログ末尾のバックアップをとる
BACKUP LOG [backup_test]
TO DISK = N'D:\MSSQL\Backup\20220528\T4.BAK'
GO
--完全バックアップからリストア
RESTORE DATABASE [restore05]
FROM DISK = N'D:\MSSQL\Backup\20220528\F1.BAK'
WITH
MOVE N'backup_test' TO N'D:\MSSQL\DATA\restore05.mdf'
,MOVE N'backup_test_log' TO N'D:\MSSQL\DATA\restore05_log.ldf'
,NORECOVERY
--差分バックアップからリストア
RESTORE DATABASE [restore05]
FROM DISK = N'D:\MSSQL\Backup\20220528\D2.BAK'
WITH
NORECOVERY
--トランザクションログバックアップからリストア
RESTORE LOG [restore05]
FROM DISK = N'D:\MSSQL\Backup\20220528\T3.BAK'
WITH
NORECOVERY
--ログ末尾のバックアップからリストア
RESTORE LOG [restore05]
FROM DISK = N'D:\MSSQL\Backup\20220528\T4.BAK'
WITH
RECOVERY
クエリ実行後のDBの状態です。
Gテーブルまでがリストアされています。
おわりに
SQL Serverのバックアップとリストアの基本動作をまとめたことで、今まであまり理解できていなかった復旧モデルや、リカバリのパターンについて自分なりに整理できました。
BACKUPステートメントやRESTOREステートメントについても、まだ理解できていない部分が多々あるので、どこかのタイミングでまとめたいです。