0

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 ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mahadev
  • 856
  • 1
  • 17
  • 44
  • You should try to concatenate together both string commands (ALTER... RESTORE) separating them with a semicolon then execute on a single query using the same connection not two separated connections – Steve Oct 10 '15 at 07:29
  • Nope. Tried that but same problem as `Scenario 1` – Mahadev Oct 10 '15 at 07:34
  • Do you really need to set the database as SINGLE_USER on LocalDB? What happens if you execute directly the restore command (after closing every connection of your own app of course) – Steve Oct 10 '15 at 07:51
  • @Steve : Even after closing every connection, It gives me error like `Database is in use...` – Mahadev Oct 10 '15 at 08:00

1 Answers1

1

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
Bharath
  • 72
  • 11