4 Replies - 945 Views - Last Post: 20 January 2013 - 06:28 PM Rate Topic: -----

#1 .net_Apprentice  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 27-October 12

Restoring SQL DataBase by Code from VB 2010

Posted 12 January 2013 - 08:47 PM

Hi guys:
Can anyone help please
I am working in VB 2010. My program works wit SQL DataBase "dbCARS" . I Open the conections, make my Queries and Close them and Disposed Inmediately.
From my program I Back-Up the DB and works fine but the Restore Sub Throws this error messege:

"Exclusive access could not be obtained because the database is in use" RESTORE DATABASE is terminating abnormally.
I have no idea what is wrong. I am positive all conections are close.
Private Sub RestoreDB()

Dim con As New SqlConnection("Data_ Source=(local);Initial Catalog=dbCARS;Integrated Security=True")

        Try
            Dim strResSQLsentence As String = "RESTORE DATABASE dbCARS FROM DISK='" & txtDBToRestore.Text & "'"

            con.Open()
            Cmd = New SqlCommand(strResSQLsentence, con)
            Cmd.ExecuteNonQuery()
            MsgBox("DataBase have been restored.")
            con.Close()

        Catch ex As Exception
            MsgBox("Could not be restored.", vbOKOnly)
        Finally
            con.Close()
            con.Dispose()
        End Try

    End Sub



Thanks in advance.

Is This A Good Question/Topic? 0
  • +

Replies To: Restoring SQL DataBase by Code from VB 2010

#2 _HAWK_  Icon User is online

  • Master(Of Foo)
  • member icon

Reputation: 1030
  • View blog
  • Posts: 4,010
  • Joined: 02-July 08

Re: Restoring SQL DataBase by Code from VB 2010

Posted 12 January 2013 - 10:14 PM

Try to stop and restart the server in the surface configuration editor. Then run the sub. Double check other db operations for closing connections. In SSMS make sure the AutoClose property is set to True - so if you are in SSMS and exit out it closes the connection.

Worst case reboot will close it.
Was This Post Helpful? 1
  • +
  • -

#3 .net_Apprentice  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 27-October 12

Re: Restoring SQL DataBase by Code from VB 2010

Posted 12 January 2013 - 10:57 PM

Thanks a lot.
I'm a biginer in vb.net so I'll have to google a little bit first understand everything.
But I appreciate your help.
Thank you Hawk
Was This Post Helpful? 0
  • +
  • -

#4 bibang  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 7
  • Joined: 13-November 12

Re: Restoring SQL DataBase by Code from VB 2010

Posted 14 January 2013 - 04:53 AM

sir apprentice, is this already working? how did u do the backup and restore sql database from vb 2010? thank you, i also needed this with my project, and im still going on researching, so im asking you if this is already successful? thank ou
Was This Post Helpful? 1
  • +
  • -

#5 .net_Apprentice  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 27-October 12

Re: Restoring SQL DataBase by Code from VB 2010

Posted 20 January 2013 - 06:28 PM

Hi bibang:
The DB has to be Dropped or Deleted from the server in orden to be restored. If it physically exist in the Server the Restore proccess does not work.
This is the BackUp code. To easily pick the file where DB is going to be Backed-Up I used a FileSaveDialog.
    Sub BackUpDB()
        Try
            Dim strBakName As String = Me.txtNameBackUp.Text & ".bak"
            Dim bakcon As New SqlConnection("Data Source=(local);Initial _ 
                     Catalog=dbPROYECTOCARROS;Integrated    Security=True")
            Dim bakadp As SqlDataAdapter
            Dim bakds As DataSet
            Dim strbakSQLSentence As String = "BACKUP DATABASE dbCARS TO DISK= '" & Me.txtFolderPath.Text & "\" & strBakName & "' WITH INIT, NOUNLOAD, NAME = '" & strBakName & "' , NOSKIP , STATS = 10 , NOFORMAT "

            bakadp = New SqlDataAdapter(strbakSQLSentence, bakcon)
            bakcon.Open()
            bakds = New DataSet
            bakadp.Fill(bakds)
            bakcon.Close()
            MsgBox("Copy of your DB have been created", vbOKOnly)
        Catch ex As Exception
            MsgBox("Lo siento pero no he podido crear tu copia." & vbNewLine & "Ha ocurrido un error.")
        Finally
            If bakcon.State = ConnectionState.Open Then
                bakcon.Close()
                bakcon.Dispose()
            End If
        End Try
    End Sub



This is what i did to restore it. To find the (already Backed-Up) File Location and Name I used a OpenFileDialog.
   Private Sub RestoreDB()
        Try
            'THI[b]S TIME THE CONNECTION IS SET TO THE MASTER db[/b]
            Dim rescon As New SqlConnection("Data Source=(local);Initial Catalog=master;Integrated Security=True")
            Dim resAdp As SqlDataAdapter
            Dim resCmd As SqlCommand            
            Dim strResSQLsentencia As String = "RESTORE DATABASE dbCARS_
 FROM DISK='" & txtDBtoRestore.Text & "'"
            rescon.Open()
            resCmd = New SqlCommand(SQLsentence, rescon)
            resCmd.ExecuteNonQuery()
            MsgBox("Db Has been Restored.")
            rescon.Close()
        Catch ex As Exception
            MsgBox("Could not be restored.", vbOKOnly)
        Finally
            If rescon.Stste=ConnectionState.Open Then
                rescon.Close()
                rescon.Dispose()
            End If
        End Try

    End Sub


Anything I can help. Here I am.
Thank you for let me do something helpful.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1