none
Backup & Restore: What exactly wrong with my vb.net code? RRS feed

  • Question

  • The following code backs up sql data successfully but Restoring the database is not successful. Please what's exactly wrong with the restore code. One thing i noticed is that the code runs perfectly without any error. Just that restore doesnt take effect after it says restore complete. Also, how do I add a backup and restore progress bar to it for user to see the progress of their activities. Thank you.

    'backup
    
            Dim stempel As String = (Date.Today.Day.ToString & "_" &
                                     Date.Today.Month.ToString & "_" & Date.Today.Year.ToString & "_" &
                                     Date.Now.Hour.ToString & "_" & Date.Now.Minute.ToString & "_" &
                                     Date.Now.Second.ToString & " hrs")
    
            Dim backup_name As String = stempel
            Dim backupcommand As String = "backup database [" & database & "] to Disk='" & My.Computer.FileSystem.SpecialDirectories.MyDocuments.ToString & "\NumbersBackup" & stempel & ".bak'"
    
            Dim cmd As SqlCommand = New SqlCommand(backupcommand, con)
    
            Try
                MsgBox("The Backup page will close when the Backup is done. Click OK and Wait!", vbInformation, "Backup Database")
                con.Open()
                cmd.ExecuteNonQuery()
            Catch ex As Exception
    
            End Try

    'Restore
    
            BackupOpenFileDialog.InitialDirectory = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments).ToString
            BackupOpenFileDialog.Filter = "Backup Files|*.bak"
            BackupOpenFileDialog.ShowDialog()
            lblBackupFileLocation.Text = BackupOpenFileDialog.FileName.ToString
    
            If lblBackupFileLocation.Text = "Select Backup File To Restore" Then
            Else
    
                ''Drops database
                Dim dropcommand As String = "Use [Master]; ALTER DATABASE [" & database & "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [" & database & "]"
                Dim dropcmd As SqlCommand = New SqlCommand(dropcommand, con)
    
                'Retores database
    
                Dim restorecommand As String = "Use [Master]; Restore Database [" & database & "] from Disk='" & BackupOpenFileDialog.FileName & "' with File = 1, Move 'cms' to '" & database _
                                                       & "', Move 'cms_log' to '" & databaselog & "', Replace"
    
    
                Dim cmd As SqlCommand = New SqlCommand(restorecommand, con)
    
                Try
                    MsgBox("The Restore page will close when the Restore is done. Click OK and Wait!", vbInformation, "Restore Backup")
                    con.Open()
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
    
                Finally
                    MsgBox("Restore Completed!", vbInformation, "Backup/Restore")

    Monday, November 26, 2018 6:19 PM

Answers

  • i have figured it out.

    I initially didn't execute the drop command that was why;

      Dim dropcommand As String = "Use [Master]; ALTER DATABASE [" & database & "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [" & database & "]"
                Dim dropcmd As SqlCommand = New SqlCommand(dropcommand, con)
                con.Open()
                dropcmd.ExecuteNonQuery()

    Thanks for your help

    • Marked as answer by Rans-Yaw Monday, November 26, 2018 10:27 PM
    Monday, November 26, 2018 10:11 PM

All replies

  • In order to be sure that it runs perfectly, add MsgBox(ex) between Catch and Finally. Execute again and see if it displays some message.


    Monday, November 26, 2018 6:57 PM
  • You might consider using SMO for backup and restore. The link has a full example for both operations.

    https://docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/tasks/backing-up-and-restoring-databases-and-transaction-logs?view=sql-server-2017


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, November 26, 2018 7:02 PM
    Moderator
  • Viorel, thanks.

    I had the following error message

    "Exclusive access could not be obtained because the database is in use.
    RESTORE DATABASE is terminating abnormally.
    Changed database context to 'master'."

    What can be done to solve this problem?


    • Edited by Rans-Yaw Monday, November 26, 2018 9:21 PM
    Monday, November 26, 2018 8:37 PM
  • Karen Payne, thanks.

    Reading through the link you sent. I will get back when i get stuck. 

    Thanks again.

    Monday, November 26, 2018 8:49 PM
  • i have figured it out.

    I initially didn't execute the drop command that was why;

      Dim dropcommand As String = "Use [Master]; ALTER DATABASE [" & database & "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [" & database & "]"
                Dim dropcmd As SqlCommand = New SqlCommand(dropcommand, con)
                con.Open()
                dropcmd.ExecuteNonQuery()

    Thanks for your help

    • Marked as answer by Rans-Yaw Monday, November 26, 2018 10:27 PM
    Monday, November 26, 2018 10:11 PM