vb.netvisual-studio-2013localdbdatabase-restoresql-server-2012-localdb

Restore database in SQL LocalDB using VB.NET


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

  1. 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.

  2. 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 ?


Solution

  • 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