きよくらの備忘録

「三日坊主と呼ばせない!日記」改め。主にソフトウェア開発関連の話題。

SQL Server LocalDBのインスタンスを削除してもユーザーデータベースのファイルが残る

f:id:kiyokura:20161220002431p:plain

TL;DR

  • SQL Server Express LocalDBのインスタンスをSQL Server Data Tools (SSDT)のSQL Server オブジェクトエクスプローラーやコマンドラインユーティリティー SqlLocalDb.exeで削除すると、ユーザーデータベースのmdf/ldfファイルは残る
  • この状態で同名のインスタンスを再度作成後、もと合ったものと同名のデータベースを作成しようとするとエラーが発生する
  • これを回避/回復するには以下の方法がある
    • 事前にユーザーデータベースを削除した上でインスタンスを削除する
    • インスタンスを削除済みの場合はファイルシステム上の実体を手動で削除する
      • SSDT関連のツールで作成した場合は%USERPROFILE%\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances にあるインスタンス名のフォルダが対象
      • CREATE DATABASE ~の規定値で作成した場合は%USERPROFILE%\Documents配下に作成さる
    • SSDT関連ツールで作成したデータベースであれば、.NETから簡単に利用できるライブラリ SQL LocalDB Wrapper を使ってコード中から削除できる
  • 手元で試した環境は SQL Server LocalDB 13.0.2151.0 および SQL Server Data Tools 14.0.61021.0

 

2016/12/20 追記

全体的にSSDT関連の機能で作成した場合を前提に書いてしまっていました(ここのところずっとSSDT絡みでLocalDBを使っていたので……)。 SSDT関連のツールでデータベースを作成するとファイルの実体は%USERPROFILE%\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances にある各インスタンスのフォルダ内に作成されます。 しかし、CREATE DATABASE ~で規定値で作成した場合は%USERPROFILE%\Documentsに作成されます。ですので、ここを手動で削除する必要があります。このケースでは、SQL LocalDB Wrapperでも対応できません。事前にDROP DATABASEするか、事後に手動で削除する必要があります。

SQL Server Express LocalDBのインスタンスを削除してもユーザーDBファイルは残る

SQL Server Express LocalDB(以下、LocalDB)では、コマンドラインユーティリティー SqlLocalDb.exe やSQL Server Data Tools(以下、SSDT)のSQL Server オブジェクトエクスプローラー(以下、SSOE)を使ってインスタンスを削除することができます。

これらの方法でインスタンスの削除を行った場合、(事前にユーザーデータべースを削除していない限り)ファイルシステム上にはユーザーデータベースのファイル(mdf/ldf)が残ります。 うっかり削除してしまった場合でもデータ損失を起こさないで済みますから、これは自体は悪い挙動ではないと思います。しかし、この挙動を知っていないと少しはまることもあると思います(というかはまった)。

試してみる

では、少し試してみます。 ツールとしてSSOEとSqlLocalDb.exe を使います。

1. LocalDBの作成

まずはSqlLocalDb.exe で、以下のようにLocalDBを作ります。 SqlLocalDb.exeは%ProgramFiles%\Microsoft SQL Server\<version>\Tools\Binn\SqlLocalDB.exe あたりにあると思います*1

C:\> SqlLocalDB create MyLocalDb01

f:id:kiyokura:20161220002440p:plain

(※バージョンはインストールされているLocalDBのバージョンおよびパスの優先順位などが影響します)

2.SQL Serverオブジェクトエクスプローラーで接続

Visual Studio を立ち上げ、作成したインスタンスにSSOEから接続します*2

最近のSSDTなら参照タブのLocalノードにLocalDBのインスタンスが列挙されるので、選択して接続するだけでOKです。

f:id:kiyokura:20161220003907p:plain

3.データベースを作成する

接続出来たら、データベースを作成してみます。接続したLocalDBのデータベースノードのコンテキストメニューでデータベースを追加します。とりあえず名前はなんでもOKです。

f:id:kiyokura:20161220002614p:plain

こんな感じでデータベースが作成出来たら準備完了

f:id:kiyokura:20161220002621p:plain

4.インスタンスを削除する

さっそくインスタンスを削除してみます。

SSOEの場合は、インスタンスを右クリックしてコンテキストメニューから「インスタンスの削除*3」。SqlLocalDbで行う場合は、SqlLocalDb delete <instance name> です。

f:id:kiyokura:20161220002633p:plain

あっさり削除できたと思います。先ほど削除したMyLocalDb01はもうありません。

f:id:kiyokura:20161220002640p:plain

接続しようとしても、リストにも出てきません。

f:id:kiyokura:20161220002649p:plain

これで一見、何事もなくきれいに削除できたかのように見えます。

ファイルは残っている

ですが、冒頭に述べた通り、ファイルの実体は残っています。LocalDBのインスタンスの実体は、以下のパスに格納されています。Explorerで次のパスを表示してみましょう。

%USERPROFILE%\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances

先ほど削除したインスタンスのフォルダが残ってますね。

f:id:kiyokura:20161220002708p:plain

中を見てみましょう。作成したユーザーDBのデータベースとログファイルが残っているのが分かります。

f:id:kiyokura:20161220002715p:plain

ファイルが残っていると何か問題が?

消したインスタンスを二度と使わないのであれば特に問題はありません(ディスクを占有はしますが)。 顕著に問題が出てくるのは、再度同名のインスタンスを作成したときです。

やってみましょう。 先ほどと同名のインスタンスをまたコマンドラインで作成し、SSOEで接続します。

f:id:kiyokura:20161220002737p:plain

ここまでは何の問題もなく操作できると思います。では、ここでまた先ほど同名のデータベースを追加しようとすると……。

f:id:kiyokura:20161220002741p:plain

Cannot create file 'C:\Users\<UserName>\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MyLocalDb01\MyDataBase.mdf' because it already exists. Change the file path or the file name, and retry the operation. CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

ということで、作ろうとしたファイル名がすでにあるので作成できません。

これは、データベースプロジェクトの発行機能やユニットテスト実行時のデプロイなどを行おうとしたときにも発生し得ます。

回避策・対応策

これを回避または対応するための方法として、以下を思いつきました*4

  • あらかじめユーザーデータベースを削除してからインスタンスを削除する
  • インスタンス削除後にファイルの実体を削除する
  • (SSDT関連ツールで作成したデータベースであれば)SQL LocalDB Wrapper を使ってプログラムを作成して実行する

他二つは自明だと思うので、「SQL LocalDB Wrapperを使ってプログラムを作成して実行する 」について少し補足しておきます。

SQL LocalDB Wrapperを利用してインスタンスを削除する

LocalDBにはこれを管理・運用するためにネイティブAPIが用意されています。これを.NETから簡単に利用できるようにしたラッパーライブラリが SQL LocalDB Wrapperです。

www.nuget.org

このラッパーライブラリが備えている SqlLocalDbApi.DeleteInstanceメソッドにファイルを削除するオプションがあり、これを使うとサクっと削除できました%USERPROFILE%¥AppData\~配下にあるインスタンスのフォルダごと削除してくれます

    SqlLocalDbApi.DeleteInstance(instanceName, true);

なので、SSDT関連のツールで作った場合など、%USERPROFILE%\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances配下に実体を配置しているケースではとても有効だと思います。 頻繁にこういう操作をする場合はこれを実行するだけのコマンドラインツールでも作っておくとよいかもしれませんね。

以下余談

この「ラッパーライブラリで用意されているということは当然ネイティブのAPIでも削除するオプションがあるに違いない」ともうかもしれません。……が、実はありません*5。 リファレンスのLocalDBDeleteInstance 関数を見てみると、第二引数に DWORD dwFlags とかあって「お、これにフラグ指定したら削除されるんちゃうん!?」と思わせてくれます。が、よくよく見ると、

dwFlags [入力] 将来の使用のために予約されています。 現時点では、0 に設定する必要があります。

と書いてあります。Oh...。

さらに余談

では先ほどのSQL LocalDB Wrapperではどうやってるんだろう、と思ってソースを見てみると……自力で削除してました:p

DeleteInstanceFiles メソッドあたりを参照

まとめ

安全側に倒しているのは理解できるのですが、標準で用意されているツールからだとさくっと消す方法が無い&表面的には消えている(ようにも)見えるので、LocalDBの仕組みを知らないとはまってしまう可能性があるように思います。というかは少しまりました。

将来的にユーティリティーにファイル削除するオプションをつけたり、SQL Server オブジェクトエクスプローラーでもファイルを削除するかダイアログで聞いてから消してくれるようになるとかすると嬉しいですね。

参考

*1:SSDTインストール時にパスが通ってる気もします

*2:SSOE使うだけなら特にプロジェクトは開かなくても大丈夫です

*3:手元も環境だと今SSDTが英語版になってるので[Delete Instance...]になってる

*4:他にも良い方法があるかもしれません。ご存知の方がおられてたらぜひ教えてくださいm(__)m

*5:今のところ