MDF ファイルのみからデータベースをリストアする方法

先日開発環境でやらかしたので、忘れないうちに自分用にメモします。

注意とお約束

これから記述する内容は無保証ですので、適用する場合は自己責任でお願いします。
また、最終手段としてとりあえずデータベースを認識させるためのものであり、障害発生時点の状態に戻せるわけではなく、データページに破損がある場合や、チェックポイント未実行のデータがある場合、それらは失われます。また、MDF ファイルが著しく破損している場合はこの方法でも認識できないことがあるかもしれません。

前提

アタッチ機能で MDF ファイルからデータベースをリストアする場合、普通は LDF ファイル（トランザクションログ）を一緒に指定します。
ただし、もともと LDF ファイルの内容がすべて MDF に適用されていた状態であれば、MDF 単独でもアタッチ可能です。この場合、アタッチ後には初期化された LDF ファイルが自動的に作成されます。

Management Studio の GUI では、「データベースの詳細」グリッドから LDF ファイルを選択して削除します。

ただし、LDF ファイルに未適用データが存在していた場合は、この方法ではアタッチできません。強行しようとしても、以下のようなダイアログが表示されてしまいます。

指示に従って詳細を確認すると、次のように表示されます。

LDF ファイルが正しくないと書いてありますね。でも LDF ファイルがない場合はしょうがないので、多少のデータ消失リスクは受け入れて、なんとかデータベースをリストアして生きてるデータだけでも救出したいところです。

リストア手順

それではこれから具体的な手順を書いていきます。

まずは、復旧したいデータベースと同じ名前で新しいデータベースを作成します。今回の例では「Northwind」です。
作成したら、SQL Server のサービスを一旦停止します。その後で、リストアしたい MDF ファイルを、先ほど作成したデータベースの MDF ファイルに上書きコピーして、SQL Server のサービスを起動します。

何をしているかというと、まずは何とかして SQL Server に MDF ファイルをロードしてもらう必要があるので、一旦破損していないデータベースを作っておいて、その MDF ファイルを差し替えるということをしています。

当然、これだけでリストアできるほど単純ではありません。起動後、Management Studio で接続すると、オブジェクトエクスプローラーには次のように「（復旧待ち）」と表示され、テーブル等を参照することはできません。

続いて以下の SQL を実行してデータベースを復旧します。Northwind のところは実際のデータベース名に読み替えてください。

USE master
GO

-- データベースを緊急モードに切り替える
ALTER DATABASE Northwind SET EMERGENCY
GO

-- データベースをシングルユーザモードに切り替える
ALTER DATABASE Northwind SET SINGLE_USER
GO

-- データベースを復旧する
DBCC CHECKDB ('Northwind',REPAIR_ALLOW_DATA_LOSS)
GO

-- データベースをオンラインに切り替える（省略可）
ALTER DATABASE Northwind SET ONLINE
GO

-- データベースをマルチユーザモードに切り替える
ALTER DATABASE Northwind SET MULTI_USER
GO

なお、緊急モードへ切り替えるのに次のような SQL を紹介しているサイトもあります。これは SQL Server 2000 まではこの方法を使っていたようですが、2005 以降では最後の UPDATE で「システム カタログへのアドホック更新は許可されません。」というエラーになります。
2005 以降は上記の方法で OK です。

sp_configure 'allow updates', 1
GO

RECONFIGURE
GO

UPDATE sys.sysdatabases SET STATUS = 32768 WHERE dbid = db_id('Northwind')
GO

DBCC CHECKDB は、MDF ファイルが大きいほど時間がかかりますのでしばらくお待ちください。完了すると以下のようなメッセージが表示されます。

メッセージ 5173、レベル 16、状態 1、行 12
データベースのプライマリ ファイルと一致しないファイルが 1 つ以上あります。データベースをアタッチする場合は、正しいファイルを使用して操作を再試行してください。これが既存のデータベースの場合は、ファイルが壊れている可能性があるので、バックアップから復元する必要があります。
ログ ファイル 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\northwind_log.ldf' がプライマリ ファイルと一致しません。このログ ファイルは別のデータベースまたはログから以前に再構築された可能性があります。
警告: データベース 'Northwind' のログが再構築されました。トランザクションの一貫性は失われました。RESTORE チェーンが壊れ、サーバーが以前のログ ファイルのコンテキストを保持しなくなったので、以前のログ ファイルについて把握しておく必要があります。DBCC CHECKDB を実行して物理的な一貫性を検証してください。データベースは dbo 専用モードに設定されました。データベースが使用可能な状態になったら、データベース オプションを再設定し、余分なログ ファイルを削除してください。
'Northwind' の DBCC 結果。
（省略）
CHECKDB により、データベース 'Northwind' に 0 個のアロケーション エラーと 0 個の一貫性エラーが見つかりました。
DBCC の実行が完了しました。DBCC がエラー メッセージを出力した場合は、システム管理者に相談してください。

かなり長々と表示されますが、大事なのは最後から 2 行目の内容でしょうか。とりあえず、 MDF ファイルに破損はなかったと書かれています。

ただし、エラーがなかったからと言って、データがすべて復旧したわけではありません。最初にも書きましたが、チェックポイント未実行のデータがあった場合、それらは失われます。
実際に上記のメッセージは、チェックポイント未実施で SQL Server を強制終了した時の MDF ファイルからリストアした時のものですが、データの内容は最後のチェックポイント時点まで戻っていました。