Results 1 to 23 of 23

Thread: Restore database from file

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Restore database from file

    can someone help me fix this
    i want to select file and restore it
    but i got message that

    Exclusive access could not be obtained because the database is in use.
    RESTORE DATABASE is terminating abnormally.

    cmd.ExecuteNonQuery() <---

    how to fix it?

    Code:
     Private Sub Execute(ByVal strAction As Action)
            Dim Filename As String
            ServerName1 = cmbServerName1.Text.Trim()
            UserID1 = txtUserName1.Text.Trim()
            Password1 = txtPassword1.Text.Trim()
            database = cmbDataBase1.Text
    
            If cmbAuthe1.SelectedIndex = 0 Then
                strConn = "Data Source=" & ServerName1 & ";Initial Catalog=" & cmbDataBase1.Text & ";Integrated Security=True"
            Else
                strConn = "Data Source=" & ServerName1 & "; Initial Catalog=" & cmbDataBase1.Text & ";user id=" & UserID1 & ";password=" & Password1 & ";Integrated Security=false"
            End If
            con = New SqlConnection(strConn)
            con.Open()
            Dim strQuery As String
            If strAction = Action.BackUp Then
                Dim objdlg As New SaveFileDialog
                objdlg.FileName = database
                objdlg.ShowDialog()
                Filename = objdlg.FileName
                strQuery = "backup database " & database & " to disk='" & Filename & "'"
            Else
                Dim objdlg As New OpenFileDialog
                objdlg.FileName = database
                objdlg.ShowDialog()
                Filename = objdlg.FileName
                strQuery = "RESTORE DATABASE " & database & " FROM disk='" & Filename & "'"
            End If
    
            Dim cmd As SqlCommand
            cmd = New SqlCommand(strQuery, con)
            cmd.ExecuteNonQuery()
        End Sub

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Restore database from file

    While you can backup a database while connected to it (because it's a read operation) you CANNOT be connected to the database when you restore it (because it's a write operation) ... you have to connect to a DIFFERENT database (preferably the master or default database) in order to issue a restore.
    That's a long way of saying your connectionstring needs to change.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Re: Restore database from file

    how can i do it
    i must write database name in field and click restore so can connect to the db, how to fix it
    take a look on the sourceBackUpAndRestoreSQLDB.zip
    need only to fix this to restore db

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Restore database from file

    how can i do it
    As I said... don't connect to the database... connect to a different database, such as Master or Default. I clearly stated that "your connectionstring needs to change."

    i must write database name in field and click restore so can connect to the db, how to fix it
    No... you need the database name to restore it... NOT to connect to it.

    take a look on the sourceBackUpAndRestoreSQLDB.zip
    I don't need to.. the code you provided is enough for me to see the problem, even if you can't.

    need only to fix this to restore db
    You need a different connectionstring for your restore... I don't know how many more times I can say it... "your connectionstring needs to change."

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Re: Restore database from file

    how to change the db then..i got fields that i write show me code how to change the db then that is used dont know how to connect it to other

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Restore database from file

    sigh.... fine....

    you connect it to another database BY GIVING it the name of the other database... like "master" ....


    Code:
            If cmbAuthe1.SelectedIndex = 0 Then
                strConn = "Data Source=" & ServerName1 & ";Initial Catalog=" & if(strAction = Action.BackUp, cmbDataBase1.Text, "master") & ";Integrated Security=True"
            Else
                strConn = "Data Source=" & ServerName1 & "; Initial Catalog=" & if(strAction = Action.BackUp, cmbDataBase1.Text, "master") & ";user id=" & UserID1 & ";password=" & Password1 & ";Integrated Security=false"
            End If
    there... IF you're doing a back up, it will use the database name, otherwise it will use master as the database...

    that's all... as I said, simply connect to a DIFFERENT database for a restore...


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Re: Restore database from file

    so i need to edit this:
    cmbDataBase1.Items.Add(database.Name)
    ?
    and add the code

    Dim cmd As SqlCommand
    cmd = New SqlCommand(strQuery, con)
    cmd.ExecuteNonQuery()

    If cmbAuthe1.SelectedIndex = 0 Then
    strConn = "Data Source=" & ServerName1 & ";Initial Catalog=" & If(strAction = Action.BackUp, cmbDataBase1.Text, "master") & ";Integrated Security=True"
    Else
    strConn = "Data Source=" & ServerName1 & "; Initial Catalog=" & If(strAction = Action.BackUp, cmbDataBase1.Text, "master") & ";user id=" & UserID1 & ";password=" & Password1 & ";Integrated Security=false"
    End If


    Edit , here is full form1
    Code:
    Imports System.Data.SqlClient
    Imports Microsoft.SqlServer.Management.Smo
    
    Public Class Form1
        'Backup & Restore of SQL Server database using VB.NET
        Private Sub cmbDataBase1_DropDown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbDataBase1.DropDown
            Try
                cmbDataBase1.Items.Clear()
                Dim serverName As String = cmbServerName1.Text.ToString()
                Dim serverConnection As Microsoft.SqlServer.Management.Common.ServerConnection = New Microsoft.SqlServer.Management.Common.ServerConnection()
                serverConnection.ServerInstance = serverName
                serverConnection.LoginSecure = True
                If cmbAuthe1.SelectedIndex = 1 Then
                    serverConnection.LoginSecure = False
                    serverConnection.Login = txtUserName1.Text
                    serverConnection.Password = txtPassword1.Text
                End If
                Dim server As Server = New Server(serverConnection)
                Try
                    For Each database As Database In server.Databases
                        cmbDataBase1.Items.Add(database.Name)
                    Next
                Catch ex As Exception
                    Dim exception As String = ex.Message
                End Try
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
    
        Private Sub cmbServerName1_DropDown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbServerName1.DropDown
            Dim dataTable1 = SmoApplication.EnumAvailableSqlServers(False)
            cmbServerName1.ValueMember = "Name"
            cmbServerName1.DataSource = dataTable1
        End Sub
        Dim con As SqlConnection
        Dim Connect1 As Integer = 0
        Dim ServerName1 As String
        Dim UserID1 As String
        Dim Password1 As String
        Dim strConn As String
        Dim database As String
        Enum Action
            BackUp
            Restore
        End Enum
        Private Sub btnBackUp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBackUp.Click
            Execute(Action.BackUp)
        End Sub
        Private Sub btnRestore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRestore.Click
            Execute(Action.Restore)
        End Sub
        Private Sub Execute(ByVal strAction As Action)
            Dim Filename As String
            ServerName1 = cmbServerName1.Text.Trim()
            UserID1 = txtUserName1.Text.Trim()
            Password1 = txtPassword1.Text.Trim()
            database = cmbDataBase1.Text
    
            If cmbAuthe1.SelectedIndex = 0 Then
                strConn = "Data Source=" & ServerName1 & ";Initial Catalog=" & cmbDataBase1.Text & ";Integrated Security=True"
            Else
                strConn = "Data Source=" & ServerName1 & "; Initial Catalog=" & cmbDataBase1.Text & ";user id=" & UserID1 & ";password=" & Password1 & ";Integrated Security=false"
            End If
            con = New SqlConnection(strConn)
            con.Open()
            Dim strQuery As String
            If strAction = Action.BackUp Then
                Dim objdlg As New SaveFileDialog
                objdlg.FileName = database
                objdlg.ShowDialog()
                Filename = objdlg.FileName
                strQuery = "backup database " & database & " to disk='" & Filename & "'"
            Else
                Dim objdlg As New OpenFileDialog
                objdlg.FileName = database
                objdlg.ShowDialog()
                Filename = objdlg.FileName
                strQuery = "RESTORE DATABASE " & database & " FROM disk='" & Filename & "'"
            End If
    
            Dim cmd As SqlCommand
            cmd = New SqlCommand(strQuery, con)
            cmd.ExecuteNonQuery()
    
            If cmbAuthe1.SelectedIndex = 0 Then
                strConn = "Data Source=" & ServerName1 & ";Initial Catalog=" & If(strAction = Action.BackUp, cmbDataBase1.Text, "master") & ";Integrated Security=True"
            Else
                strConn = "Data Source=" & ServerName1 & "; Initial Catalog=" & If(strAction = Action.BackUp, cmbDataBase1.Text, "master") & ";user id=" & UserID1 & ";password=" & Password1 & ";Integrated Security=false"
            End If
        End Sub
    Last edited by diablo21; Feb 11th, 2014 at 01:03 PM.

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Restore database from file

    Ungh... I'm sorry... I made an assumption that turned out to be wrong.

    No... you weren't supposed to add that code... you were supposed to use it to REPLACE the connectionstring building you're doing at the top.

    as I said, you need to change your connectionstring... so that you connect to a different database... that's what it does... but if you add it to your code AFTER you try to do the restore... what good does it do?


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Re: Restore database from file

    nothing still shows cant use same database,
    i enter in the field database: master or muonline and click restore and select .bak file and always got this message dont know

  10. #10
    Addicted Member
    Join Date
    Oct 2008
    Location
    Califorina
    Posts
    235

    Re: Restore database from file

    What is the error message that the sql server is returning. Have you tried to restore the database in SMSS to make sure your tsql you're using is correct?

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Re: Restore database from file

    sql server isnt returning nothing only the program show error cant cant run the option
    need to fix this when write database name and select db to restore it normal
    Last edited by diablo21; Feb 11th, 2014 at 02:47 PM.

  12. #12
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Restore database from file

    2 things... 1) you need to make sure the backup you're restoring from actually belongs to the database you're trying to restore. 2) do NOT select master as the database in the dropdown... it should ONLY ever be part of the connectionstring.. AND NOTHING ELSE. 3) you need the FULL path to the bak file... AND it needs to BE ON THE SERVER... if you're backing up and restoring your local system, that's fine, but if you're working on a server... then the bak file needs to be on the server. 4) I can't count.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  13. #13

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Re: Restore database from file

    the back file in on the same pc..my pc..
    i write on the text field MuOnline or master still the same the same error always

    If i leave the text field empty and click restore select file and shows me this error:
    Incorrect syntax near the keyword 'FROM'. >> cmd.ExecuteNonQuery()

  14. #14
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Restore database from file

    "i write on the text field" -- what text field.. you should be selecting a database name from a dropdown.
    "or master " Gah! I said stop that... master should only ever appear in the connection string IF YOU ARE DOING a restore...
    "If i leave the text field empty and click restore select file and shows me this error:
    Incorrect syntax near the keyword 'FROM'. >> cmd.ExecuteNonQuery()" -- expected since, yes, having a blank database name would result in bad SQL.

    are you selecting a database, clicking Backup... then clicking Restore? with out doing anything else.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  15. #15
    Addicted Member
    Join Date
    Oct 2008
    Location
    Califorina
    Posts
    235

    Re: Restore database from file

    Have you looked at what your app built for strQuery? It's saying you have an error in your syntax, I would use to the debugger to grab what app have created for strQuery.

  16. #16

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Re: Restore database from file

    thats why i posted full source, but i see nobody even look it...

    Dim strQuery As String
    strQuery = "RESTORE DATABASE " & database & " FROM disk='" & Filename & "'"

  17. #17
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Restore database from file

    The only time a syntax error was mentioned was when the OP doesn't select a database to restore. Because of the way the SQL is being constructed, of course there's going to be an error... you can't restore w/o specifying the database.
    I even addressed this in my last post:
    "If i leave the text field empty and click restore select file and shows me this error:
    Incorrect syntax near the keyword 'FROM'. >> cmd.ExecuteNonQuery()" -- expected since, yes, having a blank database name would result in bad SQL.
    However, that doesn't address the problem that there is a connection open on the database when trying to do a restore. I tried to get more information about that, but if my inquiries are going to go unanswered, then I'm just wasting everyone's time and I'll move on to the next problem.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  18. #18
    Addicted Member
    Join Date
    Oct 2008
    Location
    Califorina
    Posts
    235

    Re: Restore database from file

    Quote Originally Posted by techgnome View Post
    The only time a syntax error was mentioned was when the OP doesn't select a database to restore. Because of the way the SQL is being constructed, of course there's going to be an error... you can't restore w/o specifying the database.
    I even addressed this in my last post:


    However, that doesn't address the problem that there is a connection open on the database when trying to do a restore. I tried to get more information about that, but if my inquiries are going to go unanswered, then I'm just wasting everyone's time and I'll move on to the next problem.

    -tg

    OHhh, I thought he changed to connection string to connect to master and was no getting a syntax error when trying restore the database.

  19. #19
    Addicted Member
    Join Date
    Oct 2008
    Location
    Califorina
    Posts
    235

    Re: Restore database from file

    Quote Originally Posted by diablo21 View Post
    thats why i posted full source, but i see nobody even look it...

    Dim strQuery As String
    strQuery = "RESTORE DATABASE " & database & " FROM disk='" & Filename & "'"
    That wasn't what I mean. I meant after that line of code has been executed what are the results? But since TG said the connection hasn't been corrected my post doesn't really matter until that's been fixed.

  20. #20

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Re: Restore database from file

    still the same if i even change to parametres still cant load it

    edit: or someone can change the functions for backup and restore to other code that is working ?
    Last edited by diablo21; Feb 12th, 2014 at 04:09 AM.

  21. #21
    Addicted Member
    Join Date
    Oct 2008
    Location
    Califorina
    Posts
    235

    Re: Restore database from file

    Here is a quick example I threw together.

    Code:
    Imports System.Data.SqlClient
    Imports System.Data.Sql
    
    Private Sub restoreDB()
          Dim sqlCmd As New SqlCommand
          Dim connstr As String = "Data Source=localhost\clinkdev;Initial Catalog=Master;Integrated Security=SSPI;"
          Dim dbName As String = "CLINK_REPORTING"
          Dim dbBakFile As String = "C:\temp\CLINK_REPORTING.bak"
          Dim sqlQry = "restore database " & dbName & "  from disk = '" & dbBakFile & "' WITH REPLACE"
    
          Try
             Using sqlCnn As New SqlConnection(connstr)
                With sqlCmd 'build sql command
                   .Connection = sqlCnn
                   .CommandText = sqlQry
                   .CommandType = CommandType.Text
                End With
    
                sqlCnn.Open()
                sqlCmd.ExecuteNonQuery()
             End Using
          Catch ex As Exception
             MessageBox.Show(ex.ToString)
          Finally
             sqlCmd.Dispose()
          End Try
       End Sub

  22. #22

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2009
    Posts
    524

    Re: Restore database from file

    i make it like this but got error again

    Code:
     Private Sub Execute(ByVal strAction As Action)
            Dim Filename As String
            Dim sqlCmd As New SqlCommand
            ServerName1 = cmbServerName1.Text.Trim()
            UserID1 = txtUserName1.Text.Trim()
            Password1 = txtPassword1.Text.Trim()
            database = cmbDataBase1.Text
    
            If cmbAuthe1.SelectedIndex = 0 Then
                strConn = "Data Source=" & ServerName1 & ";Initial Catalog=" & cmbDataBase1.Text & ";Integrated Security=True"
            Else
                strConn = "Data Source=" & ServerName1 & "; Initial Catalog=" & cmbDataBase1.Text & ";user id=" & UserID1 & ";password=" & Password1 & ";Integrated Security=false"
            End If
            con = New SqlConnection(strConn)
            con.Open()
            Dim strQuery As String
            If strAction = Action.BackUp Then
                Dim objdlg As New SaveFileDialog
                objdlg.FileName = database
                objdlg.ShowDialog()
                Filename = objdlg.FileName
                strQuery = "backup database " & database & " to disk='" & Filename & "'"
            Else
                Dim objdlg As New OpenFileDialog
                objdlg.FileName = database
                objdlg.ShowDialog()
                Filename = objdlg.FileName
                strQuery = "RESTORE DATABASE " & database & " FROM disk='" & Filename & "' WITH REPLACE"
            End If
    
            Try
                Using sqlCnn As New SqlConnection(strConn)
                    With sqlCmd 'build sql command
                        .Connection = sqlCnn
                        .CommandText = strQuery
                        .CommandType = CommandType.Text
                    End With
    
                    sqlCnn.Open()
                    sqlCmd.ExecuteNonQuery()
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            Finally
                sqlCmd.Dispose()
            End Try
    
        End Sub

  23. #23
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Restore database from file

    I'm going to ask one more time... I'm willing to continue with the thread if you can answer it. If not, then I'm done.

    Quote Originally Posted by me

    are you selecting a database, clicking Backup... then clicking Restore? with out doing anything else.
    To further expand on that, is there ANYONE ELSE using this database? Or are you the only user?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width