How to detach a LocalDB (SQL Server Express) file in code

Question

When using LocalDB .mdf files in deployment you will often want to move, delete or backup the database file. It is paramount to detach this file first as simply deleting it will cause errors because LocalDB still keeps a registration of it.

So how is a LocalDB .mdf file detached in code?

Answer 1

I have same issue and was thinking of how to deal with it.

There are 2 approaches.

(1) Detach at the end of (or during) working with database

I didn't find the way to close connection in LinqToSQL, but actually it is not needed. Simply execute following code:

var db = @"c:\blablabla\database1.mdf";
using (var master = new DataContext(@"Data Source=(LocalDB)\v11.0;Initial Catalog=master;Integrated Security=True"))
{
    master.ExecuteCommand(@"ALTER DATABASE [{0}] SET OFFLINE WITH ROLLBACK IMMEDIATE", db);
    master.ExecuteCommand(@"exec sp_detach_db '{0}'", db);
}

and make sure nothing will try to query db after (or you get it attached again).

(2) Detach on start

Before you made any connection to db, detaching is as simple as:

var db = @"c:\blablabla\database1.mdf";
using (var master = new DataContext(@"Data Source=(LocalDB)\v11.0;Initial Catalog=master;Integrated Security=True"))
    master.ExecuteCommand(@"exec sp_detach_db '{0}'", db);

This suit very well to my needs, because I do not care about delay to start application (because in this case I will have to attach to db always), but it will fix any kind of

System.Data.SqlClient.SqlException (0x80131904): Database 'c:\blablabla\database1.mdf' already exists. Choose a different database name.

which occurs, if database file is delete and you try to create it programmatically

// DataContext
if (!DatabaseExists())
    CreateDatabase();

Another way

You can also run command line tool sqllocaldb like this:

        var start = new ProcessStartInfo("sqllocaldb", "stop v11.0");
        start.WindowStyle = ProcessWindowStyle.Hidden;
        using (var stop = Process.Start(start))
            stop.WaitForExit();
        start.Arguments = "delete v11.0";
        using (var delete = Process.Start(start))
            delete.WaitForExit();

It will stop server, detaching all databases. If you have other application using localDB, then they will experience attaching delay next time when they try to do query.