SQL Server のバックアップとリストアの手順メモです。

環境

• OS: Windows Server 2019 Standard Evaluation
• DB: Microsoft SQL Server 2019 Developer Edition

復旧モデルについて

一般的にデータベースのバックアップと復元の方法には2種類あり、1つはデータベースのフルバックアップのみを取得する方法で、もう1つはフルバックアップとトランザクションログをバックアップする方法です。

フルバックアップのみを取得する方法では、バックアップを取得した時点のデータへ復元することはできますが、バックアップ取得以降に変更されたデータについては復元することができません。

フルバックアップとトランザクションログの両方をバックアップする方法では、トランザクションログに各トランザクションで変更されたデータの履歴が保存されているため、フルバックアップ取得後の変更についても復元することができます。

2つ目のトランザクションログを使った復元は、任意の時点への復元ができるという意味で PITR (Point-in-time Recovery) と呼ばれます。

SQL Server では、どちらの方法でバックアップが取得されるかに応じて “復旧モデル” を設定します。

復旧モードの違いによって、トランザクションログがクリーンアップされるタイミングが異なります。

復旧モデルには以下の 3つのがあります。

• 単純復旧モデル
• 完全復旧モデル
• 一括ログ復旧モデル

単純復旧モデル

フルバックアップのみを取得する運用の場合、”単純復旧モデル” を選択します。

このモデルではトランザクションログのバックアップは取得されないため、コミットされたトランザクションのログは随時削除され、不要なトランザクションログが溜まらないようになります。

完全復旧モデル

フルバックアップとトランザクションログのバックアップを両方取得し、PITR を実現する場合は “完全復旧モデル” に設定します。

このモデルではトランザクションがコミットされた後も、バックアップが取得されるまでトランザクションログは削除されず残り続けます。

一括ログ復旧モデル

完全復旧モデルと似たものに、”一括ログ復旧モデル” というものもあります。完全復旧モデルとの違いは、一括データ処理についてのトランザクションログを記録するかしないかという点です。

bcp ツールや BULK INSERT 文でデータを一括で登録した場合、各行の変更についてのトランザクションログが記録されます。

ただこの変更はすぐデータファイルに書き出されるのが期待され、またトランザクション自体もすぐにコミットされると考えられるため、一括登録時のトランザクションログの重要性はあまり高くありません。

そのため一括ログ復旧モデルでは、トランザクションログの容量を節約するために一括登録時のログを最小限に抑えるようになっています。

ただ、一括ログ復旧モデルにも制限はあり、そのうちの1つに PITR をサポートしていないというものがあります (具体的には、RESTORE LOG コマンドで STOPAT オプションを使用できません)。

データを復元する時は常に最新のデータに復元する、ということであれば一括ログ復旧モデルでも問題ありませんが、例えば特定の時間のデータにデータを復旧することがある場合は、一括ログ復旧モデルは使えず、完全復旧モデルを選択する必要があります。

復旧モデルの設定方法

復旧モデルはデータベース単位で設定します。完全復旧モデルに設定する例は以下の通りです。

SQL

ALTER DATABASE my_database SET RECOVERY FULL;

用語

SQL Server のバックアップでは、いくつか独特な用語が使われています。実際の運用ではあまり意識する必要はありませんが、ドキュメントにはよく出てくるので意味を理解しておくと便利だと思います。

Backup Set

1回のバックアップ操作で、バックアップファイルに書き込まれるコンテンツを “Backup Set” と言います。

SQL Server では、1回のバックアップ操作で複数のファイルにデータを保存することができます。例えば3つのファイルを指定してバックアップを行ったとき、バックアップデータは3つのファイルに分散して保存されます。

※同じデータが3つのファイルに複製されて保存されるのでは無い点に注意してください。

以下の例では、3つのファイルにバックアップを保存しています。

SQL

BACKUP DATABASE my_database
TO DISK = 'backup1.bak', 
   DISK = 'backup2.bak',
   DISK = 'backup3.bak';

バックアップは、同じファイルに複数回保存できます。バックアップを保存すると、バックアップファイルのヘッダーに 1回目のバックアップの情報、2回目のバックアップの情報… と記録されていきます。

例えば同じファイルに2回バックアップを保存した時、Backup Set は 2つということになります。

同じファイルに複数回バックアップを取得する場合、バックアップするデータベースや使用するファイルは常に同じである必要があります。

Media Family

バックアップは複数のファイルに分散して書き込むことができると書きましたが、このそれぞれのファイルを “Media Family” と呼びます。

例えば 3つのファイルを指定してバックアップした場合、各ファイルが “Media Family” と呼ばれます。この場合、Media Family が 3つある状態です。

バックアップを取得する時、同じコンテンツを複数のファイルにコピーすることができ、これは “ミラーリング” と呼ばれています。

ミラーリングされたファイルのペアは 1つの Media Family とカウントされます。少しややこしいのですが、Media Family という用語を使用する場面はあまりないと思いますので、あまり意識しなくても良いと思います。

バックアップファイルをミラーリングして保存するには、以下のように SQL を実行します。

SQL

BACKUP DATABASE my_database
TO DISK = 'backup1.bak', 
   DISK = 'backup2.bak',
   DISK = 'backup3.bak'
MIRROR TO DISK = 'backup1_mirror.bak',
   DISK = 'backup2_mirror.bak',
   DISK = 'backup3_mirror.bak';

Media Set

Backup Set と Media Family 全てを含むバックアップデータを総じて “Media Set” と呼びます。

上記で説明した通り、バックアップは複数のファイルに保存でき、また同じファイルに複数回バックアップを保存できます。

例えば 3つのファイルに継続してバックアップを保存している時、各ファイルに1回目のバックアップ、2回目のバックアップ、3回目の … とバックアップデータが溜まっていきます。これらすべてのバックアップデータを指して “Media Set” と呼びます。

バックアップを初めて取得する時、WITH FORMAT というオプションを指定する必要があるのですが、このオプションが指定された時に、Media Set が新規に作成されたと考えます。

WITH FORMAT オプションでバックアップファイルが作成された時、バックアップされたデータベースや指定されたファイルの情報等がバックアップファイルのヘッダーに記録されます。

以降、これらのバックアップファイルには、最初に取得した時と同じデータベース、同じファイルの組み合わせでないとバックアップを保存できなくなります。

WITH FORMAT を使って新しいバックアップを取得する例は以下の通りです。

SQL

BACKUP DATABASE my_database
TO DISK = 'backup1.bak', 
   DISK = 'backup2.bak',
   DISK = 'backup3.bak'
WITH FORMAT;

推奨事項

バックアップの頻度

トランザクションログのバックアップは、15分 ～ 30分毎に取得するのが推奨されています。

一般的なユースケースでは、フルトランザクションを毎日取得し、フルトランザクション取得後に15分毎にトランザクションログを取得する、というスケジュールから検討を始めるのが良いのではと思います。

またフルバックアップ取得時は常に新しいバックアップファイルを作成し、トランザクションログはその日に取得されたフルバックアップのファイルに追加していく、というように運用すれば、各日付のバックアップが1つのバックアップファイルにまとめられるため便利になると思います。

バックアップのログの抑制

デフォルトでは、バックアップが成功すると SQL Server ログとイベントログにログが記録されます。バックアップの頻度が高いとログの容量が増えるため、ログの抑制を検討するのが推奨されます。

バックアップのログは トレースフラグ 3326 を使って抑制できます。

(なお DBCC TRACEON で設定したトレースフラグは再起動すると元に戻ります。トレースフラグの操作については公式ドキュメントを参照してください)

SQL

DBCC TRACEON (3226, -1)

バックアップ – リストア 手順

それでは、バックアップの取得からリストアするまでの手順を説明します。

今回は “完全復旧モデル” を選択し、フルバックアップ + トランザクションログ のバックアップ両方を取得した後、リストアでは最新のトランザクションログまで復旧するという手順を想定します。

なお、バックアップとリストアは SQL Server Management Studio を使って UI 上で操作することもできますが、今回は SQL で操作する方法を紹介します。

1). 事前準備 – テストデータベースの作成

テスト用のデータベースを作成します。

SQL

USE master;
GO

CREATE DATABASE backup_test;
GO

-- 完全復旧モデルとして設定
ALTER DATABASE backup_test SET RECOVERY FULL;  
GO

USE backup_test;
GO

-- テストテーブルを作成
CREATE TABLE table1 (id INT IDENTITY PRIMARY KEY, name NVARCHAR(255));
GO

BEGIN TRANSACTION
INSERT INTO table1 (name) VALUES ('name 1'), ('name 2');
COMMIT TRANSACTION

SELECT * FROM table1;
/*
id	name
1	name 1
2	name 2
*/

2). フルバックアップを取得

先ほど作成したデータベースのフルバックアップを取得します。

フルバックアップは既存のバックアップファイルに追加するのではなく、常に新しい Media Set を作成するようにします。

SQL

BACKUP DATABASE backup_test 
  TO DISK = 'C:\backup\backup_test_backup1.bak'
  WITH FORMAT;
GO
/*
データベース 'backup_test' の 376 ページ、ファイル 1 のファイル 'backup_test' を処理しました。
データベース 'backup_test' の 1 ページ、ファイル 1 のファイル 'backup_test_log' を処理しました。
BACKUP DATABASE により 377 ページが 0.069 秒間で正常に処理されました (42.593 MB/秒)。

Completion time: 2023-01-12T15:27:34.6792372+09:00
*/

3). 新しいデータを登録し、トランザクションログ バックアップを取得

ここで、新しいデータを登録しておきます。このデータはフルバックアップには含まれていないため、後でリストアする時にこのデータが復元されていれば、トランザクションログからデータが復旧されたのを確認できます。

現実のユースケースでは、フルバックアップ取得後も継続的にデータが変更され、また継続的にトランザクションログのバックアップが取得されます。

ここでは現実のユースケースをシミュレーションするために、2回データを登録し、それぞれのデータ登録後にトランザクションログのバックアップを取得します。

SQL

USE backup_test;
GO

-- 新しいデータを登録。このデータは完全バックアップには含まれていない
BEGIN TRANSACTION
INSERT INTO table1 (name) VALUES ('name 3'), ('name 4');
COMMIT TRANSACTION
/*
(2 rows affected)

Completion time: 2023-01-12T15:27:54.3826627+09:00
*/

-- トランザクションログのバックアップを先ほど作成したバックアップファイルに追加
BACKUP LOG backup_test 
  TO DISK = 'C:\backup\backup_test_backup1.bak';
GO
/*
データベース 'backup_test' の 1 ページ、ファイル 2 のファイル 'backup_test_log' を処理しました。
BACKUP LOG により 1 ページが 0.013 秒間で正常に処理されました (0.563 MB/秒)。

Completion time: 2023-01-12T15:28:08.3200178+09:00
*/

-- もう一度データ登録し、トランザクションログ バックアップを取得
BEGIN TRANSACTION
INSERT INTO table1 (name) VALUES ('name 5'), ('name 6');
COMMIT TRANSACTION
/*
(2 rows affected)

Completion time: 2023-01-12T15:28:42.5269963+09:00
*/

BACKUP LOG backup_test TO DISK = 'C:\backup\backup_test_backup1.bak';
GO
/*
データベース 'backup_test' の 2 ページ、ファイル 3 のファイル 'backup_test_log' を処理しました。
BACKUP LOG により 2 ページが 0.010 秒間で正常に処理されました (1.025 MB/秒)。

Completion time: 2023-01-12T15:28:53.8812016+09:00
*/

4). バックアップを新しいデータベースへリストア

それではバックアップを新しいデータベースへリストアします。

現実のユースケースでは、元のデータベースが何らかの原因で壊れた状態だと思いますので、新しいサーバーに SQL Server を新規インストールし、バックアップからデータをリストアする、という手順になります。

4-1). バックアップに含まれるデータベースファイルの確認

データベースにはデータファイル (.mdf ファイル) とトランザクションログファイル (.ldf) ファイルがあり、バックアップにはそれぞれのファイルのバックアップが含まれています。

新しいデータベースにバックアップを復元する場合、各ファイルをどこに復元するかを指定する必要があるため、まずバックアップに含まれるデータベースファイルを確認します。

バックアップに含まれるファイルを確認するには、RESTORE FILELISTONLY コマンドを使用します。

SQL

RESTORE FILELISTONLY FROM DISK = 'C:\backup\backup_test_backup1.bak';
/*
LogicalName     PhysicalName                    Type FileGroupName (省略)
backup_test     C:\...\DATA\backup_test.mdf     D    PRIMARY
backup_test_log C:\...\DATA\backup_test_log.ldf L    NULL
*/

4-2). フルバックアップのリストア

今回は論理名が backup_test というデータファイルと、backup_test_log というトランザクションログファイルがあるのがわかりましたので、これら 2つのファイルを指定してフルバックアップからデータベースをリストアします。

なお、ここでは new_backup_test という名前の新しいデータベースにデータを復元することにします。

データベースをリストアするには RESTORE DATABASE コマンドを使用します。データベースのリストア後、続けてトランザクションログを復元する場合は WITH NORECOVERY というオプションを指定する必要がある点に注意してください。

ちなみに、この WITH NORECOVERY オプションはリストア後もデータベースをオンライン状態にしないという意味です。全てのリストア操作が終わり、データベースに書き込みを行える状態になったら WITH RECOVERY オプションを指定してデータベースをオンライン状態に設定します。

SQL

RESTORE DATABASE new_backup_test
 FROM DISK = 'C:\backup\backup_test_backup1.bak'
 WITH NORECOVERY,
 MOVE 'backup_test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\new_backup_test.mdf',
 MOVE 'backup_test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\new_backup_test_log.ldf';
/*
データベース 'new_backup_test' の 376 ページ、ファイル 1 のファイル 'backup_test' を処理しました。
データベース 'new_backup_test' の 1 ページ、ファイル 1 のファイル 'backup_test_log' を処理しました。
RESTORE DATABASE により 377 ページが 0.046 秒間で正常に処理されました (63.890 MB/秒)。

Completion time: 2023-01-12T15:32:44.1496867+09:00
*/

4-4). トランザクションログのリストア

続いてトランザクションログを復元します。

実際の運用では、サーバーが急にダウンした場合にいくつのトランザクションログが含まれているか把握するのが難しいと思いますので、まずバックアップファイルにいくつのバックアップが含まれているかを確認します。

上記で Backup Set の情報はバックアップファイルのヘッダーに記録されると記載しました。

バックアップファイルのヘッダーは RESTORE HEADERONLY コマンドで確認できるので、このコマンドを使ってファイルにいくつのバックアップが含まれているかを確認します。

SQL

RESTORE HEADERONLY FROM DISK = 'C:\backup\backup_test_backup1.bak';
/*
Position FirstLSN          LastLSN           BackupTypeDescription (省略)
1        37000000028300001 37000000028600001 Database
2        37000000028300001 37000000029800001 Transaction Log
3        37000000028300001 37000000031900001 Transaction Log
*/

上記では、Position 2 と 3 が Transaction Log のバックアップということがわかります。

今回、手順 3). で2回データを追加登録し、2回トランザクションログのバックアップを取得しましたので、これがそれぞれ Position 2 と Position 3 のバックアップに対応しています。

(ちなみに “LSN” (Log Sequence Number) はトランザクションに付与される連番で、上記の FirstLSN や LastLSN とあるのは、各バックアップにどのトランザクションまでのデータが含まれているかを示しています)

トランザクションログをリストアする時は、各トランザクションログを順にリストアしていきます。

トランザクションログのリストアは RESTORE LOG コマンドで行い、どの Position のログを復元するかは WITH FILE オプションで指定します。

今回は Position 2 と Position 3 のトランザクションログ バックアップをリストアします。

実際のシナリオでは、例えば 15分毎にトランザクションログをバックアップしていた場合、バックアップの数が多くなるため、予めスクリプトなどを準備しておくのが良いと思います。

SQL

-- Position 2 のトランザクションログ バックアップをリストア
RESTORE LOG new_backup_test
 FROM DISK = 'C:\backup\backup_test_backup1.bak'
 WITH NORECOVERY, FILE = 2;
/*
データベース 'new_backup_test' の 0 ページ、ファイル 2 のファイル 'backup_test' を処理しました。
データベース 'new_backup_test' の 1 ページ、ファイル 2 のファイル 'backup_test_log' を処理しました。
RESTORE LOG により 1 ページが 0.009 秒間で正常に処理されました (0.813 MB/秒)。

Completion time: 2023-01-12T15:35:20.7156645+09:00
*/


-- Position 3 のトランザクションログ バックアップをリストア
RESTORE LOG new_backup_test
 FROM DISK = 'C:\backup\backup_test_backup1.bak'
 WITH NORECOVERY, FILE = 3;
/*
データベース 'new_backup_test' の 0 ページ、ファイル 3 のファイル 'backup_test' を処理しました。
データベース 'new_backup_test' の 2 ページ、ファイル 3 のファイル 'backup_test_log' を処理しました。
RESTORE LOG により 2 ページが 0.009 秒間で正常に処理されました (1.139 MB/秒)。

Completion time: 2023-01-12T15:35:43.2692550+09:00
*/

4-5). データベースをオンライン (書き込み可能状態) に設定

最後に、データベースをオンラインにすれば復元したデータベースを使用できるようになります。

リストアしたデータベースをオンライン状態にするには、RESTORE DATABASE に WITH RECOVERY オプションを付けて実行します。

SQL

RESTORE DATABASE new_backup_test WITH RECOVERY;
/*
RESTORE DATABASE により 0 ページが 0.242 秒間で正常に処理されました (0.000 MB/秒)。

Completion time: 2023-01-12T15:25:07.8672818+09:00
*/

-- 結果を確認。トランザクションログからもデータが復元されているのを確認できます。
USE new_backup_test;
GO

SELECT * FROM table1;
/*
id	name
1	name 1
2	name 2
3	name 3
4	name 4
5	name 5
6	name 6
*/

バックアップとリストア手順は以上です。