SE の雑記

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

第 1 回 Get The Fact セミナーの振り返り その 5

3 comments

今回は [バックアップポリシー] について振り返っていきたいと思います。
# 参加から 2 週間経過したのでテンポよくまとめていかないとだいぶ記憶が薄れてきました…。

セミナーでは以下のような図を元にバックアップポリシーについてのお話しがありました。

image

 

■バックアップの基本的な設定

上の図は復旧モデルを [完全] または [一括ログ] にした場合のバックアップの基本的な設定になります。
# [単純] 復旧モデルの場合は、ログのバックアップが取れませんので、データベースのバックアップのみになります。

セミナーでは日に一度のデータベースバックアップと、一時間おきのログのバックアップで設定というような例でお話をされていたと思います。

SQL Server のバックアップは大きく分けて 3 種類あります。
# ファイル / ファイルグループでのバックアップも取得ができるのですが、今回は割愛しておきます。

  • 完全
  • 差分
  • トランザクションログ

完全 / トランザクションログは上の図で [データベース] [ログ] と書かれているバックアップになります。

[完全バックアップ] は、その時点 (バックアップを実行したタイミング) でのデータまで復元することが可能なバックアップとなります。
完全バックアップはそのバックアップ単体で戻すことが可能ですのでバックアップにはログも含まれています。

 

[差分バックアップ] は、前回の完全バックアップからの差分データのみを含んだバックアップになります。
完全バックアップを元にした差分になりますので、単体で戻すことはできず必ず前回の完全バックアップが必要となります。
image
また、差分バックアップは前回の完全バックアップからの差分データですので、完全バックアップを取得してからの期間が長くなり、全データが更新された状態になってしまうと、完全バックアップ相当のバックアップファイルのサイズとなります。

 

[トランザクションログバックアップ] は、ログレコードのバックアップになります。
復旧モデルのときにもお話しがあったのですが、復旧モデルを [完全] [一括ログ] にしている場合は必須になるバックアップとなります。
これらの復旧モデルに関しては、ログレコードは自動的に切り捨てはされず、バックアップを取得したタイミングで切り捨てが行われ、切り捨てられた個所が再利用可能となります。
そのため、ログのバックアップを取得しないとログがずっと蓄積された状態となり、ログ領域のディスクが枯渇することになります。
ログのバックアップは障害発生時に重要となるバックアップのため、できるだけ短い間隔で取得することが重要であるとセミナーではお話がありました。
# 1 時間おきのログのバックアップというお話はこのタイミングであった気がします。
データベースの完全バックアップを取得していない状態ではログバックアップは取得することはできません。
image

 

バックアップについてですが、以下の資料がわかりやすいと思います。
SQL Server 2000 の資料ですが、それ以降のバージョンでも考え方は同じですので。
第 4 章 バックアップと復元

 

■定期的なバックアップの設定

定期的なバックアップの設定方法として、[メンテナンスプランウィザード] によるバックアップの設定が紹介されていました。

ワンタイムのバックアップであれば、データベースを右クリックして、[タスク] → [バックアップ] からバックアップを取得することが可能です。
image

image

このバックアップに関しては、ワンタイムになりますので定期的なバックアップの取得の設定はできません。

定期的なバックアップをウィザード形式で設定する場合は、セミナーで紹介のあった [メンテナンスプランウィザード] を使用する事で設定ができます。

メンテナンスプランウィザードは [管理] → [メンテナンス プラン] → [メンテナンス プラン ウィザード] から起動することができます。
image

メンテナンス プラン ウィザードを使用することで、データベースのメンテナンスをする仕組みを数ステップで設定が可能となります。
image

今回のセミナーでお話しのあったメンテナンスプランに似たものを実際に作ってみたいと思います。

  1. [次へ] をクリックします。
    image
  2. [タスクごとに個別のスケジュールを使用する] を選択して、[次へ] をクリックします。
    image
  3. [データべすのバックアップ (完全)] [データベースのバックアップ (トランザクション ログ)] を選択して、[次へ] をクリックします。
    image
  4. [次へ] をクリックします。
    image
  5. [完全] バックアップの取得対象とバックアップのスケジュールを設定し、[次へ] をクリックします。
    今回は [すべてのデータベース] を毎日 [22:00] に取得するように設定をしています。
    image
  6. トランザクションログのバックアップを取得するデータベースとスケジュールを選択し、[次へ] をクリックします。
    今回は、[すべてのユーザー データベース] を [毎日1 時間置き] にバックアップを取得するように設定しています。
    すべてのユーザー データベースですが復旧モデルが単純以外のデータベースが対象となります。
    image
  7. ログの出力場所を設定し、[次へ ]をクリックします。
    image
  8. [完了] をクリックします。
    image
  9. [閉じる] をクリックします。
    image

以上でメンテナンスプランの作成は完了です。

今回は、[完全 バックアップ] の取得と、[トランザクションログ] のバックアップの取得を設定していますので二つのジョブが SQL Server エージェントに作成されています。

image

作成した 2 つのジョブを手動で実行して、バックアップの取得状況を確認してみたいと思います。
ジョブを手動実行する場合は、対象のジョブを右クリックして、[ステップでジョブを開始] をクリックします。
image

今回は、H ドライブにアックアップを取得しているのですが、データベースのバックアップ (.bak) とトランザクションログのバックアップ (.trn) が取得されていることが確認できます。
image

今回のメンテナンスプランですが、バックアップを取得するたびにタイムスタンプが設定されたバックアップファイルが蓄積されていきます。
image

また、ログに関してもバックアップを実行するたびに蓄積されていきます。
image

このバックアップとログの履歴 (世代) 管理ですが、[メンテナンス クリーンアップ タスク] を使用することで管理することができます。
先ほど作成したメンテナンスプランにこの履歴クリーンアップタスクを組み込んでみたいと思います。

作成したメンテナンスプランは、[管理] → [メンテナンス プラン] の下に作成がされますので、作成したプランをダブルクリックします。
image

そうするとメンテナンスプランのデザインウィンドウが開きます。
image

先ほど作成したプランが、[Subplan_1] [Subplan_2] として設定されています。
今回は [サブプランの追加] をクリックして、[Subplan_3] として[メンテナンス クリーンアップ タスク] を設定します。
image

タスクをダブルクリックすると設定画面になります。
このタスクを使用することで、バックアップファイルとメンテナンスプランのレポートを削除することが可能です。
拡張子単位で指定はできるのですが、ワイルドカードは使えないので、[bak] [tran] 用のバックアップファイル削除タスクを用意します。
imageimage

テキストレポートのクリーンアップタスクとしては以下の内容を設定しました。
image

今回はスケジュールも設定し、このような形でサブプランを設定してみました。
image

今回は 4 週経過したファイルが対象になっていますので、以下の PowerShell を実行して、ファイルの更新日付を変更してみました。
# クリーンアップタスクでファイル操作をする際の日付はファイルの更新日付が検索対象となります。

Get-Item "C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLLogMaintenancePlan_Subplan_1_20101220221815.txt" | Set-ItemProperty -name LastWriteTime -Value (Get-Date).AddDays(-28)

これで、メンテナンスレポートの日付が 28 日前 (4 週間前) になりました。
image

それでは、作成したクリーンアップタスクんのジョブを実行してみます。
ジョブを実行したことで、先ほどファイルの更新日付を変更したメンテナンスレポートが削除されていることが確認できます。
image

バックアップ ファイルに関しても更新日付が条件に一致するのであれば削除されます。
image
image

メンテナンス プラン ウィザードを使用することでバックアップのメンテナンスプランを GUI のウィザードベースで柔軟に設定することが可能となります。

今回の投稿では設定をしなかったのですが、メンテナンスプランには [データベースの整合性確認] というメンテナンス タスクがあります。

セミナーの中ではこのタスクについてもお話がありました。

次の投稿では、データベースの整合性確認についてまとめてみたいと思います。

Share

Written by Masayuki.Ozawa

12月 20th, 2010 at 9:52 pm

3 Responses to '第 1 回 Get The Fact セミナーの振り返り その 5'

Subscribe to comments with RSS or TrackBack to '第 1 回 Get The Fact セミナーの振り返り その 5'.

  1. いつも大変お世話になっております。
    小さな会社で,最近AccesからSQLServerへ移行し始めたSQLServer超初心者です。
    書籍やネット検索でバックアップを勉強している最中で,
    検索するといつもこちらのサイトに会います。
    プリントアウトして熟読させていただいております。
    ひとつ質問させていただきたいのですが,
    こちらの記事の
    「これで、メンテナンスレポートの日付が 28 日前 (4 週間前) になりました。 」
    の画面の下にある画像で,Log_○○という名前の,SQLServerProfilerファイルがありますが,これはなんですか?
    私もメンテナンスプランを動かし始めたら,1日一度このファイルが出来てしまってなんだろうと思っています。ファイルは一つ20メガほどです。
    ずうずうしくて恐縮ですが教えていただけたら嬉しいです。
    宜しくお願いいたします。

    masunona

    21 10月 16 at 09:44

  2. デフォルトトレースという既定で取得されているトレースのことかと思います。
    デフォルトトレースに関しては、以下を参照していただくのがよろしいかと思いますので、こちらを一読いただければ。
    https://msdn.microsoft.com/ja-jp/library/ms175513.aspx
    https://blogs.msdn.microsoft.com/jpsql/2012/05/07/sql-troubleshooting-4-1/

    masayuki.ozawa

    21 10月 16 at 22:40

  3. ありがとうございます!お返事いただけて感激です。
    >デフォルトトレース
    という言葉をはじめて知りました!
    記事拝読いたします。
    今後とも参考にさせていただきます。
    よろしくお願いいたします!

    masunona

    25 10月 16 at 09:53

Leave a Reply