データベースサーバー管理者は、とにかくバックアップに日夜神経を注いでください。

SQL Serverには、オンラインバックアップ機能があります。クライアントアプリケーションがデータベースを使用している間でも、データベースのバックアップを実行することができます。バックアップ作業中にクライアント側アプリケーションに影響を与えない負荷程度であれば、バックアップ計画に従って、バックアップをどんどん実行してください。

バックアップの基本は、データベースのデータファイルとトランザクションログファイルを全部バックアップする、全体バックアップです。

注意するのは、全体バックアップで得られる1個のバックアップファイルの大きさと、現在のデータベースのファイルサイズ（データファイルとトランザクションログファイルの合計量）は、異なります。ですからファイルサイズの違いが大きくても、異常ではありません。

全体バックアップは、

BACKUP DATABASE データベース名

TO 書き込み先デバイス名

という書式です。

ハードディスクなどの物理ファイルを直接指定するときは、

BACKUP DATABASE データベース名

TO DISK =’ファイル名’

の書式です。

ファイル名は、データベースサーバーから見たファイル名です。特に、ドライブ名などを指定するときは、注意しましょう。

またファイル名にUNC (Uniform Naming Convention) 名を指定することができます。セキュリティ権限が適切に設定されておれば、ネットワーク内のファイルサーバーの中に、データベースのバックアップファイルを作成することができます。

複数箇所に、バックアップファイルを作成することが大事です。ハードウェア障害などによって、万が一、バックアップファイルが破損したら大変です。バックアップ命令によって直接作成されたバックアップファイルを1次格納ファイルとすれば、それと同じものをデータベースサーバーの中にある別の増設ハードディスクの中に、1次格納ファイルと同じものをコピーします。これが2次格納ファイルです。さらに、ファイルサーバー上に3次格納ファイルを作ると良いでしょう。また、ファイルサーバー自身のバックアップによって4次格納ファイルが作成されます。

重要なデータベースであれば、1次から4次ぐらいまで、4個のファイルが自動的に作成されコピーされる仕組みを作ると安心できると思います。

SQL Serverには、xp_cmdshellシステムストアドプロシージャが組み込まれております。セキュリティ上の脅威から、このストアドプロシージャの実行に制限を掛けたり、システムから削除したり、対策を講じているデータベースサーバーもあります。しかし中小企業が使うデータベースサーバーであれば、データベースのバックアップを実行する方のセキュリティ権限は、データベースサーバー管理者（sysadmin）だと思います。また、xp_cmdshellストアドプロシージャのセキュリティ上の脅威をあまり考慮しなくても良いでしょう。そうであれば、バックアップ手法の中に、積極的に、xp_cmdshellストアドプロシージャを活用してください。

データベースのバックアップファイルの作成で、最初の1次格納ファイルは、BACKUP DATABASE文によって作成しますが、2次以降のファイルは、xp_cmdshellストアドプロシージャから起動されるファイルコピー命令などによって作りましょう。OSが持っているコマンドシェルのコマンドを実行することができますので、バックアップ作業に役立つ命令がたくさんあります。また自作プログラムを呼び出して実行することも可能です。

データベースの全体バックアップに要する時間が十分に待てる範囲だったり、また、全体バックアップ作業中のデータベースサーバーの負荷がクライアントアプリケーションに大きな影響を与えないのであれば、差分バックアップ手法を積極的に使う必要はありません。全体バックアップ手法でバックアップ戦略を構築すれば良いでしょう。

ところが、世の中には、全体バックアップ作業を実行すると、バックアップに何十時間も何日も要するような巨大なデータベースが存在します。このようなデータベースでは、全体バックアップ命令は、気軽に実行することは許されません。事実上、全体バックアップの実行ができない状態のデータベースです。

このような巨大なデータベースのバックアップ手法に対して、SQL Serverでは、差分バックアップという技術を提供します。

この差分バックアップは、最初に全体バックアップを実行する必要がありますので、巨大なデータベースでも1度は全体バックアップを実施してください。

この全体バックアップを実行したときのデータベースの状況を覚えておき、差分バックアップでは、データベースのデータが変更された部分を探して、その部分だけのバックアップを実行します。

データの変更箇所が少なければ、差分バックアップは速やかに実行できます。また、差分バックアップで得られるバックアップファイルも小さくなります。

このような差分バックアップの利点も、時間の経過とともに、無くなるので注意が必要です。それは、SQL Serverの差分バックアップは、全体バックアップを実行したときを基準にしているからです。

例えば、午後0時に、データベースの全体バックアップを実行しました。そしてデータベースのデータを変更して、午後3時に、差分バックアップを実行します。この差分バックアップファイルの中には、全体バックアップを実行してからの変更部分のデータ、すなわち午後0時から午後3時までに変更された部分のデータが記録されます。差分バックアップを実行してから再びデータベースのデータを変更して、午後6時に、2度目の差分バックアップを実行します。問題は、この2度目の差分バックアップに含まれるデータです。

差分というイメージから想像すると、午後3時に差分バックアップを実行しているので、午後6時の差分バックアップは、午後3時から午後6時までの間に変更されたデータだけをバックアップするように思います。ところがSQL Serverの差分バックアップは、あくまでも全体バックアップを基準にしており、午後6時の差分バックアップは、午後0時の全体バックアップからの変更部分のすべて、つまり、午後0時から午後6時までの変更部分のバックアップとなります。つまりこの中には午後3時の差分バックアップの効果が含まれます。

従って時間の経過とともに、差分バックアップで作成されるバックアップファイルもだんだん大きくなり、バックアップに要する時間も大きくなります。ですから、頃合いを見て、全体バックアップを実行して、差分バックアップの基点を更新する必要があります。

差分バックアップ手法を取るときは、前回の図5の中で、

「データベース − 差分」

を選択してください。BACKUP DATABASE文であれば、DIFFERENTIALオプションをWITH句の中に指定してください。

3.1　トランザクションログファイルとは？

トランザクションログを有効に働かせるためには、前回解説したデータベースの復旧モデルで、シンプルモード以外のデータベースオプションを設定しておく必要があります（できれば、フルモードが良い）。

トランザクションログファイルの内容を簡単に説明すると、データベースのデータを更新した理由を記述したものです。

ある時間に、あるテーブルの、あるレコードのある列のデータを、AからBに変更しました

このようなデータの更新履歴情報が、トランザクションログファイルの中に記録されます。実際はもっともっと複雑な情報としてトランザクションログファイルの中に記録されますが、私達がイメージするのは、この程度でよいと思います。

3.2トランザクションログファイルの管理とは？

データ更新が多いデータベースであれば、データ更新作業に伴ってトランザクションログファイルの中に記録される「更新理由の記述」もどんどん増えて行きます。極端なことを言えば

Update TBL

SET COL = COL

WHERE ROW=Pkey

このような実質的には何も値の変更が行われないUpdate文を、仮に100万回実行したとしても、データファイルの大きさには変化がありません。それに対してトランザクションログファイルの方には、たとえデータの値に変化が無くても、更新を行ったという事実を記録しないわけにはいきません。記録を省略することはできないので、ですから、100万回の命令を記録することになります。

この例からもわかるように、トランザクションログファイルは膨大な大きさに成長します。そこで頃合を見て、大きくなったトランザクションログファイルを小さくしたりする作業をしなければいけません。これが、トランザクションログ管理と呼ばれます。

このような管理作業を技術的な問題などでできない場合は、データベースの復旧モデルのオプションを「シンプル」にしてください。シンプルモードにすると、トランザクションログファイルの内容を定期的に破棄します。

データファイルが壊れたときに実感するトランザクションログファイルの大きな役目

データベースのバックアップ戦略は全体バックアップ方式を採用しているので、トランザクションログファイルのバックアップの重要性をあまり理解していない方も見受けられます。確かに、トランザクションログファイルのバックアップが役立った！という体験は、あまりして欲しくないのですが、データベース管理者としては、トランザクションログのバックアップの役目を理解しておくことは重要です。

例えば午後0時に、データベースの全体バックアップを実行しました。

その後、データベースのデータを変更していたのですが、午後6時に、突然にデータベースが壊れて使えなくなったとします。

このような状況を想定したときに、誰でも考えるのは、午後0時の全体バックアップを復元するということです。しかしその復元されたデータベースでは、午後0時から午後6時までの間に変更されたデータは反映されておらず、業務に支障がでることが考えられます。データベース管理者としては、データベースが壊れる直前の午後6時の状態に復元しなければいけません。また、データベースサーバーはそのような高信頼性に応える義務があります。それを果たしてくれるのがトランザクションログファイルです。

トランザクションログファイルが破損を受けていなければ（これが大前提です！）、その中には全体バックアップを実施した午後0時からデータファイルが壊れる直前の午後6時までの、データを更新した履歴情報が記録されているはずです。

ですからこの履歴情報をバックアップして（トランザクションログのバックアップ）、その後に、午後0時の全体バックアップを復元して、さらに、バックアップしたトランザクションログファイルの更新履歴の内容を復元されたデータベースに適用して行けば、データベースが壊れる直前の午後6時の状態に、データベースを復元させることができます。

このように、バックアップ戦略にどのような方法を採用していても、データベースが壊れたときの最初に実行する命令は、トランザクションログファイルのバックアップ命令になります。ですからデータベース管理者としては、トランザクションログファイルのバックアップ命令に慣れておく必要があります。

【注意】

データベースが壊れた直後のトランザクションログファイルのバックアップ命令では、NO_TRUNCATEオプションを付けてバックアップ命令を実行してください。

BACKUP LOG命令

SQL文からトランザクションログファイルをバックアップするときは、

BACKUP LOG データベース名

TO バックアップデバイス名

WITH オプション

の書式です。

直接物理ファイルを指定するときは、

BACKUP LOG データベース名

TO DISK = ‘物理ファイル名’

WITH オプション

の書式です。

トランザクションログファイルのバックアップを実行すると、バックアップされた履歴情報部分には、上書き再利用可の印が付けられます。この印を付けることを、「ログの切捨て」と呼びます。切り捨てられた部分に、新しいログが上書きされるので、トランザクションログファイルの成長（自動拡張）が抑えられる効果があります。

一方、WITH NO_TRUNCATEオプションを付けてバックアップを実行すると、「ログの切捨て」は実行されません。またデータファイルが壊れた状態では、「ログの切捨て」操作を行うことはできないので、データベースが壊れた直後に最初に実行するトランザクションログのバックアップ命令では、必ず、WITH NO_TRUNCATEオプションが必要となります。

Enterprise Manager上からのログのバックアップ操作

Enterprise Manager上からログのバックアップ操作を行うためには、

図1で、

図1：トランザクションログのバックアップの指定

トランザクションログ

のバックアップを選択してください

図2で、

図2：バックアップオプションの指定

アクティブでないエントリをトランザクションログから削除

のオプションを、設定または解除してください。この削除が、「ログの切捨て」に相当します。「ログの切捨て」を実行するときは、このオプションを指定してください。

全体バックアップ戦略の中にトランザクションログファイルのバックアップはどのように組み入れるか？

中小規模のデータベースのバックアップ戦略では、全体バックアップ方式で十分な場合があります。このようなバックアップ戦略の中で、トランザクションログのバックアップをどのように取り扱ったらよいでしょうか？

もちろん、データベースが壊れた直後には必ずトランザクションログのバックアップが必要になりますが、通常時の話です。

1つの基準として、過去のある一時点でのデータベースのデータが欲しいという要望があるかどうかです。

データベースの運用中に、ある部署から、過去の何月何日のこのデータの値を調べて欲しいというような、データベース管理者としては辛い面倒な作業を強いられるかどうかです。

トランザクションログのバックアップファイルを保存しておけば、このような要望にも応えることができます。実はトランザクションログを使ったデータベースの復元作業のオプションの中に、指定された日時に復元操作を中断させる機能が用意されています。この機能を使うときには、トランザクションログのバックアップファイルを継続的に管理しなければいけません。

一方、このような過去の一時点のデータベースを復元するような要求が出ないのであれば、トランザクションログのバックアップをバックアップ戦略から外してもよいでしょう。

ただこのような判断は、データベースサーバー管理者の一存では決めることはできません。上司や経営者などの方と相談する必要があります。

また管理するにしても、現実的には、バックアップテープ装置やテープメディアのランニングコスト等の費用が発生します。ですから永遠に管理するのではなく、過去1ヶ月や3ヶ月、長くても半年や1年を上限に、考えておくとよいでしょう。

トランザクションログの切捨て

トランザクションログファイルのバックアップを実行すると、ログの切捨てが行われます。しかしトランザクションログ管理が不要な全体バックアップ戦略を採用している場合は、「ログの切捨て」だけを行いたい希望があります。

トランザクションログのバックアップを実行せずに、ログだけを切り捨てたい。

そのような要望を実行するSQL文が、

BACKUP LOG データベース名

WITH TRUNCATE_ONLY

の命令です。

この命令を実行すれば、「ログの切捨て」が即座に実行されます。

トランザクションログの圧縮

「ログを切捨て」した後に、大きく成長したトランザクションログファイルを元の小さいファイルサイズにしたいときは、ファイルの圧縮操作を行います。

図3：データベースの圧縮操作を行う

Enterprise Managerから図3のように

　　　　　　　データベースの圧縮

を選びます。

次の画面の図4で、圧縮する単位をファイルにします。

図4：ファイルの圧縮の指定

次の画面の図5で、圧縮するファイル名（論理ファイル名）をトランザクションログにしてください。OKボタンを押すとファイルが圧縮され小さくなります。

図5：トランザクションログの論理ファイル名を選択する

SQL文から圧縮するときは、

DBCC SHRINKFILE(‘論理ファイル名’)

によって、圧縮操作が行われます。