I have a project developed in VB.NET and SQL Server 2012 LocalDB (v11) and I am need backup/restore facility in my application. Backup part is complete but I am stuck at restore part. This is the query I want to be worked (and its working fine in SQL Editor)
ALTER DATABASE [<.MDF File Path>]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [<.MDF File Path] FROM DISK='<.bak File Path'
and here is the code in VB.NET I am trying to execute
Sub Restorequery(ByVal que As String)
MainForm.Conn.Close()
con = New SqlConnection("Data Source=(LocalDB)\v11.0;Database=Master;integrated security=True;")
If Not con.State = ConnectionState.Open Then con.Open()
cmd = New SqlCommand(que, con)
cmd.ExecuteNonQuery()
End Sub
And here are the approaches I tried so far
Using the same query as above
Restorequery("ALTER DATABASE [<.MDF File Path>] SET SINGLE_USER WITH ROLLBACK IMMEDIATE")
Restorequery("restore database [<.MDF File Path>] from disk='<.bak File Path>'")
and this results in an error
Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally.
After finding reason for above issue (which is nonsense even after using master
database while opening connection and closing all the previously opened connections using MainForm.Conn.Close()
), I tried second approach as per some links and references from Stackoverflow. And here are the queries I tried:
Restorequery("use [master] ")
Restorequery("alter database [<.MDF File Name>] set single_user with rollback immediate")
Restorequery("restore database[<.MDF File Name>] from disk='<.bak File Name>'")
Restorequery("alter database [<.MDF File Name>] set multi_user")
Restorequery("use [<.MDF File Name>]")
and here is the error I got while executing second query :
Additional information: User does not have permission to alter database <.MDF File Name with Path>, the database does not exist, or the database is not in a state that allows access checks. ALTER DATABASE statement failed.
Is there any other way to restore a SQL Server LocalDB using VB.NET ?
I have Used this code in one of my project.
Try
With OpenFileDialog1
.Filter = ("DB Backup File|*.bak;")
.FilterIndex = 4
End With
OpenFileDialog1.FileName = ""
If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
Cursor = Cursors.WaitCursor
SqlConnection.ClearAllPools()
con = New SqlConnection(cs)
con.Open()
Dim cb As String = "USE Master ALTER DATABASE [" & System.Windows.Forms.Application.StartupPath & "\BRH.mdf] SET Single_User WITH Rollback Immediate Restore database [" & System.Windows.Forms.Application.StartupPath & "\BRH.mdf] FROM disk='" & OpenFileDialog1.FileName & "' WITH REPLACE ALTER DATABASE [" & System.Windows.Forms.Application.StartupPath & "\BRH.mdf] SET Multi_User "
cmd = New SqlCommand(cb)
cmd.Connection = con
cmd.ExecuteReader()
con.Close()
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try