Microsoft SQL Serverのバックアップについて聞いて恥ずかしかったこと

バックアップとSQL Serverデータベースの復元についてプレゼンテーションを行う場合、通常2種類の質問がされます。 最初のものは聴衆からのプレゼンテーション中に設定され、2番目のものはプライベート会話で既に設定されています。 これらの「プライベート」な質問はより興味深い場合が多く、バックアップを作成する方法、バックアップを作成する理由、またはバックアップする理由について別の記事を書くのではなく、最も複雑で興味深い質問に答えようとします。バックアップを確認します（ただし、バックアップを確認する必要があります）。

バックアップが作成されたバージョン以外のバージョンのSQL Serverにバックアップを展開できますか？ どのような問題が発生する可能性がありますか？

バックアップを別のバージョンのSQL Serverに復元できますが、バックアップを展開するSQL Serverのバージョンが、バックアップを作成したバージョンよりも新しい場合のみです。 つまり、SQL Server 2000が作成したバックアップをSQL Server 2005に、SQL Server 2005をSQL Server 2008 R2に、またはSQL Server 2008からSQL Server 2012に展開できますが、これを逆方向に行うことはできません。 SQL Serverの各バージョンは、データベースとそれを保存するファイルに独自の変更を加えます。 Microsoftは、これらの変更をサポートするために「過去に戻る」ことはなく、SQL Serverの以前のバージョンを書き換えることはありません。 本当に古いバージョンのSQL Serverにアップグレードする必要がある場合は、スキーマとデータ自体のスクリプトを作成する必要があります（たとえば、 この移行に関する記事はこちらです ）



バックアップが作成されたSQL Serverのバージョンを判断するには、バックアップファイルのヘッダーを確認する必要があります。

RESTORE HEADERONLY FROM DISK = 'd:\bu\mm.bak';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

その結果、バックアップが作成されたSQL Serverのインスタンスのメジャーバージョン、マイナーバージョン、およびビルドバージョンが表示されます（下のスクリーンショットを参照）。 これにより、このバックアップを復元するためのSQL Serverの適切なバージョンを決定できます。







データベースを新しいバージョンのSQL Serverに復元すると、このバージョンのSQL Serverと互換性のないものがあることが判明する場合があります。 SQL Serverの新しいバージョンにアップグレードする最も安全な方法は、転送するベースでMicrosoft Upgrade Advisor（SQL Serverの各バージョンで利用可能な無料のユーティリティ）を起動し、準備が整っていることを確認してから、新しいインスタンスでバックアップと復元を行うことです（ただし、最初にバックアップを転送してからアシスタントを開始するのではなく、この順序でのみ）。



回復後、データベースは移行元のSQL Serverのバージョンとの互換モードになります。 これは、バックアップが作成されたSQL Serverのバージョンでサポートされていた機能のみが利用できることを意味します。 SQL Serverの新しいバージョンのすべての利点を得るには、データベースの互換性レベルを変更する必要があります。 これは、GUIを使用して実行するか、スクリプトを使用できます。

 ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 110;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

異なる番号は、SQL Serverの異なるバージョンを示します。SQLServer 2005の場合は90、SQL Server 2008および2008 R2の場合は100、SQL Server 2012の場合は110です（ SQL Serverバージョンの詳細については、 こちらをご覧ください -翻訳者のメモ ）。



すべての「遷移」が可能というわけではないことを付け加える価値があります。 SQL Serverでは、2つのバージョンでのみ「ジャンプフォワード」できます。 たとえば、SQL Server 2000によって作成されたバックアップをSQL Server 2012に展開することはできません。最初に、SQL Server 2008に展開し、適切な互換性レベルを確立し、新しいバックアップを作成してから、SQL Server 2012に展開する必要があります。

復元操作を使用してデータベースのコピーを作成できますか？ 何がおかしいのでしょうか？

はい、できます。 バックアップを別のサーバーに展開する場合は、新しいサーバーに「古い」サーバーと同じ論理ドライブがあることを確認するか、WITH MOVEオプションを使用してデータベースファイルの正しいパスを手動で登録する必要があります。データベースの復元：

 RESTORE DATABASE NewDBName FROM DISK = 'c:\bu\mm.bak' WITH MOVE 'OldDB' TO 'c:\data\new_mm.mdf', MOVE 'OldDB_Log' TO 'c:\data\new_mm_log.ldf';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

データベースファイルには、論理名と物理ファイル名の両方があります。 必要なのは、すべての論理ファイル名を登録し、それぞれについて新しい物理的な場所を決定するだけです。



発生する可能性のある主な問題は、データベースの復元先のディスクに空き領域がないことに関連するエラーです。データベースの新しい名前を指定し忘れると、SQL Serverは既存のデータベースの上にデータベースを復元しようとします。



データベースユーザーが新しいサーバーに表示されていないアカウントに関連付けられている場合、データベースを新しいサーバーに復元すると、「孤立ユーザー」問題（ msdnへの翻訳によるとアカウントとの連絡を失ったユーザー-約Translator ）が発生する場合があります。 このエラーを修正する必要があります。

トランザクションログファイルがない場合、MDFファイルをデータベースとして添付できますか？

これが受け入れられる唯一のオプションは、データベース操作が正しく完了した後にトランザクションログが失われた場合です。 いずれにせよ、これは良い考えではありません。 データベースを接続する場合、データベース回復プロセスを実行するには、トランザクションログファイルとデータファイルが必要です（ ここでは、データベース回復はRESTORE DATABASE操作として理解されていませんが、回復はSQL Serverが開始するたびに発生するプロセスであり、SQL Serverは»トランザクションログによると、データファイルを一貫した状態にします-およそTranslator ）。 ただし、トランザクションログファイルなしでデータファイルを添付できる場合もありますが、この機能は、ハードウェアの問題やバックアップがないためにトランザクションログファイルが破損または失われた場合のみを対象としています。 もちろん、データベースはトランザクションログなしでは存在できず、トランザクションログファイルなしでデータベースをアタッチすると、SQL Serverは単にそれを再作成します。



トランザクションログファイルなしでデータファイルを添付すると、ログのチェーンが破壊され、さらに、データベースでトランザクションまたは構造の整合性が侵害されることが判明する場合があります（トランザクションログの「損失」時のデータベースの状態によって異なります）。 このようなデータベースをアタッチする操作は、実行されるアクションの種類に関係なく失敗する場合があります。



データファイルとトランザクションログファイルのコピーは、デタッチ操作が完了した後、またはSQL Serverプロセスが正しく完了した後にのみ許可されます。これにより、すべてのトランザクションが正しく完了します。 データベースファイルを別のサーバーにコピー/移動することは、バックアップを作成/展開するよりも別のサーバーにデータベースを転送するより速い方法ですが、それほど安全ではありません（コピーなしでデータベースファイルを直接移動する場合）。 また、同じバージョンまたは新しいバージョンのSQL Serverのデータベースにしか参加できないことを覚えておく必要があります。

私のDBはSAN上にあります。 SANバックアップで十分だと聞きました。 本当ですか？

それは本当かもしれません。 主なことは、SAN（ ストレージ、ネットワーク/データストレージシステム-約Translator ）がSQL Serverトランザクションをサポートしていることです。 もしそうなら、彼女はデータベースにトランザクションがあり、これらのトランザクションの存在はデータファイルのデータが完全でないかもしれないことを知っているでしょう、なぜならこれらのトランザクションで変更されたデータをハードディスクに書き込むプロセスはバックアップ時に完了していません。 SQL Server自体が行うバックアップでは、当然これらの点が考慮されます。



たとえば、EMC Data Domainは、他のベンダーの製品と同様に、トランザクションサポートを提供するソフトウェアとSANの組み合わせですが、SANのドキュメントを確認する必要があります。 「トランザクションの一貫性」、「トランザクション対応」などのフレーズに注意してください。 それらが見つからなかった場合、すべてのバックアップ要件を満たすのに十分なSANバックアップが必要であると判断する前に、データベースの回復を確認することをお勧めします。 ただし、SANバックアップが正常に実行されていることを確認した後でも、ネイティブSQL Serverバックアップが不要になったわけではありません。 たとえば、ある時点でデータベースを復元する必要がある場合、SQL Serverを使用してトランザクションログのバックアップを作成する必要があります。



通常、バックアップを作成するとき、SQL Server対応のSANはSQL Server VDIインターフェイスを使用し、バックアップ中はデータベースをフリーズします。 このようなバックアップを作成するメカニズムを実行し、SQL Serverエラーログを見ると、IO操作が凍結されているというメッセージが表示されます。



SANによって作成されたバックアップに依存している場合、ライブデータベースまたはSANバックアップから復元されたコピーのいずれかでデータベースの整合性チェックを実行する必要があります。 それ以外の場合は、破損したデータベースのバックアップを長期間作成し、それを知ることさえできません。

Windowsで作成されたデータファイルのコピーをバックアップとして使用できないのはなぜですか？ 任意の時点で回復する機能は必要ありません。

SQL Serverは通常のデスクトップアプリケーションではありません。 彼は、すべてのACIDプロパティ（Atomic、Consistency、Isolated、Durable- もう少し詳細 -約Translator ）が満たされるようにファイルを管理します。 要するに、トランザクションの正常な完了を保証するために、SQL Serverはファイルへのアクセスを誰にも与えないようにし、必要に応じてそれらを変更します。



現在実行中のロックとトランザクションを無視してデータファイルをコピーするだけの場合、後でこのファイルを添付しようとすると、一貫性のない状態になり、エラーが発生します。



データベースが完全に変更されていない場合にのみ、ファイルをコピーして後で添付できます。 ファイルのコピー時に少なくとも1つのトランザクションが開かれた可能性が少なくとも最小限であれば、バックアップが失敗する可能性が高くなります。 バックアップとして使用するデータファイルとトランザクションログをコピーする唯一の安全な方法は、コピーする前にデータベースをオフラインにすることです。



組み込みのSQL Serverエンジンを使用する方がはるかに安全で簡単です。

バックアップを作成します。 このようなバックアップはデータベースの完全なコピーであり、すべてのACIDプロパティが実行されます。

私は非常に小さなデータベースを持っています。 各テーブルをディスクに「アンロード」してバックアップを作成できないのはなぜですか？

SQLCMDのようなものを使用してテーブルを単純なテキストファイルにアップロードできますが、データベースを1つのコマンドだけで復元する代わりに、いくつかのコマンドを実行する必要があります。 最初に、空のデータベースを作成する必要があります。 次に、ファイルから各テーブルを作成してロードする必要があります。 テーブルにIDENTITY列が含まれている場合、これらの各テーブルでSET IDENTITY_INSERTを実行する必要があります。 また、整合性を確保するために、テーブルにデータをロードする順序を慎重に決定する必要があります。



さらに、すべてのテーブルは異なる時間にディスクにアップロードされるため、アンロード中にデータが何らかの形で変更された場合、リカバリ後にデータベースを一貫した状態にできず、エラーを手動で探して修正する必要があります。



もちろん、あなたにはそうする権利があります。 一方、BACKUP DATABASEコマンドを実行するだけで、必要に応じてこのバックアップを復元できます。

SQL Server自体がこれを行うことができるのに、なぜバックアップユーティリティにお金を払うのですか？

サードパーティのバックアッププログラムを使用する主な理由は、リーダーシップ、自動化、および機能の3つです。 あなたが初心者のデータベース管理者であるか、データベース管理者ではないが、メインジョブの追加としてDBMSを維持することを余儀なくされている場合、SQL Serverでバックアップを構成する方法、場所、および理由がわからない場合があります。 優れたユーティリティ（SQL Backup Proなど）は、バックアップを使用してデータベースの安全性を確保するために必要な種類のマニュアルを提供します。



SQL Server自体によって作成されたバックアップは正常に機能しますが、それらを構成するために、さらに自動化するためにさらに多くの作業を行う必要があります。 優れたサードパーティのユーティリティを使用すると、自動化プロセスが非常に簡単になります。 さらに、ログのミラーリング/配信やバックアップの整合性チェックなど、バックアップに関連する他のプロセスを自動化できます。



最後に、SQL Serverバックアップは必要なことを実行しますが、最良の方法では実行しない場合があります。 たとえば、一部のユーティリティはバックアップをより効率的に圧縮するため、ディスクスペースを節約し、バックアップ時間を短縮します。 また、バックアップファイルの暗号化などの機能も追加します（データベース自体が暗号化されている場合にのみ、組み込みのSQL Serverツールでこのようなことが可能です）。

バックアップがネットワーク共有にある場合、誰かがそれを読むことができますか？

バックアップファイル自体を直接暗号化するまで-はい-これは最も一般的なファイルです。 誰かがこのボールにアクセスできるようになったら、任意のテキストエディターでそれを読むか、単にコピーしてSQL Serverの別のインスタンスから回復を開始できます。



さらに、バックアップから、データベーススキーマまたはデータを復元することなく取得できます。 SQL Data Compareユーティリティを使用している場合、/ Exportスイッチを使用して実行すると、バックアップからすべてのデータをCSV形式で抽出し、このバックアップを空のデータベースと比較し、パスワードを要求することなく比較できます。 また、同じSQLデータ比較により、データベーススキーマを作成するスクリプトを作成できます。



バックアップへの不正アクセスを防ぐために、いくつかのことを行う必要があります。 まず、バックアップが保存されているボールが限られた人たちにアクセスできることを確認してください。 次に、本当に必要なバックアップのみを保存する必要があります。 最後に、サードパーティのバックアップユーティリティ（SQL Backup Proなど）を使用している場合、バックアップを暗号化できるため、誰かがファイルに直接アクセスすると、そこから何も読み取れなくなります。



サードパーティのユーティリティがなくても、Transparent Data Encryption（TDE）を使用してこれを実現できます。



最高レベルのセキュリティを確保するには、上記のすべてのアクションを実行する必要があります。

誰でもバックアップの内容を変更できますか？

バックアップファイルの内容を変更する機能は提供されていません。 バックアップは（バックアップ時に存在した形式の）データベースのページコピーであるため、このデータベースの復元されたコピーは、バックアップ時に元のコピーとまったく同じ状態になります。

SQL Serverは、データベースの回復中に各ページを読み取るとき、その内容に応じてチェックサムを計算し、バックアップの作成時に元のページから読み取られた値と比較します（作成時にWITH CHECKSUMパラメーターを使用したことが理解されます）バックアップコピー）。 誰かがバックアップファイルに変更を加えた場合、これらの値は一致せず、SQL Serverはページを破損としてマークします。

バックアップを作成するときに、常にそれから回復できると確信できるように設定することでフラグがありますか？

このフラグにより​​、バックアッププロセスがバックアップの作成後にRESTORE VERIFYONLY操作の実行を伴うことを意味する場合、いいえ、このバックアップからデータベースを復元できることを確認できません。 RESTORE VERIFYONLYは、2つのチェックのセットを実行できます。



まず、バックアップヘッダーをチェックして、エラーがないことを確認します。 ヘッダーが破損している場合、このバックアップからデータベースを復元できません。

 RESTORE VERIFYONLY FROM DISK= '<Backup_location>'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

2番目のチェックは、WITH CHECKSUMパラメーターを使用してバックアップ手順を開始した場合にのみ可能です。 つまり、バックアッププロセス中に、SQL Serverは読み取られたすべてのページのチェックサムを再カウントして検証します。 これらの量が収束しないページを見つけた場合、バックアップ操作は失敗します。 チェックが成功すると、BACKUP WITH CHECKSUMは作成されたコピーのチェックサムを計算して書き込みます。



したがって、RESTORE VERIFYONLYを使用してチェックサムを再計算し、保存中にバックアップコピーが破損していないことを確認できます。

 RESTORE VERIFYONLY FROM DISK= '<Backup_location>' WITH CHECKSUM
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

問題は2つの場所で発生する可能性があります。 まず、実行時にVERIFYONLYでヘッダーをチェックしても、リカバリプロセスに影響する可能性のあるものはチェックされません。 つまり、RESTORE VERIFYONLYはエラーなしで完了できますが、「検証済み」コピーからデータベースを復元することはできません。



第二に、CHECKSUMはメモリで発生した損傷を検出できません。 メモリ内でデータページが更新され、ディスク（および、それに応じてバックアップ）に書き込まれる前に破損した場合、チェックサムの計算ではエラーは表示されず、単に同じものがバックアップに書き込まれたことを確認しますバックアップ時にデータベースに含まれていたページ。 つまり バックアップ時にページがすでに破損している場合、チェックサムを使用してエラーを見つけることができず、このバックアップからの復元が失敗する可能性があります。



バックアップから回復でき、結果のデータベースが破損していないことを確実に知る唯一の方法は、それを復元し、できれば復元されたコピーに対してデータベースの整合性チェックを実行することです。

バックアップにはデータ以外のものが含まれていますか？ 誰でもそこからパスワードを読むことができますか？

バックアップにはデータだけが含まれます。 データベース構造全体が含まれています。 すべてのデータ、プロシージャ、表現、関数、およびその他のすべてのコードが含まれます。 また、すべてのデータベース設定が含まれています。 最後に、データベースユーザーに関するすべての情報が含まれています。 通常のデータベースの場合、各データベースユーザーはSQL Serverアカウントに関連付けられます。 そのようなユーザーのパスワードはアカウントとともに保存されるため、バックアップにはそのようなパスワードはありません。



ただし、スタンドアロンデータベース（ 包含データベース -約Translator ）には、USER WITH PASSWORDという概念があります。スタンドアロンデータベースの概念は、そのようなデータベースとサーバーとの最小限の接続を意味するためです。 この場合、パスワードはバックアップに含まれているため、そこからパスワードを取得しようとする可能性があります。 パスワードは平文ではなく、アカウントパスワード（マスターシステムデータベースに保存され、当然そのバックアップに入ります）と同様にハッシュされます。



Microsoftは、自律型データベースをセキュリティで保護するためのいくつかのベストプラクティスを提供しています。

なぜバックアップインデックス、統計、その他の再作成が簡単なのですか？ それは時間の無駄ですか？

そして私の意見では、時間の損失は、このように物事を分離し、1つの部分のみのバックアップを作成しようとする試みです。 まず、それを行う方法は？ たとえば、クラスターインデックスをバックアップせずにデータをバックアップする方法は？ クラスタ化インデックスのリーフレベルはデータページであるため、これは不可能です。つまり、クラスターインデックスはテーブル自体であると言えます。したがって、クラスターインデックスをバックアップに含める必要があります。もちろん、非クラスター化インデックスを個別のファイルグループに分けてバックアップすることはできませんが、バックアップを復元した後、このファイルグループを元の状態に戻し、すべてのインデックスを再構築する必要があります。では、何を達成するのでしょうか？



統計に関する問題もあります。 SQL Serverは、データベースと一緒に統計をバックアップし（統計と呼ばれるヒストグラムは200行のみで構築されるため、スペースをほとんど消費しません）、データベースと一緒に復元します。ただし、回復後にインデックスの再作成を開始した場合、インデックスをバックアップしなかったため、統計を再作成する必要があります。また、追加の時間が必要になり、データベースはアクセス不能のままになります。



結局、緊急時にプロセス全体が非常に混乱する可能性があり、このデータベースで作業している人々はそれ以上アクセスできないという事実につながるため、「再作成が簡単」という言葉で議論しますバックアップからの単純な回復の場合よりも時間がかかります。



バックアップを作成するまさにその考えは、データベースを可能な限り迅速かつ効率的に復元できるようにすることです。リカバリプロセスが複雑になるほど、バックアップの効率は低下します。はい、インデックス、ユーザー、ストアドプロシージャ、その他すべてを保存するには、追加のスペースが必要ですが、すべてが1か所にあるという事実により、この追加スペースの価値があるため、リカバリの速度が向上します。

OMG！テーブルを削除しました！これがトランザクションログにあることを知っています。彼女を取り戻すにはどうすればいいですか？

トランザクションがコミットされた後、SQL Serverはそれをロールバックできません。 DELETEおよびTRUNCATE操作は、まったく異なる方法でデータを削除します。 DELETE操作は、各行を削除するトランザクションを使用してデータを削除します。 TRUNCATE操作は、削除されたデータが存在していたデータページを単に使用されていないとしてマークします。ただし、トランザクションログを表示するときに、これらの操作の結果を手動で排除することはできません。代わりに、ポイントインタイムリカバリと呼ばれるプロセスを実行する必要があります。テーブルから必要なデータを誤って削除するまでのすべての変更を保存するために、データベースのトランザクションログのバックアップをすぐに作成する必要があります。次に、このマニュアルの第6章の手順に従う必要があります。ある時点での回復のために（MSDNにはすべてがあります -約翻訳者）。



もう1つのオプションは、SQL Backup Proなどのサードパーティユーティリティを使用することです。これにより、既存のバックアップから個々のデータベースオブジェクトをオンラインで復元できます。

また、バックアップを直接復元せずに、バックアップを使用してデータベースを構築するスクリプトを作成するだけの場合は...？

SQL Serverにこのようなスクリプトを作成するための標準ツールはありません。ただし、SQL Compareなどのユーティリティで作成できます。 GUIを使用して簡単に作成



& 'C:\Program Files (x86)\Red Gate\SQL Compare 8\SQLCompare.exe' /Backup1:C:\MyBackups\MyBackupFile.bak /MakeScripts:"C:\MyScripts\MyBackupScript"







できますが、PowerShellを使用して作成することもできます。SQL仮想復元にも注意を払うことができます。このユーティリティを使用すると、あたかもこのバックアップからリカバリプロセスを開始しているかのようにSQL Serverにバックアップをマウントできますが、リカバリ中に必要なすべてのスペースを使用する必要はありません。この方法でマウントされたバックアップは、最も一般的なデータベースのように見え、ユーザーにとって都合の良い方法でスクリプトを作成できます。



翻訳者から：

, . , - – ( ).

, .