SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server のバックアップの基本パターン

one comment

以前、SQLTO で [SQL Server のバックアップとリストアの基礎] というセッションを担当させていただきました。
ブログではバックアップについてまとめたことがなかったので少しまとめてみたいと思います。

2013/8/14 追記
投稿を見直して、内容を大幅に修正しています。

■バックアップの基本パターン


バックアップの基本パターンですが以下のようになると思います。

  • 週に一度のユーザーデータベースの完全バックアップ
  • ユーザーデータベースは完全バックアップ以外の曜日は差分バックアップ
  • 毎日システムデータベースを完全バックアップ
  • 一日に定期的な間隔でトランザクションログをバックアップ
  • 過去のバックアップファイル (bak/trn) の削除
  • 過去のメンテナンスプラン テキストレポート (txt) の削除
  • 過去のバックアップログ (msdb のレコード) の削除

SQL Server のバックアップでは、それぞれの処理をどのようなタイミングで実行するかを検討していきます。

 

■バックアップを取る前のデータベースの状態確認


バックアップの基本パターンは先ほど記載したものをベースにして考えますが、前提として考慮すべき内容が一つあります。

それは [破損しているデータベースのバックアップは破損している] ということです。

以下のような整合性エラーが発生しているテーブルがあります。
# 実際にはページのチェックサムのエラーが発生しています。
image

このデータベースを以下のようなクエリでバックアップを取得してみます。

BACKUP DATABASE TEST TO DISK=N’TEST.bak’

この時のバックアップの結果ですが以下のように正常にバックアップが完了しています。
image

この際のバックアップの内容ですが、破損しているデータベースを破損しているままバックアップを取得してしまっていることになります。
このバックアップをリストアした場合、破損したページについては破損した状態でリストアが行われます。
# RESTORE するときに CONTINUE_AFTER_ERROR を使用しないといけないかもしれませんが。

そのため、バックアップを取得する場合にはデータベースが破損していないことを確認する必要があります。

具体的には以下のような方法を使用します。

  • バックアップの取得前にデータベースに対して DBCC CHECKDB を実行する
  • バックアップを取得する際にチェックサムを確認する

バックアップの取得前に DBCC CHECKDB を実行することでデータベースの整合性を確認することができます。
今回の場合であれば、以下のような [情報] のレベルの内容がイベントビューアーに出力されます。これを通常監視で取得できれば整合性エラーが発生した際にトラップをすることができます。
# 情報なのでこの内容では、不整合エラーはトラップしにくいかもしれませんが。
image

メンテナンスプランの [データベースの整合性タスク] から DBCC CHECKDB を定期的に実行するためのメンテナンス用のジョブを作成することができます。

メンテナンスプランでエラーが検出された場合は、SSIS パッケージの失敗として [エラー] でイベントビューアーに出力がされますので、イベントビューアーで監視しいている場合にはこちらで検知を行うとよいかもしれませんね。
image

 

後者の場合は以下のように WITH CHECKSUM オプションを使用してバックアップを取得します。

BACKUP DATABASE TEST TO DISK = N’TEST.bak’ WITH CHECKSUM

この場合はイベントビューアーにエラーで出力されますのでトラップしやすいかと思います。
imageimage

なお、DBCC CHECKDB でエラーになった場合は、[SELECT @@ERROR] は 0 以外になりますので、これをキャッチして、RAISERROR でイベントビューアーに情報を出力するという方法もあります。
# WITH LOG を使用すればイベントビューアーにログをエラーを出力できますので。

SQL Server のデータページはチェックサムを保持していますので、ページ破損を意図的に発生させたい場合にはバイナリエディタで mdf / ndf を開き、データページの情報を書き換えると再現できます。
# char / varchar といった非 Unicode 文字列は格納した情報が半角英数で、ページ圧縮をしていない状態であれば、バイナリエディタの文字列検索で簡単に検出できますので、特定のページの破損を意図的に再現できます。

 

■データベースバックアップの取得


データベースバックアップはデータベースの更新頻度に応じて

  • 完全バックアップのみで運用
  • 完全バックアップと差分バックアップで運用

のどちらを使用するかを検討していきます。

 

完全バックアップはリストア時の起点となるため、必ず取得する必要があります。

完全バックアップの対象は

  • ユーザーデータベース
  • システムデータベース

に分けて考えると運用が楽だと思います。

ユーザーデータベースについては、サイズが小さい場合は、[2.] の差分バックアップは完全バックアップとしてしまってもよいのですが、サイズが大きい場合には毎日、完全バックアップを取得するとバックアップのサイズが大きくなってしまうので、完全バックアップ以外の曜日は差分バックアップを取得することでバックアップで必要な領域を節約することができます。

差分バックアップは前回の完全バックアップ以降の差分となりますので、更新量が少なければバックアップのサイズは少なくなります。全テーブルの全データが更新された場合には、差分バックアップは完全バックアップに近いサイズとなります。

システムデータベース (master / model / msdb) については通常、サイズはそれほど大きくならないので、毎日完全バックアップを取得しても問題はないと思います。

今回はユーザーデータベースとシステムデータベースのバックアップは以下のように取得するように計画をしてみます。

  • 週次で全データベース (ユーザーデータベース / システムデータベース) の完全バックアップを取得
  • 日次でユーザーデータベースの差分バックアップとシステムデータベースの完全バックアップを取得

 

■トランザクションログのバックアップ


復旧モデルを [完全] [一括ログ] にしている場合は、トランザクションログのバックアップを取得する必要があります。
復旧モデルが [単純] の場合はチェックポイント時のログ切り捨てとなり、自動的にログが切り捨てられますが、それ以外のモデルの場合は定期的なトランザクションログの切り捨てが必要となります。

トランザクションログのバックアップを取得しないとすでにデータファイルに反映済みのトランザクションログの再利用がされないので数分~数時間起きにトランザクションログのバックアップを取得するようにします。

なお、AWS の RDS では 5 分間隔でバックアップがされていたはずです。一般的な OLTP であれば 30 分間隔で取得してもよいかもしれないですね。

 

■クリーンアップの実施


バックアップはいつまでも残していても容量を消費するだけですので、不要となったバックアップ (bak / trn) は削除する必要があります。

メンテナンスプランとしてジョブを実行した場合にはテキスト形式のレポートが出力されますので、これも定期的に削除をする必要があります。

SQL Server バックアップ / SQL Server Agent の実行結果の情報は msdb (backupfile/backupset/sysjobhistory 等) に格納されます。
これも削除をしないと残ったままになってしまいますので定期的な削除が必要です。

クリーンアップについてはメンテナンスプランとして、

  • メンテナンス クリーンアップ タスク (不要なバックアップとメンテナンスプランのレポートを削除)
    # ファイルとして存在しているものを削除
  • 履歴クリーンアップ タスク (msdb から不要なログ情報を削除)
    # テーブル内のレコードとして存在しているものを削除

が用意されていますので、これを使うと便利です。

 

■バックアップを設定


最後に実際のどのようにバックアップを設定するかを確認していきたいと思います。

バックアップを設定するための簡単な手法としては、

を使用する方法があるかと思いますが、今回はメンテナンスプランを使用したいと思います。

なお、メンテナンスプランではサブプランを作成することができますが、このサブプランは SQL Server エージェントの新しいジョブとして登録がされます。
# サブステップではありませんので独立したジョブが作成されます。
image

 

■週次バックアップの取得

週次のバックアップの取得方法を見ていきます。

データベースのバックアップはデータベースの整合性が取れている状態で取得する必要がありますので、[データベースの整合性確認タスク] を最初に設定しています。
image
# データベースのサイズが大きい場合には CHECKDB を PHYSICAL_ONLY で実行することで時間を短縮できますがこのオプションはメンテナンスプランで設定ができないので、設定したい場合はメンテナンスプランを使用せずに独自に設定をする必要があります。

整合性確認タスクが正常に終了した場合にのみ以降の処理が実行され、バックアップの取得が行われています。
バックアップの取得は [データベースのバックアップ タスク] で実施します。
バックアップにはバックアップの整合性チェック (RESTORE VERIFYONLY によるリストアできるバックアップかの検証) をするオプションがありますのでこれを有効にしておくとよいと思います。
image

完全バックアップが取得できた場合、起点ができますので不要となったバックアップは削除をします。 
整合性確認タスクは成功時にのみ後続の処理を実行するようにしていますのでエラーが発見された場合は、削除は行われません。
また、バックアップが失敗した場合も後続の処理 (不要世代のバックアップ削除) は実施されませんので、バックアップが成功しない場合には、過去のバックアップが残ることになります。

バックアップの削除は [メンテナンス クリーン アップ タスク] で実施します。
image

メンテナンスクリーンアップタスクでは、以下の 2 種類のクリーンアップができますが、今回の週次のバックアップではバックアップファイルの削除を対象としています。
image
クリーンアップタスクでは削除対象とする拡張子を設定することができますが、複数の拡張子を指定することはできないようなので、データベースバックアップ (bak) とトランザクションログバックアップ (trn) を削除するタスクをそれぞれ作成する必要があります。

これらを踏まえ、今回は週次のバックアップとして以下のようなメンテナンスプランを作成しています。
実施している内容としては、

  1. データベースの整合性を確認し成功した (エラーのない) 場合のみ後続の処理を実行する
  2. すべてのデータベース (ユーザーデータベース / システムデータベース) の完全バックアップを取得
  3. 不要なデータベースバックアップ (bak) を削除
  4. 不要なトランザクションログバックアップ (trn) を削除

image

クリーンアップについては並列で実行しても問題ないかと思いますので以下のようにバックアップ後は並列で実行してもよいかもしれないですね。
image

 

■週次以外の曜日の差分バックアップ


ユーザーデータベースのサイズが小さい場合には、先ほどの週次のバックアップを日次で実行すればよいのですが、データベースのサイズが多い場合は、週次の曜日以外は差分バックアップを取得する必要が出てきます。

差分バックアップを取得する場合は、[バックアップの種類] を [差分] にした以下のようなメンテナンスプランを作成します。
image

差分バックアップの取得前にもデータベースの整合性確認を実施しています。
これで毎日整合性が確認され、破損があった場合には検出がされます。

ユーザーデータベースについては差分で取得をしていますが、システムデータベースについては完全で取得をしています。
これは、システムデータベースは復旧モデルが [単純] になっており差分バックアップが取得できないためです。
通常はさほどサイズが大きくないため、完全で取得してもバックアップの領域は圧迫しないかと思います。

msdb 以外のシステムデータベースは通常それほど更新が行われないはずなため、割り切って週次でもよいかもしれませんが。

今回は週次の完全バックアップ時に履歴を削除する (完全バックアップという起点ができたら不要バックアップを削除) という考えでいるため、差分バックアップ取得時には履歴は削除していません。

■日次のトランザクションログのバックアップ

トランザクションログのバックアップは 、[バックアップの種類] を [トランザクション ログ] に設定したバックアップになります。
これはすべてのユーザーデータベースのトランザクションログのバックアップを取得するだけのプランとなります。

image

トランザクションログは一日の中で定期的にバックアップが取得されるようにスケジューリングする必要があります。
image
トランザクションログのバックアップを取得する間隔はデータ更新量に応じて調整をする必要があります。
頻繁にバックアップを取得するとバックアップによるディスク負荷が増加しますが、トランザクションログ内のデータを減らすことができるため、ログファイルのサイズを小さいまま保つことができますので。
# トランザクションログの自動拡張時には瞬時初期化は使用できないので、拡張が発生した場合にはそれなりのオーバーヘッドが発生します。自動拡張はあくまでも保険的に考え、通常は発生しないようにしておくのが好ましいです。

なお、トランザクションログのバックアップは一日に数分間隔で実施する必要があるため、このバックアップを取得する前にデータベースの整合性チェックをするのはあまり現実的ではないと思います。
# DBCC CHECKDB はディスク負荷がかなり上がりますので。

毎日 1 回はデータベースの整合性チェックを行っているため、そこでエラーが検知された場合はそこまでのバックアップを使用して状態を戻せればよいのではないでしょうか。

 

■メンテナンスプランのログとジョブ実行ログの削除

最後にメンテナンスプランのログ (txt) とジョブ実行ログ (msdb のレコード) の削除を行います。
この設定をしておかないと、バックアップの履歴やジョブの実行履歴が蓄積され続け、履歴の確認が面倒になりますので定期的にデータを削除するようにします。
この辺の削除は日次や週次のバックアップ時に実行してもよいかとは思いますが今回は独自のプランとして作成しています。

メンテナンスプランのログの削除は [メンテナンス クリーン アップ タスク] で [メンテナンス プラン テキスト レポート] を削除するように設定します。デフォルトでは [txt] が拡張子ですので対象は txt に設定しておきます。
imageimage

なお、メンテナンスプラン実行時のログは SQL Server の ERROR ログ等を出力しているディレクトリにデフォルトで出力がされます。ログの出力場所は [レポートとログ記録] から変更することができます。
# ボタンが少しわかりにくいですが。
image 

ジョブの実行ログに関しては、[履歴クリーンアップ タスク] で実行します。
image

このタスクでは、msdb に格納されている以下のレコードを削除することが可能です。
# これを実行しないと msdb に実行ログやバックアップの情報が蓄積され続きます。
image

これらのタスクを利用して今回は以下のようなプランを作成しています。
image

上のプランでは並列で実行していますが、以下のように成功時に後続を実行する直列でも問題はありません。
image

 

■まとめ


これらの設定をバックアップの基本パターンとして設定しておくとよいかと思います。

今回の投稿で最終的に設定したメンテナンスプランは以下のようになります。

メンテナンスプラン 実行タクト 処理 内容
週次完全バックアップ 毎週 日曜日
00:15
1.データベースの整合性確認タスク
DBCC CHECKDB を実行して、
バックアップ取得前に全 DB の整合性を確認
2.データベースのバックアップタスク
全データベース (ユーザー/システム) の完全バックアップを取得
3.データベースバックアップのクリーンアップ
(メンテナンス クリーンアップ タスク)
不要な世代の bak ファイルを削除
4.トランザクションログバックアップのクリーンアップ
(メンテナンス クリーンアップ タスク)
不要な世代の trn ファイルを削除
日次差分バックアップ 毎週 月~土
00:15
1.データベースの整合性確認タスク DBCC CHECKDB を実行して、
バックアップ取得前に全 DB の整合性を確認
2.ユーザーデータベースの差分バックアップタスク
(データベースのバックアップタスク)
全ユーザーデータベースの差分バックアップを取得
3.システムデータベースの完全バックアップタスク
(データベースのバックアップタスク)
全システムデータベースの完全バックアップを取得
トランザクションログ
バックアップ
毎日
00:30
30 分間隔
1.データベースのバックアップタスク 全ユーザーデータベースのトランザクションログバックアップを取得
ログメンテナンス 毎日
01:00
1.メンテナンス クリーンアップ タスク 不要な世代の txt ファイルを削除
2.履歴クリーンアップ タスク msdb から不要な履歴レコードを削除

細かな設定をしようとするとメンテナンスプランでは対応が難しいことがありますのでその場合は SQL Server エージェントのジョブを自分で組んでいく必要がありますが、シンプルなものでしたらこれらの設定をしておくことを覚えておくとよいかと。

なお、臨時のバックアップを取得したい場合などは COPY_ONLY を使用して BACKUP を実行することを合わせて覚えておくとよいかと思います。
バックアップは連続性 (チェーン) を持っていますので、COPY_ONLY のオプションを指定しないとその連続性が崩れてしまいます。連続性が崩れると、メンテナンスプランで取得しているバックアップだけでは復旧ができなくなってしまいますのでこの辺は注意が必要です。

Written by masayuki.ozawa

8月 12th, 2013 at 10:17 pm

Posted in SQL Server

Tagged with

One Response to 'SQL Server のバックアップの基本パターン'

Subscribe to comments with RSS or TrackBack to 'SQL Server のバックアップの基本パターン'.

  1. […] personal blog : backup 計画 […]

Leave a Reply

*