LoginSignup
42

More than 1 year has passed since last update.

SQL Server:バックアップとリストアの基本

Last updated at Posted at 2022-05-28

はじめに

バックアップとリストアは、データを保護するためにとても重要な仕組みです。
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) と言います。

ログ先行書き込み1
ログ先行書き込み2
ログ先行書き込み3
ログ先行書き込み4
ログ先行書き込み5

チェックポイント

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の内部動作を理解した上で、バックアップとリストアのパターン例を考えてみました。

バックアップ例

復旧モデルが完全復旧モデルのデータベースに対して、以下のようにバックアップを行っているとします。

バックアップ例

リストア例

上記のバックアップスケジュールで、障害が発生した場合のリストアのパターンについて確認していきます。

リストア例1

障害発生直前の状態に戻すためには、まず完全バックアップはF2を使用します。
差分バックアップはD4を使用します。差分バックアップは、前回の完全バックアップ時点から変更されたデータの差分が含まれているため、差分バックアップD3は使用しません。
差分バックアップ以後の更新は、障害発生の直前のトランザクションログバックアップT6を使用してロールフォワードします。
障害発生時にコミットされていなかったトランザクションは、ロールバックされ、実行される前の状態に戻ります。

リストア例2

もし、差分バックアップのD4が破損したなどで使用できない場合、完全バックアップF2と、差分バックアップD3、トランザクションログバックアップのT5T6を使用して復旧も可能です。

出典:【第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を
使用すれば、視覚的にどの時点に復元するか選択できるので、手順を紹介します。

  1. バックアップを実行したDB名上で右クリック→タスク→復元→データベースをクリックします。
    SSMS_復元

  2. タイムラインをクリックします。
    SSMS_タイムライン

  3. 復元したい日時を選択し、OKをクリックします。
    SSMS_バックアップのタイムライン

  4. OKをクリックすれば、リストアが開始されます。必要に応じて、リストア先のDB名を変更したり、既存のDBに上書きするオプションを選択してください。
    SSMS_データベースの復元

クエリを使用した操作

次に、クエリを実行して、完全バックアップ、差分バックアップ、トランザクションログバックアップからそれぞれリストアしていきます。

出典:RESTORE (Transact-SQL) - SQL Server | Microsoft Docs

完全バックアップを使用したリストア

完全バックアップF1からリストアする例です。
今回はわかりやすいように、別のDB名(restore01)でリストアを行います。

リストアの実行例1

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テーブルのみがリストアされています。
リストア1

完全+差分バックアップを使用したリストア

完全バックアップF1と差分バックアップD2からリストアする例です。

リストアの実行例2

--完全バックアップからリストア
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テーブルまでがリストアされています。
リストア2

完全+差分+ログバックアップを使用したリストア

完全バックアップF1と差分バックアップD2、ログバックアップT3からリストアする例です。

リストアの実行例3

--完全バックアップからリストア
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テーブルまでがリストアされています。
リストア3

完全+ログバックアップを使用したリストア

完全バックアップF1とログバックアップT1T2T3からリストアする例です。
結果は前述のrestore03と同じですが、使用するバックアップファイルの組み合わせを変えてリストアも可能です。

リストアの実行例4

--完全バックアップからリストア
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テーブルまでがリストアされています。
リストア4

完全+差分+ログ+ログ末尾バックアップを使用したリストア

完全バックアップF1と差分バックアップD2、ログバックアップT3、ログ末尾バックアップT4からリストアする例です。

出典:ログ末尾のバックアップ (SQL Server) - SQL Server | Microsoft Docs

リストアの実行例5

--ログ末尾のバックアップをとる
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テーブルまでがリストアされています。
リストア5

おわりに

SQL Serverのバックアップとリストアの基本動作をまとめたことで、今まであまり理解できていなかった復旧モデルや、リカバリのパターンについて自分なりに整理できました。
BACKUPステートメントやRESTOREステートメントについても、まだ理解できていない部分が多々あるので、どこかのタイミングでまとめたいです。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
42