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

localdb

Solution

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

Problem

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?

Original source

Related problems