SQL Server 2005 以降を使用していて、完全バックアップ + 差分バックアップの運用をしている場合に使用できる方法になります。
■完全バックアップ + 差分バックアップの運用
SQL Server の復旧モデルを [完全] [一括ログ] にしている場合は、差分バックアップを取得することが可能です。
差分バックアップを利用すると以下のようなバックアップウィンドウを設定することが可能となります。
月に完全バックアップを取得し、以降の曜日では差分バックアップを取得することでバックアップのサイズを減らし、時間を短縮することが可能です。
上記はバックアップのタクトを表したものになりますが、バックアップのサイズを各アイコンの長さで示すと以下のようになります。
差分バックアップは [最後に取得した完全バックアップからの差分] となりますので水の状態に戻すためには、
- 月の完全→火の差分→水の差分
ではなく
- 月の完全→水の差分
というように戻すタイミングの差分バックアップと最後に取得した完全バックアップの 2 つを使用することで特定のタイミングに戻すことが可能です。
では、火 に計画外の事象で完全バックアップを取得する必要が発生したとします。
その場合、完全と差分バックアップの関係は以下のようになります。
月に完全、火以降は差分でバックアップを取得するスケジュールを組んでいた場合、通常のバックアップの取得方法で火に完全バックアップを取得してしまうと、水のスケジュールで取得している差分バックアップは通常のバックアップウィンドウで取得している [完全 A] ではなく、 [完全B] に関連付いたバックアップとなってしまいます。
# 差分 B は完全 B からの差分となりますので、サイズは小さくなります。
計画外のバックアップを取得してもバックアップの関連付けは以下のようになっているとリストア方法も悩まずにすみ楽ですよね。
■COPY_ONLY オプション
このようなバックアップを取得するために、SQL Server 2005 以降では、BACKUP DATABASE に [COPY_ONLY] というオプションが追加されています。
SSMS の GUI から取得する場合には、[コピーのみのバックアップ] を有効にすると、COPY_ONLY のバックアップとなります。
T-SQL で取得する場合には COPY_ONLY オプションを指定します。
BACKUP (Transact-SQL)
実際のクエリを書くと以下のようになります。
| BACKUP DATABASE [TEST] TO? DISK = N’C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLBackupTEST_DB.bak’ WITH? COPY_ONLY, NOFORMAT, NOINIT,? NAME = N’TEST-完全 データベース バックアップ’, SKIP, NOREWIND, NOUNLOAD,? STATS = 10 GO |
?
COPY_ONLY オプションを指定すると、DCM (差分変更マップ) のビットがクリアされずに完全バックアップを取得することが可能となります。
DCM ですが、完全バックアップ取得後に変更があったエクステントのビットマップ情報になります。
DCM は以下のクエリで確認することが可能です。
# TEST というデータベースの DCM の確認例です。
| DBCC TRACEON(3604) DBCC PAGE(N’TEST’, 1, 6, 1) DBCC TRACEOFF(3604) |
最初の DCM はページ番号 6 に格納がされていますので、第 3 引数は 6 (ページ目) を指定しています。
完全バックアップ取得直後の DCM のデータは以下のようになっています。
データの変更は発生させていないため、変更されたエクステントの情報は記録されていません。
それではテーブルに何件かデータを挿入して、DCM を確認してみたいと思います。
データの変更をしたことにより、DCM の情報が以下のように変更されます。
さらに変更をするとこのように。
完全バックアップ以降に変更がされたエクステントは、この DCM という領域で管理され、差分バックアップでは DCM にビットが立っているエクステントの情報を取得します。
一度、以下のようなクエリで差分バックアップを取得してみます。
| BACKUP DATABASE [TEST] TO? DISK = N’C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLBackupTEST_DIFF.bak’ WITH? DIFFERENTIAL , NOFORMAT, INIT,? NAME = N’TEST-差分 データベース バックアップ’, SKIP, NOREWIND, NOUNLOAD,? STATS = 10 GO |
差分バックアップでは DCM の内容はクリアされないため、差分バックアップ取得後の DCM の内容は上記の DCM の内容と変わりません。
差分バックアップでは DCM の内容がクリアされないため、次回の完全バックアップ取得までは、変更されたエクステントの情報が追跡され、差分バックアップのファイルサイズが増えていくことになります。
一度完全バックアップを取ると DCM の内容はこのようにクリアされます。
COPY_ONLY でバックアップを取得するとどうなるでしょう。
バックアップを取得する前の DCM の情報はこのようになっています。
以下のクエリを実行して COPY_ONLY のバックアップを取得して、その後 DCM を確認してみます。
| BACKUP DATABASE [TEST] TO? DISK = N’C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLBackupTEST_COPY.bak’ WITH? COPY_ONLY, NOFORMAT, INIT,? NAME = N’TEST-完全 データベース バックアップ’, SKIP, NOREWIND, NOUNLOAD,? STATS = 10 GO |
完全バックアップを取得しても、DCM の内容はクリアされていないことが確認できます。
これが COPY_ONLY のバックアップとなります。
DCM の内容がクリアされないので、通常のバックアップウィンドウで取得しているバックアップのリストア方法を変えずに計画外 (スケジュールされたバックアップとは別の任意のタイミング) のバックアップを取得することが可能となります。
停電対応等で、通常のバックアップとは異なるタイミングでバックアップの取得が必要になった場合、COPY_ONLY を利用するとバックアップ管理が楽になっていいかもしれないですね。
[…] い解説はcopy_onlyについてhttps://blog.engineer-memo.com/2011/03/17/sql-server-2005-%E4%BB%A5%E9%99%8D%E3%81%A7%E8%A8%88%E7%94%BB%E5%A4%96%E3%83%90%E3%83%83%E3%82%AF%E3%82%A2%E3%83%83%E3%83%97%E3%82%92%E5%8F%96%E5%BE%97/ を参照下さい。 […]
SQL Server 2012でバックアップ リストアしてみました | blog.server-tech.xyz
28 3月 21 at 11:30