如何在代码中分离LocalDB(SQL Server Express)文件

发布时间：2020-12-12 06:24:01 所属栏目：MsSql教程来源：网络整理

导读：在部署中使用LocalDB .mdf文件时,您通常需要移动,删除或备份数据库文件. 首先将此文件分离为 simply deleting it will cause errors because LocalDB still keeps a registration of it是至关重要的. 那么如何在代码中分离LocalDB .mdf文件？解决方法我不得

在部署中使用LocalDB .mdf文件时,您通常需要移动,删除或备份数据库文件.
首先将此文件分离为 simply deleting it will cause errors because LocalDB still keeps a registration of it是至关重要的.

那么如何在代码中分离LocalDB .mdf文件？

解决方法

我不得不把几个地方的答案串起来,所以我将在这里发布：
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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!