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 had to string together the answer from several places, so I wil post it here: Mind, manually detaching the .mdf file from Visual Studio is possible after manually deleting it before detachment by going through SQL Server Object Explorer.

''' <summary>
''' Detach a database from LocalDB. This MUST be done prior to deleting it. It must also be done after a inadvertent (or ill advised) manual delete.
''' </summary>
''' <param name="dbName">The NAME of the database, not its filename.</param>
''' <remarks></remarks>
Private Sub DetachDatabase(dbName As String)
    Try
        'Close the connection to the database.
        myViewModel.CloseDatabase()

        'Connect to the MASTER database in order to excute the detach command on it.
        Dim connectionString = String.Format("Data Source=(LocalDB)\v11.0;Initial Catalog=master;Integrated Security=True")
        Using connection As New SqlConnection(connectionString)
            connection.Open()
            Dim cmd = connection.CreateCommand
            '--Before the database file can be detached from code the workaround below has to be applied.
            'http://web.archive.org/web/20130429051616/http://gunnalag.wordpress.com/2012/02/27/fix-cannot-detach-the-database-dbname-because-it-is-currently-in-use-microsoft-sql-server-error-3703
            cmd.CommandText = String.Format("ALTER DATABASE [{0}] SET OFFLINE WITH ROLLBACK IMMEDIATE", dbName)
            cmd.ExecuteNonQuery()
            '--
            '--Now detach
            cmd.CommandText = String.Format("exec sp_detach_db '{0}'", dbName)
            cmd.ExecuteNonQuery()
            '--
        End Using
    Catch ex As Exception
        'Do something meaningful here.    
    End Try
End Sub

Answer 2

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

There are 3 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 the 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();
   

3. 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 the 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.

Answer 3

Here is my solution for EntityFramework Core 1.0

As you see the database name can be used with its full file path.

var dbf = fileDlg.FileName;
var options = new DbContextOptionsBuilder();
options.UseSqlServer($@"Server=(localdb)\mssqllocaldb;Initial Catalog=master;MultipleActiveResultSets=False;Integrated Security=True");
using (var master = new DbContext(options.Options))
{
     master.Database.ExecuteSqlCommand($"ALTER DATABASE [{dbf}] SET OFFLINE WITH ROLLBACK IMMEDIATE");
     master.Database.ExecuteSqlCommand($"exec sp_detach_db '{dbf}'");
}