みなさん、こんにちは。今回はバックアップからのリストアによる SQL Server のリカバリ手順についてご紹介します。
本手順では SQL Server 2019 を使用して、下記 の2 通りのリカバリ手順について記載しています。
A. 障害発生直前までのリカバリ手順
B. ある特定の時点までのリカバリ手順
■前提条件
障害発生直前、またはある特定の時点までデータベースを復旧するためには、下記 の 3 つの条件を満たしていることが必要です。
- 完全バックアップを取得済みであること。
- トランザクション ログが破損していないこと。
- データベースの復旧モデルが「完全」であること。
※ 本手順は ServerA という名前のサーバー上にインストールされた SQL Server の test データベースについて、2021 年 9 月 18 日に下記のバックアップを取得済みであることを前提として記載しています。
※復旧モデルは Microsoft SQL Server Management Studio から、該当データベースを右クリックして、［タスク］メニューの［バックアップ］をクリックすることで確認できます。
|
No |
バックアップ日時 |
バックアップの種類 |
バックアップ ファイル名 |
|
1 |
2021-09-18 02:00 |
完全バックアップ |
c:\work\full.bak |
|
2 |
2021-09-18 02:30 |
ログバックアップ |
c:\work\log1.trn |
|
3 |
2021-09-18 12:00 |
差分バックアップ |
c:\work\diff.bak |
|
4 |
2021-09-18 12:30 |
ログバックアップ |
c:\work\log2.trn |
A. 障害発生直前までのリカバリ手順
下記の流れで障害発生直前まで復元します。
- まだバックアップしていないトランザクションログを取得する(A-1)
- 最新の完全バックアップを復元する。(A-2)
- 最新の完全バックアップ以降に差分バックアップを取得している場合には、最新の差分バックアップを復元する。(A-3)
- すでに復元したバックアップ以降に取得したトランザクション ログバックアップをすべて復元する。(A-4,5)
具体的にディスク障害等でデータファイル (.mdf) が破損してしまった場合に、障害発生直前 (2021 年 9 月 18 日 12:30 時点) までデータベースを復旧する手順を下記にご紹介します。
A-1. コマンド プロンプトを起動し、下記のコマンドを実行して、残ったトランザクション ログ（まだバックアップしていないログ）を、NO_TRUNCATE オプションを使用してバックアップします。
---------------------------------------------------------------------------------------
C:\> sqlcmd -E -S ServerA
1> BACKUP LOG test TO DISK = 'C:\work\log3.trn' WITH NO_TRUNCATE
2> GO
---------------------------------------------------------------------------------------
※このログは、’ログ末尾’ や ’TAIL ログ’ とも呼ばれます。
※ NO_TRUNCATE オプションは、ログ末尾をバックアップするとともに、ログを切り捨てないという効果もあります。
A-2. 下記のコマンドを実行して、02:00 に取得した完全バックアップをリストアします。
--------------------------------------------------------------------------------------------------------
1> USE master
2> RESTORE DATABASE test FROM DISK = 'C:\work\full.bak' WITH NORECOVERY, REPLACE
3> GO
--------------------------------------------------------------------------------------------------------
※NORECOVERY オプションは、まだリストアすべきバックアップがある場合に使用するオプションです。NORECOVERY を指定してリストアを行った場合は、データベースが「復旧中」（リストア中）となり、ユーザーからの接続を一切できないようにブロックします。復旧状態のオプションに関しては以下の公式ドキュメントをご参照ください。
データベースの復元 ([オプション] ページ) - SQL Server | Microsoft Docs
※REPLACE オプションは、指定したデータベースと同じ名前のデータベースが既に存在している場合でも、データベースとその関連ファイルが作成されます。その場合、既存のデータベースは削除されます。
A-3. 下記のコマンドを実行して、12:00 に取得した差分バックアップをリストアします。
-----------------------------------------------------------------------------------------------
1> USE master
2> RESTORE DATABASE test FROM DISK = 'C:\work\diff.bak' WITH NORECOVERY
3> GO
-----------------------------------------------------------------------------------------------
A-4. 下記のコマンドを実行して、12:30 に取得したログ バックアップをリストアします。
---------------------------------------------------------------------------------------
1> USE master
2> RESTORE LOG test FROM DISK = 'C:\work\log2.trn' WITH NORECOVERY
3> GO
--------------------------------------------------------------------------------------
A-5. 下記のコマンドを実行して、上記手順 1 で取得した TAIL ログをリストアします。
-------------------------------------------------------------------------------------
1> USE master
2> RESTORE LOG test FROM DISK = 'C:\work\log3.trn' WITH RECOVERY
3> GO
-------------------------------------------------------------------------------------
※RECOVERY オプションは、リストア後にデータベースを完全復旧した状態にします。これは、リストアが完了したことの合図になり、残りのこれ以降のバックアップをリストアすることはできません。
A-6. 下記のコマンドを実行して、test データベースに接続します。
------------------
1> USE test
2> GO
------------------
接続できればリカバリは完了です。 test データベースは、障害発生直前の状態まで復旧しています。
B. ある特定の時点までのリカバリ方法
データを誤って削除してしまった場合等に、ある特定の時点までデータベースを復旧する手順を下記にご紹介します。
本手順では、2021 年 9 月 18 日 の 12 時15 分 00 秒 に誤ってデータを削除してしまい、2021 年 9 月 18 日 の 12 時 14 分 59 秒 の時点までデータベースを復旧することを想定しています。
B-1. 上記 ‘A. 障害発生直前までのリカバリ手順’ の手順 1 から 手順 3 までを実行します。
B-2. 下記のコマンドを実行して、12:30 に取得したログ バックアップをリストアします。
--------------------------------------------------------------------------------------------------------------------
1> USE master
2> RESTORE LOG test FROM DISK = 'C:\work\log2.trn' WITH RECOVERY, STOPAT = '2021-09-18 12:14:59'
3> GO
--------------------------------------------------------------------------------------------------------------------
※STOPAT オプションは、特定の時間までのデータへ復元したいという場合に使用するオプションです。
B-3. 下記のコマンドを実行して、test データベースに接続します。
------------------
1> USE test
2> GO
------------------
接続できればリカバリは完了です。 test データベースは、データが削除される直前の状態まで復旧しています。
< 参考情報 >
SQL Server データベースを特定の時点に復元する方法(完全復旧モデル) - SQL Server | Microsoft Docs
Posted at https://sl.advdat.com/3i8gqFo