Craig Mullinの本、データベース管理の冒頭の章で、彼は”多くの点で、今日のビジネスはデータです”と言います。 ほとんどの組織では、データを保護する責任者はデータベース管理者…あなたです。

そうだ; 全体のビジネスはあなたの有能な手にあり、あなたの上司のおかげで”sa”権限を持っているその新しい生協の有能な支援を受けて、初めて、毎回、完璧なコー

泣くのはやめて あなたの注意の下でSQL Serverのデータを保護するためにできることがあり、最も重要なのは、定期的なデータベースバックアップを実行していることです。

バックアップ

Microsoftは、SQL Server Books Onlineでバックアップを次のように定義しています:

システム障害後にデータを復元および回復するために使用されるデータのコピー

SQLバックアップは、さまざまな方法で作成でき、データのすべてまたは一部 この記事では2005年の構文に焦点を当てていますが、ほとんどの概念は2000年に適用できます。 これは巨大な話題です。 せいぜい、私は表面を傷つけ、あなたが再び泣き始めることはありませんので、あなたに十分な情報を与えるつもりです。 これを読んだ後、あなたはあなたのシステムのためのバックアップの合理的なセットを設定することができるはずです。

復旧モデル

バックアップの作業を開始するには、ビジネスでデータベース復旧モデルを定義する必要があります。 本質的には、復旧モデルは、トランザクションログデータで何をするかを定義します。

復旧モデルには、フル、シンプル、一括ログの3つがあります。 これらは非常に簡単に定義できます:

• 単純-単純復旧モードでは、トランザクションログはバックアップされないため、最新の完全バックアップまたは差分バックアップにのみ復元できま
• Full–完全復旧モードでは、データベースとトランザクションログをバックアップして、任意の時点にデータベースを復旧できるようにします。
• Bulk Logged–bulk loggedモードでは、ほとんどのトランザクションはトランザクションログに格納されますが、一括読み込みやインデックス作成などの一部の一括操

最も一般的に使用される二つのモードは、シンプルでフルです。 もちろん、データを保護するために常に完全復旧を使用する必要があるとは限りません。 それはビジネス上の決定です。 ビジネスでは、ある時点まで回復する必要があるかどうか、または最後の完全バックアップが必要なだけかどうかを教えてくれます。 手動入力などの他の手段でデータを回復できるかどうか、または可能な限り保護する必要があるかどうかを定義します。 最後の完全バックアップまたは差分バックアップ以降に保存されたデータを失う余裕がある場合、または特定の時点までの回復が必要ない場合は、Simple recoveryを使用します。 簡易モードでは、プライマリを復元するときに、すべてのセカンダリ読み取り/書き込みファイルグループを復元する必要があります。 ほとんどの場合、企業やレポートシステムの絶対的な重要な部分ではないセカンダリデータベースでSimpleを使用し、読み取り専用アクセス権を使用するた フルを使用するデータのすべてのビットが重要である場合は、特定の時点に回復する必要がありますまたは、通常は非常に大規模なデータベース（VLDB）の場

単純復旧モデルと完全復旧モデルの両方で、データベースをバックアップファイルにコピーすることができますが、ログ、差分バックアップスケジュール、ま この記事では、これらのトピックのできるだけ多くをドリルダウンしようとしますが、ファイルとファイルグループはドリルダウンしません。

単純回復での作業

十分な話。 バックアップを実行することに取り掛かろう。 小規模から中規模のデータベースで単純なリカバリを行っていると仮定しましょう。 すべてのサンプルスクリプトにAdventureWorksを使用します。 簡単な回復に設定するには:

最も簡単なバックアップ方法は、次のSQL Server backupコマンドを定期的に実行し、データベースの完全バックアップを実行することです:

あなたが尋ねるすべてのタイピングは何ですか？ 私たちは私たちのために仕事を処理するためのGUIツールを持っていませんか？ はい、ほとんどの単純なバックアップはSQL Server Management Studioを使用して実行できます。 しかし、Management Studioがあなたのために何をしているのかを学び、理解したい場合、またはバックアップされているもの、方法、場所を細かく制御したい場合は、

上記のコマンドは、ディスクへの基本的なバックアップを沈殿させます。 私が知っているほとんどのDbaは、ファイルにバックアップしてから、テープや他のメディアにファイルをこすります。 メディアは時々痛みのビットをすることができますが、ディスク上のファイルは、回復が簡単かつ迅速であるためです。 たとえば、通常、ファイルシステムには2〜3日分のバックアップがあり、すぐに回復できます。 古いバックアップの復元を実行する必要がある場合にのみ、テープシステムに移動します。

そのコマンドは何をしたのですか？ データベース内のすべてのコミットされたデータのコピーを作成しました。 また、コミットされていないログエントリもコピーしました。 これらは、バックアップ処理中にデータに発生した変更をコミットまたはロールバックするために、リカバリ中に使用されます。

コピーのみのバックアップ

通常、データベースのバックアップは他のバックアップおよび復元プロセスに影響します。 たとえば、前のコマンドを実行した後、差分バックアップ(最後のバックアップ以降に変更されたデータのみをコピーするバックアップ)は、昨夜実行したバ 前述したように、SQL2005では、バックアップに新しい概念COPY_ONLY backupsが導入されています。:

すでに私たちは、Management Studioがあなたを助けないだろうそれらのより粒状の瞬間の一つを発見しました。 コピーのみのバックアップが必要な場合は、コマンドラインを使用する必要があります。

差分バックアップ

しばらくの間、私たちはまだ単純な回復にいると仮定しましょうが、サイズが100GBを超える大きなデータベースを扱っています。 完全バックアップは、実際にはプロセスを少し遅くするために開始することができます。 代わりに、ビジネスとの協議の後、毎週の完全バックアップと毎日の差分バックアップを行うことにしました。 差分バックアップは、最後の完全バックアップ以降に変更されたデータページのみをバックアップします。 差分バックアップを実行するためのSQL backupコマンドを次に示します:

ここで、このデータベースを復元する必要がある場合は、最初に最後の完全バックアップに移動し、それを復元してから、差分バックアップを順番に復元

私はここで詳述することはありません他のバックアップオプションの数があります。 BLOCKSIZE、EXPIREDATE、RETAINDAYS、PASSWORD、NAME、STATSなどの詳細については、オンラインで本を読んでください。

データベースバックアップの整合性をチェックするステートメントを実行することもできます。 バックアップ内のデータの整合性はチェックされませんが、バックアップが正しくフォーマットされ、アクセス可能であることが確認されます。

完全復旧とログバックアップ

私たちは主に単純復旧モードのデータベースに取り組んできました（これは以前はTruncate Log on Checkpointと呼ばれていました）。 このモードでは、後で回復するためにトランザクションログをバックアップしません。 このメカニズムの下にあるすべてのバックアップは、データベースのバックアップです。 ログのバックアップは、単純にはできません。

ただし、最後の正常なバックアップの時点では、完全または差分のいずれかのデータのみを保護しています。 私たちの仮定を変えてみましょう。 今、私たちは、大規模な、ミッションクリティカルなアプリケーションとデータベースを扱っています。 私たちは、このデータベースを最新の分まで回復できるようにしたいと考えています。 これは非常に重要なポイントです。 理論的には、ログエントリは保存およびバックアップされているため、障害が発生した時点まで保護されています。 ただし、いくつかの障害はログの破損を引き起こし、ある時点までの回復を不可能にする可能性があります。 したがって、ログバックアップの間の合理的な最小時間を決定する必要があります。 この場合、我々は失われたデータの価値がこれ以上15分以上で生きることができます。

では、データベースを完全復旧モードにすることから始めましょう:

次に、この場合は15分ごとにスケジュールされたベースで、トランザクションログに対してSQL backupコマンドを実行します:

このスクリプトは、コミットされたトランザクションをトランザクショ これは、開始時刻と停止時刻を示すファイル内のマーカーを持っています。 ログが正常に完了するとログが切り捨てられ、バックアップファイルに書き込まれたコミットされたトランザクションがトランザクションログか 必要に応じて、WITH NO_TRUNCATEステートメントを使用して、データベースの状態に関係なく、トランザクションログからデータをキャプチャできます。 これは緊急事態のみのためのものです。

この場合、INITステートメントは使用しませんが、選択すれば使用できます。 ログバックアップを実行するときには、次のオプションがあります:

1. すべてのバックアップを単一のファイルに実行し、そこにスタックし、復元時に（後で説明します）行う必要があるのは、それらを循環させることです。
2. バックアップに一意の名前を付け、おそらく文字列の日付と時刻を使用します。

後者の場合、safetyはINITを使用すると、どこでバックアップされるかを最大限に制御しているため、バックアップが何であるか、いつ取得されたか、名前に基づ これは、コマンドラインからバックアップを操作すると、GUIよりも詳細な制御を提供するさらに別の場所です。 私たちは、異なる理由のために私たちのシステムで両方のアプローチを使用してきました。 あなたの技術とビジネス要件に最適なものを決定することができます。

データベースバックアップで使用可能なオプションのほとんどは、COPY_ONLYを含むログバックアップに含まれています。 これにより、ログや次回のスケジュールされたログバックアップに影響を与えずに、一連のトランザクションデータをキャプチャできます。 これは、トラブルシューティングなどのために生産データを別のシステムに取り込むのに便利です。

データベースを完全復旧に設定している場合は、ログバックアップを実行する必要があります。 時には、人々は忘れて、トランザクションログは、それがディスクドライブをいっぱいにする点に成長します。 この場合、次のように実行できます:

ログバックアップにNO_LOGをアタッチし、ログの場所を指定しないと、ログの非アクティブな部分が削除され、ログエントリ自体がなくてもこれが行われ、完全なディスクドライブが破壊されます。 これは、データベースを特定の時点まで回復する一連のログバックアップであるログチェーンを中断するため、絶対にお勧めしません。 Microsoftでは、このステートメントを使用した直後に完全バックアップを実行することをお勧めします。 さらに、彼らはこの声明が将来のリリースで廃止される可能性があることを警告しています。

データベースの復元

SQL Serverのバックアップと同じくらい重要であり、データベースを復元する機能がなければ役に立たないことが不可欠です。

データベースの完全バックアップの復元

データベースの完全バックアップの復元は、作成するのと同じくらい簡単です:

それは本当に簡単です–私たちがバックアップデバイスであるかのようにすべてをファイルにバックアップしていない限り。 その場合は、アクセスしている「デバイス」内のファイルを指定する必要があります。 どのファイルがわからない場合は、リストを生成する必要があります:

これにより、上記のManagement Studioから示したのと同じリストが表示されます。 したがって、グループ内の2番目のファイルであるCOPY_ONLYバックアップを復元する場合は、次のコマンドを発行します:

残念ながら、あなたがフォローしている場合は、このエラーを生成したばかりであることがあります:

これが意味するのは、データベースが完全復旧モードになっているが、最後にバックアップを実行してから入力されたトランザクションを意味する”ログ 前の構文を次のように変更すると、この要件を上書きできます:

with句を積み重ねたのはこれが初めてです（WITH FILE=2そしてWITH REPLACEはWITH FILE=2、REPLACEとして表されます）が、最後ではありません。 オンラインで本を読んでください。 ほとんどのWITH句文は、他の文と組み合わせて使用できます。

元のデータベースとは異なるデータベースに復元したい場合はどうなりますか？ たとえば、別のバックアップからデータベースのコピーを作成したいとします。 たぶん、データベースの本番コピーとは別に、本番サポートサーバーに移動して、いくつかの作業を行いたいと思うかもしれません。 単純なアプローチを取る場合は、これを試してみてください:

この場合、上書きされていないファイルに関連する一連のエラー全体が表示されるはずです。 実際にはバックアップから新しいデータベースを作成できますが、既存のデータベースを持つサーバー上でデータベースを作成する場合は、論理名を使用して物理 特定のデータベースのファイルの論理名を知るには、ファイルを移動しようとする前にこれを実行します:

これは、このスクリプトを生成するために適切な論理名を識別するために使用することができます:

差分バックアップの復元

最後の方法は、差分バックアップを適用することです。 これには2つのステップが必要です。 まず、データベースを復元しますが、ひねりを加えてから、差分バックアップを適用します:

これのほとんどは、我々はすでにカバーしてきたものに基づいて、おそらく自明です。 一つのしわは、NORECOVERYキーワードを含めることです。 簡単に言えば、復元中に、バックアップ処理中にトランザクションが開始されている可能性があります。 復元の最後に、完了したトランザクションはデータベースにロールフォワードされ、不完全なトランザクションはロールバックされます。 NORECOVERYを設定すると、トランザクションが開いたままになります。 これにより、次のトランザクションのセットを次のバックアップから順番に取得できます。

この記事では主に単純なバックアップと復元を扱っていますが、2005年のより高度な復元では、データベースがオンラインである間にセカンダリファイルグ そのプライマリファイルグループは、操作中にオンラインである必要があります。 これは、非常に大規模なデータベースシステムのためのより便利になります。

SQL Serverデータベースをある時点に復元する

ログを復元することは、先ほど完了したデータベースの差分復元よりもはるかに難しいことではありません。 時間の瞬間に復元することにはかなり多くの関与があります。 ログを単一のファイルまたはデバイスにバックアップしていると仮定します:

それ以外の場合は、単に移動して必要なファイル名を取得します。 最初にデータベースの復元を実行し、復元されていない状態のままにするように注意します。 これに続いて、ある時点までの一連のログ復元を実行します。

今私たちが持っているのは、10月23日の14:30:29の正確な最後にコミットされたトランザクションまでのデータベースです。 このような複数ステップの復元では、データベースを回復状態のままにする必要があります。 これは、復元プロセスが完了するまで、各ステートメントにNORECOVERYを追加することを意味します。 何らかの理由ですべての文にNORECOVERYを追加した場合、または途中で停止してデータベースをオンラインに戻したい場合は、この文を使用してプロセスを完了:

データベーススナップショット

SQL Server2005では、スナップショット、またはデータベースの読み取り専用の静的ビューの概念が導入されました。 スナップショットは、主に、レポートの目的でデータベースの読み取り専用バージョンを提供するために作成されます。 ただし、それらはバックアップと同様の方法で機能します。 主な違いの1つは、コミットされていないすべてのトランザクションがロールバックされることです。 ロールフォワード、ログのキャプチャなどのオプションはありません。 を使用しています。また、多くのSQL Serverリソースがまったく使用されていません。 むしろ、ディスク技術は、データのコピーを作成するために使用されます。 このため、作成と復元の両方でバックアップよりもはるかに高速です。

注:
SQL2005Snapshotの詳細については、http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/を参照してください。

スナップショットは、レポートに加えて、すべてのアクティブなユーザー(およびそのトランザクション)をシステムから削除した後に、メンテナンス前に作成することをお勧めします。 スナップショットは、ライブバックアップのボラティリティをサポートしていませんが、その速度と回復の容易さは、しくじったロールアウトからの迅速な回復のための素晴らしいツールになります。 スナップショットはサーバーに保存されるため、十分なストレージがあることを確認する必要があります。

データベースをバックアップするのではなく、新しいデータベースを作成するため、構文が異なります:

これで、読み取り専用アクセスでアクセスできるようになります。 主にこれをバックアップメカニズムとして使用することに関心があるので、データベースをデータベーススナップショットに戻す方法を含めてみましょう。

まず、使用するスナップショットを特定します。 元に戻すデータベースに複数のデータベースがある場合は、使用しているデータベースを除くすべてを削除する必要があります:

次に、RESTORE文を実行してデータベースを元に戻すことができます（メタファーが混在していますが、良くありません):

それだけです。 私のシステムでは、Adventureworksのデータベーススナップショットの実行には136ミリ秒かかりました。完全バックアップには5,670ミリ秒かかりました。スナップショットの復元には905ミリ秒かかり、データベースの復元には13,382ミリ秒かかりました。 これを本番環境のロールアウトプロセスに組み込むと、

という大きな利点が得られる可能性がありますが、スナップショットを使用するにはいくつかの注意点があることに注意する価値があります。 データベースの2番目のコピーのために十分なディスク領域が必要です。 ほとんどの構文はデータベース自体で使用される構文と似ているため、スナップショットを扱うには注意する必要があります。 最後に、データベースに添付されているスナップショットがある間は、そのデータベースのデータベースバックアップから復元を実行することはできません。

ベストプラクティス

データベースのバックアップを実行する方法は、技術的な決定ではありません。 それはビジネスによって指示されるべきです。 トランザクションレートが低い小規模システムや定期的にロードされるレポートシステムでは、データベースの完全バックアップのみが必要になります。 中規模システムと大規模システムは、必要なバックアップの種類を決定するために管理されるデータの種類に依存します。

中規模のシステムでは、日中のログバックアップを含む毎日のバックアップは、おそらくほとんどのデータ要件に適時に答えます。

大規模なデータベースの場合、最小時間で最大の回復可能性を確保するために、バックアップを混在させて一致させるのが最善の方法です。 たとえば、毎週の完全バックアップを実行します。 週の間に一日二回、差分バックアップを実行します。 日中は10分ごとに、ログバックアップを実行します。 これにより、多数の回復メカニズムが提供されます。

非常に大規模なデータベースの場合は、完全バックアップまたは完全なデータベースの差分バックアップを実行できない可能性があるため、ファイルグルー この分野で役立つ多くの追加機能が利用可能ですが、ここではそれらには触れません。

バックアップと復元を実行するためのスクリプトを開発するのに時間がかかるはずです。 命名規則は、どのデータベースから、どのサーバーから、どの日付から、どの特定のバックアップと形式があなたの正気に非常に役立つかを知っています。 バックアップの共通の場所、ログ、完全または増分を定義する必要があります。 すべての責任者は、バックアップと回復の両方と同じトラブルシューティングの訓練を受けている必要があります。 これを行うには多くの方法がありますが、PopバックアップとPop復元でいくつかの提案を見つけることができます。

実際のテストは、バックアップメカニズムを実行してから復元を実行することです。 その後、復元の異なるタイプ、および別の、および別のを試してみてください。 システムをバックアップする方法を定義する際にデューデリジェンスを行っただけでなく、それらのバックアップを回復できるようにするための余分なステップを行ったことを確認してください。 あなたがこれを練習し、練習を文書化してから文書をテストしていない場合、実際には、あなたは災害の準備ができていません。

要約

企業内のバックアップは、シカゴでの投票のようなものでなければなりません。 基本的なバックアップの設定は非常に簡単です。 ログのバックアップと差分を追加するのも簡単です。 ファイルとファイルグループのバックアップと復元を追加して、バックアップの速度を上げ、復元する方法を確認するオプションを探索して、システムの可用性とアップタイムを向上させます。 共通の命名基準を維持します。 スナップショットを使用するときは注意してくださいが、確かにそれを採用してください。 サーバー間の標準的な場所にファイルを保存します。 あなたの回復を練習します。 最後に、本当にあなたのバックアップを歌うために、Red GateのSQL Backupâ¢の無料トライアルをダウンロードしてくださ それは高性能圧縮およびネットワークの復元を薄片状ネットワークを渡るバックアップを書くか、またはコピーするプロセスを耐障害性にするために