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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ファイルは残っている

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

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

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

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

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

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

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

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

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 オブジェクトエクスプローラーでもファイルを削除するかダイアログで聞いてから消してくれるようになるとかすると嬉しいですね。

参考

• SqlLocalDB ユーティリティ
• SQL LocalDB Wrapper

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

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

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

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

*5:今のところ

真・SQL Server Data Toolsのユニットテスト実行前に複数DBにデプロイする

前回の続きです。

前回のエントリに対して、@ahiru_sp さんからアドバイスいただきました。

これ、DatabaseTestService の派生クラスを作って、引数ありのDeployDatabaseProject呼ぶのが正攻法？かと思ったけど、引数になに指定すればいいのか分からんかった（

— あひるさん (@ahiru_sp) December 14, 2016

なんとなくapp.configに書かれてるのと同じ値で良い気はするのだけど

— あひるさん (@ahiru_sp) December 14, 2016

なるほど。リファレンスにあるprotected static な'DeployDatabaseProject(String, String, String, String)'のを使えばいいのではということですね。

SqlDatabaseTestService.DeployDatabaseProject Method (Microsoft.Data.Tools.Schema.Sql.UnitTesting)

SqlDatabaseTestServiceの派生クラスを作成する

ということで、そのままやってみます。

まず以下のような Microsoft.Data.Tools.Schema.Sql.UnitTesting.SqlDatabaseTestService のは派生クラスを作り、DeployDatabaseProject(String, String, String, String)を呼ぶメソッドを一つ作っておきます。

internal class CustomSqlDatabaseTestService : SqlDatabaseTestService
{
  /// <summary>
  /// DBのデプロイ
  /// </summary>
  /// <param name="projectFile">プロジェクトファイルのパス（相対パス）</param>
  /// <param name="projectConfiguration">プロジェクトファイルの構成設定</param>
  /// <param name="connectionString">接続文字列</param>
  public void DeployDatabaseProjectEx(string projectFile, string projectConfiguration, string connectionString)
  {
    SqlDatabaseTestService.DeployDatabaseProject(projectFile, projectConfiguration, "System.Data.SqlClient", connectionString);
  }
}

引数に何を渡すかは、app.configのSqlUnitTestingセクションをみれは推測はできますね。なお、第三引数はリファレンスに This must be System.Data.SqlClient. って明記されてるので埋め込んでおきます（まあどうせSQL Serverだし）。

あとは、昨日のやつと同様、InitializeAssemblyから呼んでやればOKです。例えばこんな感じ。

using System;
using System.Configuration;
using Microsoft.Data.Tools.Schema.Sql.UnitTesting;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Xml;

namespace BletillaTestsNext
{
  [TestClass()]
  public class SqlDatabaseSetup
  {
    // ターゲットデータベース
    const string TARGET_DB_PROJECT = "..\\..\\..\\ MainDb\\MainDb.sqlproj";
    const string TARGET_DB_CONFIGURATION = "Release";
    const string TARGET_DB_CONNECTION_STRING = "＜デプロイ先のDBの接続文字列＞";
   
    // 依存データベース
    const string DEPEND_DB_PROJECT = "..\\..\\..\\SubDb\\SubDb.sqlproj";
    const string DEPEND_DB_CONFIGURATION = "Release";
    const string DEPEND_DB_CONNECTION_STRING = "＜デプロイ先のDBの接続文字列＞ ";

    [AssemblyInitialize()]
    public static void InitializeAssembly(TestContext ctx)
    {
      var testService = new CustomSqlDatabaseTestService();

      // テスト実行ターゲットをデプロイ
      testService.DeployDatabaseProjectEx(TARGET_DB_PROJECT, TARGET_DB_CONFIGURATION, TARGET_DB_CONNECTION_STRING  );
 
      // 依存DBをデプロイ
      testService.DeployDatabaseProjectEx(DEPEND_DB_PROJECT, DEPEND_DB_CONFIGURATION , DEPEND_DB_CONNECTION_STRING);

      // もともとあったこれは現在は使われてないので削除して問題ない
      //SqlDatabaseTestClass.TestService.GenerateData();
    }
  }
}

実際に試してみたら、あっさりそのまま動きました。なんだかすっきりしましたし無理やりapp.configを書き換えるとかするよりもこちらの方が断然スマートですね。

@ahiru_sp さん、ありがとうございました！

SQL Server Data Toolsのユニットテスト実行前に複数DBにデプロイする

SQL Server Data Tools (SSDT)のユニットテスト機能では、テスト実行前にソリューション内のデータベースプロジェクトをテスト実行対象のデータベースに自動でデプロイしてくれる機能があります。

これは非常に便利な機能です。しかし、複数のデータベースにまたがるような開発していると、依存関係があるなどの理由で複数のデータベースプロジェクトの内容をデプロイしたいことが出てきたりします。

SSDTの設定を調べて見ましたがそういった機能は用意されていなさそうで、一旦は諦めていました。が、別件で思案してたところでふと思いついたことを試してみた……ら、なんとか実現できなのでメモしておきます。

SSDTがユニットテスト実行前にデプロイしている仕組み

まず、SSDTがユニットテスト実行前にどうやってデプロイしているかを見てみます。

SSDTで作成されるテストプロジェクトでは、テストが実行されるとまずSqlDatabaseSetupクラスのInitializeAssemblyメソッドが呼ばれます。 このメソッドはMsTest（Microsoft.VisualStudio.QualityTools.UnitTestFrameworkアセンブリ）のAssemblyInitialize属性がついた属性です。

この中で実行されているの処理は二つ。 そのうちのSqlDatabaseTestClass.TestService.DeployDatabaseProject();がデプロイ処理であることは名前からして疑う余地はなさそうです。

この処理が実装されているMicrosoft.Data.Tools.Schema.Sql.UnitTestingアセンブリのソースは公開されていませんので具体的にどのような処理をしているのかはわかりませんが、MSDNには一応載っていました。

SqlDatabaseTestService.DeployDatabaseProject Method

この DeployDatabaseProjectメソッドは、上記のMSDNに

Deploys the database project by using the settings of the user in the app.config file.

と記載されている通り、app.confgのSqlUnitTestingセクションに記載された設定に従ってDBをデプロイします。この記載内容は、テストプロジェクト作成時や「SQL Server テスト構成」のダイアログで設定する内容です。

複数のDB・プロジェクトをデプロイする方法（強引）

追記：もっと適切だと思う方法がありましたので、ぜひそちらを参照してください kiyokura.hateblo.jp

SSDTが生成するユニットテストプロジェクトで自動デプロイが行われている仕組みをもう一度整理すると、こういうことです。

• AssemblyInitialize属性の追加メソッド内でDeployDatabaseProjectメソッドでデプロイが実施される
• DeployDatabaseProjectメソッド`はapp.configの設定に従って処理を行う

……ここまでくればピンと来たかもおられるでしょう。 そうです。

app.configを書き換えて DeployDatabaseProjectを呼ぶ

ようにすればいくらでもデプロイできそうじゃありませんか（

……ということで、例えば以下のようにapp.configを書き換えて実行してやることで、複数のデータベース／プロジェクトをすんなりデプロイすることができました。

using System;
using System.Configuration;
using Microsoft.Data.Tools.Schema.Sql.UnitTesting;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Xml;

namespace BletillaTestsNext
{
  [TestClass()]
  public class SqlDatabaseSetup
  {
    // ターゲットデータベース
    const string TARGET_DB_PROJECT = "..\\..\\..\\ MainDb\\MainDb.sqlproj";
    const string TARGET_DB_CONNECTION_STRING = "＜デプロイ先のDBの接続文字列＞";
    
    // 依存データベース
    const string DEPEND_DB_PROJECT = "..\\..\\..\\SubDb\\SubDb.sqlproj";
    const string DEPEND_DB_CONNECTION_STRING = "＜デプロイ先のDBの接続文字列＞ ";

    [AssemblyInitialize()]
    public static void InitializeAssembly(TestContext ctx)
    {
      // テスト実行ターゲットをデプロイ
      RewriteConfig(TARGET_DB_PROJECT, TARGET_DB_CONNECTION_STRING);
      SqlDatabaseTestClass.TestService.DeployDatabaseProject();
 
      // 依存DBをデプロイ
      RewriteConfig(DEPEND_DB_PROJECT, DEPEND_DB_CONNECTION_STRING);
      SqlDatabaseTestClass.TestService.DeployDatabaseProject();
 
      // テスト実行用に元に戻しておく
      RewriteConfig(TARGET_DB_PROJECT, TARGET_DB_CONNECTION_STRING);


      SqlDatabaseTestClass.TestService.GenerateData();
    }
    
    private static void RewriteConfig(string projectFile, string connectionString)
    {
      // デプロイは特権コンテキスト（PrivilegedContext）で行われるのでそちらだけ書き換えればよい
      var xmlDoc = new XmlDocument();
      xmlDoc.Load(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);
      xmlDoc.SelectSingleNode("//SqlUnitTesting/DatabaseDeployment").Attributes["DatabaseProjectFileName"].Value = projectFile;
      xmlDoc.SelectSingleNode("//SqlUnitTesting/PrivilegedContext").Attributes["ConnectionString"].Value = connectionString;
      xmlDoc.Save(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);
      ConfigurationManager.RefreshSection("SqlUnitTesting");
    }
  }
}

見てのとおり、app.configを読み込んで、

• SqlUnitTesting/DatabaseDeploymentノードのDatabaseProjectFileName属性に対象のプロジェクトファイルを
• SqlUnitTesting/PrivilegedContextノードのConnectionString属性にでデプロイ先のプロジェクトファイルを

書き込んで反映、念のためリフレッシュしています。

なお、SqlUnitTestingセクションで接続文字列を宣言してる箇所としてPrivilegedContextとExecutionContextの二つがありますが、この最初のでデプロイで利用されるのはPrivilegedContextですのでそこは間違えないようにする必要があります。 またプロジェクトファイルのパスは、このテストプロジェクトのアセンブリ出力パス（bin\Debug）からの相対パスで指定しています。

そのほかこの例ではプロジェクトのパスや接続文字列を直書きしてますが、実際にはapp.configにAppSettingsセクションを追加したりして管理するといいかもしれません。

まとめ

多少強引感もありますが、割とすんなり動いてる感があります。というか標準で複数デプロイ先へのデプロイに標準で対応してくれればもっと嬉しいのですが:p

オープンソースカンファレンス2016広島でASP.NET Coreについてお話しさせていただきました

ぼやぼやしている間にずいぶんマが空いてしまったのですが、11/27（日）に開催されたオープンソースカンファレンス 2016 Hiroshimaにて、ASP.NET Core on Linuxのタイトルでお話しさせていただきました。

セッション資料はこちらです。

ASP.NET Core on Linux—きよくら ならみ

資料にも明記していますが、開発環境と実行環境という環境面（とデプロイについて）について、Linuxを実行環境としたデモを中心にしてお話しさせていただきました。 当日、実際に配置して実行した（手元のWebサーバからアクセスした）のは、クラウド上のLinux（Ubuntu）でした。

Windows上のVisual Studio Codeで開発し、そのままWindows上でUbuntu用のELF形式の実行ファイルの形でビルドしてサーバ上に転送、実行というデモもやらせていただきました。

ASP.NET Coreのプログラミンよりの部分についてはほぼ（というか全く）触れれませんでしたが、実際に触ってみようと思われた方は、ぜひ以下のオフィシャルのチュートリアルやセッション中でも紹介した書籍などを参考されるとよいのではないかと思います。

ASP.NET MVCプログラミング入門 (マイクロソフト関連書)

告知：ASP.NET Core on Macやります

今週末、 12/17（土）に開催される 合同勉強会 in 大都会岡山 -2016 Winter-にて、Macでの開発のデモをやってみる予定です。 Visual Studio Code, Visual Studio for Mac, Riderあたりでそれぞれデモをやってみようかなと思っています。

時間の都合上、デプロイ周りとか細かな説明はおそらく出来ないので、そのあたり興味あるかたは前述の資料をご覧いただくか、当日個別に捕まえて聞いていただければと思います。

DapperのQuery<dynamic>()の結果セットのフィールド名を取得する

ちょっと必要があったのでメモ。

Querydynamic()>が返すdynamicの実体はDapper.SqlMapper.DapperRowのコレクション

Query<dynamic()>()が返すdynamicの実体はDapper.SqlMapper.DapperRowのコレクションです。

このDapperRowはDapperのDapper.SqlMapperのprivateな型ですが、以下の通りIDictionary<string, object> の実装です。

github.com

ということで素直にIDictionary<string, object>にキャストしてみます。

  // SQLは実際にはSELECT * とか結果セット戻すストアドとか
  var result = cn.Query("SELECT 1 AS Id, 'Taro' AS Name , 20 AS Age"); 
  var fieldList = ((IDictionary<string, object>)result.First())
                    .Select(x => x.Key)
                    .ToList();

こんな感じで取れました。

どこでつかうん？

『どこでそんなもの使うの？』とか思う向きもかもしれないですが、クエリが返すフィールド名をハードコーディングしたくないケースって意外とあるというか、まあそんな感じで。 （例えばプログラム部分が『土管』に徹する場合（DB→JSONtとかDB→Excelとか）等だと、両端の柔軟性を生かす事ができると思います。というか、今回やりたいケースがまさにそれでした。）