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

[kremilk]

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")
                con.Close()
                con.Dispose()
            End Try

[techgnome]

Well of course it reports that it is done... 1) Your catch is empty ... what you've done is the .NET equivalent of an On Error Resume Next ... there could be an error happening but you've chosen to stuff your fingers in your ears and go "Blah blah blah" and ignore it. 2) The finally block will ALWAYS run even when there is an error, so to have the message box in there that said it completed is a red-herring.... well... maybe... but it insinuates that it completed successfully, which it may or may not have done, but you'll never know. See point #1. Move the message box to after your ExecuteNonQuery ... and add SOMETHING to your catch block so you can see if something goes wrong... and ... well... find out if something is going wrong... I bet something is.

-tg

[kremilk]

Well of course it reports that it is done... 1) Your catch is empty ... what you've done is the .NET equivalent of an On Error Resume Next ... there could be an error happening but you've chosen to stuff your fingers in your ears and go "Blah blah blah" and ignore it. 2) The finally block will ALWAYS run even when there is an error, so to have the message box in there that said it completed is a red-herring.... well... maybe... but it insinuates that it completed successfully, which it may or may not have done, but you'll never know. See point #1. Move the message box to after your ExecuteNonQuery ... and add SOMETHING to your catch block so you can see if something goes wrong... and ... well... find out if something is going wrong... I bet something is.

-tg

Thanks Techgnome, after following your instruction i had the error:
"Exclusive access could not be obtained because the database is in use".

What do you suggest i do to solve this?
Thanks.

[techgnome]

Make sure it's not in use... IE, there are no open connections to it... by anyone. You can't restore a database if it has any open connections to it. I'd suggest that if you plan to do restore operations, that when you connect to the server, you just connect to master to begin with... don't connect to the database, then do a "use master" ... if you're the only user in the database, I'd suspect that's what the issue is. doing a backup is far easier, it doesn't care about connections, but with a restore it does because it has to disconnect the db from the server in order to restore the files before reconnecting them and marking the db ready for use.

-tg

[kremilk]

Make sure it's not in use... IE, there are no open connections to it... by anyone. You can't restore a database if it has any open connections to it. I'd suggest that if you plan to do restore operations, that when you connect to the server, you just connect to master to begin with... don't connect to the database, then do a "use master" ... if you're the only user in the database, I'd suspect that's what the issue is. doing a backup is far easier, it doesn't care about connections, but with a restore it does because it has to disconnect the db from the server in order to restore the files before reconnecting them and marking the db ready for use.

-tg

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()

[kremilk]

Make sure it's not in use... IE, there are no open connections to it... by anyone. You can't restore a database if it has any open connections to it. I'd suggest that if you plan to do restore operations, that when you connect to the server, you just connect to master to begin with... don't connect to the database, then do a "use master" ... if you're the only user in the database, I'd suspect that's what the issue is. doing a backup is far easier, it doesn't care about connections, but with a restore it does because it has to disconnect the db from the server in order to restore the files before reconnecting them and marking the db ready for use.

-tg

i have figured it out. Thanks for your help.
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()