Am I able to improve this code?

Printable View

Hi,

I thought when you use "using", it opens and closes the database connection when needed automatically. I am not sure if I am doing this right but would like to know if you can advise if it can be improved. I am able to create the BAK file without issue and I do not get any errors. Thanks.

vb Code:

Private Sub RibbonGroup_SupportSpaceTools_DatabaseManagement_BackupDatabase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RibbonGroup_SupportSpaceTools_DatabaseManagement_BackupDatabase.Click
        With diagBackupDB
            .AddExtension = True
            .CheckFileExists = True
            .CheckPathExists = True
            .CreatePrompt = True
            .DefaultExt = "bak"
            .DereferenceLinks = True
            .FileName = "DatabaseBackup"
            .Filter = "BAK Files|*.bak"
            .FilterIndex = 1
            .InitialDirectory = "C:\"
            .OverwritePrompt = True
            .RestoreDirectory = True
            .Title = ProductName & ": Database Backup"
            .ValidateNames = True
            .ShowDialog()
 
            If CBool(Windows.Forms.DialogResult.OK) Then
                Try
                    Using SetDatabaseConnection As New SqlConnection(GetDatabaseConnectionString)
                        'Specify the SQL query to backup the database.
                        Dim sSQL As String = "BACKUP DATABASE SupportSpace" & vbNewLine & _
                                             "TO DISK = " & "'" & .FileName.ToString & "'" & vbNewLine & _
                                             "WITH FORMAT"
 
                        Dim sSQLCommand As New SqlCommand
 
                        SetDatabaseConnection.Open()
 
                        With sSQLCommand
                            .Connection = SetDatabaseConnection
                            .CommandText = sSQL
                            .ExecuteNonQuery()
                        End With
 
                        SetDatabaseConnection.Close()
                    End Using
 
                    'Everything went ok, advise the end user.
                    MessageBox.Show("The database: SupportSpace was successfully backed up to: " & vbNewLine & vbNewLine & _
                                    .FileName.ToString, _
                                    "Database Backed Up", _
                                    MessageBoxButtons.OK, _
                                    MessageBoxIcon.Information, _
                                    MessageBoxDefaultButton.Button1)
                Catch ex As InvalidOperationException
                    MessageBox.Show(ex.Message, _
                                    "Invalid Operation Exception", _
                                    MessageBoxButtons.OK, _
                                    MessageBoxIcon.Exclamation, _
                                    MessageBoxDefaultButton.Button1)
                Catch ex As SqlException
                    MessageBox.Show(ex.Message, _
                                    "SQL Exception", _
                                    MessageBoxButtons.OK, _
                                    MessageBoxIcon.Exclamation, _
                                    MessageBoxDefaultButton.Button1)
                End Try
            End If
        End With
    End Sub

Jan 7th, 2011, 06:54 PM
Shaggy Hiker

Re: Am I able to improve this code?

This is not going to do what you think it is going to do:

If CBool(Windows.Forms.DialogResult.OK) Then

All you are doing is taking a constant, casting it to a boolean, and switching on it. More likely, you wanted to look at the return from the ShowDialog statement:

If diagBackupDB.ShowDialog = Windows.Forms.DialogResult.OK

1 Attachment(s)

Re: Am I able to improve this code?

Ok, I am now using the following to backup the SQL 2005 database:

vb Code:

Private Sub RibbonGroup_SupportSpaceTools_DatabaseManagement_BackupDatabase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RibbonGroup_SupportSpaceTools_DatabaseManagement_BackupDatabase.Click
        With diagBackupDB
            .AddExtension = True
            .CheckFileExists = False
            .CheckPathExists = True
            .CreatePrompt = True
            .DefaultExt = "bak"
            .DereferenceLinks = True
            .FileName = "DatabaseBackup"
            .Filter = "BAK Files|*.bak"
            .FilterIndex = 1
            .InitialDirectory = "C:\"
            .OverwritePrompt = True
            .RestoreDirectory = True
            .Title = ProductName & ": Database Backup"
            .ValidateNames = True
 
            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                Try
                    Using SetDatabaseConnection As New SqlConnection(GetDatabaseConnectionString)
                        'Specify the SQL query to backup the database.
                        Dim sSQL As String = "BACKUP DATABASE SupportSpace" & vbNewLine & _
                                             "TO DISK = " & "'" & .FileName.ToString & "'" & vbNewLine & _
                                             "WITH FORMAT;"
 
                        Dim sSQLCommand As New SqlCommand
 
                        SetDatabaseConnection.Open()
 
                        With sSQLCommand
                            .Connection = SetDatabaseConnection
                            .CommandText = sSQL
                            .ExecuteNonQuery()
                        End With
 
                        SetDatabaseConnection.Close()
                    End Using
 
                    'Everything went ok, advise the end user.
                    MessageBox.Show("The database: SupportSpace was successfully backed up to: " & vbNewLine & vbNewLine & _
                                    .FileName.ToString, _
                                    "Database Backed Up", _
                                    MessageBoxButtons.OK, _
                                    MessageBoxIcon.Information, _
                                    MessageBoxDefaultButton.Button1)
                Catch ex As InvalidOperationException
                    MessageBox.Show(ex.Message, _
                                    "Invalid Operation Exception", _
                                    MessageBoxButtons.OK, _
                                    MessageBoxIcon.Exclamation, _
                                    MessageBoxDefaultButton.Button1)
                Catch ex As SqlException
                    MessageBox.Show(ex.Message, _
                                    "SQL Exception", _
                                    MessageBoxButtons.OK, _
                                    MessageBoxIcon.Exclamation, _
                                    MessageBoxDefaultButton.Button1)
                End Try
            End If
        End With
    End Sub

and I am using the following code to restore the backup file

vb Code:

Private Sub RibbonGroup_SupportSpaceTools_DatabaseManagement_RestoreDatabase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RibbonGroup_SupportSpaceTools_DatabaseManagement_RestoreDatabase.Click
        With diagRestoreDB
            .AddExtension = True
            .CheckFileExists = True
            .CheckPathExists = True
            .DefaultExt = "bak"
            .DereferenceLinks = True
            .FileName = "DatabaseBackup"
            .Filter = "BAK Files|*.bak"
            .FilterIndex = 1
            .InitialDirectory = "C:\"
            .Multiselect = False
            .ReadOnlyChecked = False
            .RestoreDirectory = True
            .ShowReadOnly = False
            .Title = ProductName & ": Database Restore"
            .ValidateNames = False
 
            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                Try
                    Using SetDatabaseConnection As New SqlConnection(GetDatabaseConnectionString)
                        'Specify the SQL query to backup the database.
                        Dim sSQL As String = "USE master;" & vbNewLine & _
                                             "RESTORE DATABASE SupportSpace" & vbNewLine & _
                                             "FROM DISK = " & "'" & .FileName.ToString & "'"
 
                        Dim sSQLCommand As New SqlCommand
 
                        SetDatabaseConnection.Open()
 
                        With sSQLCommand
                            .Connection = SetDatabaseConnection
                            .CommandText = sSQL
                            .ExecuteNonQuery()
                        End With
 
                        SetDatabaseConnection.Close()
                    End Using
 
                    'Everything went ok, advise the end user.
                    MessageBox.Show("The database: SupportSpace was successfully restored from: " & vbNewLine & vbNewLine & _
                                    .FileName.ToString, _
                                    "Database Restored", _
                                    MessageBoxButtons.OK, _
                                    MessageBoxIcon.Information, _
                                    MessageBoxDefaultButton.Button1)
                Catch ex As InvalidOperationException
                    MessageBox.Show(ex.Message, _
                                    "Invalid Operation Exception", _
                                    MessageBoxButtons.OK, _
                                    MessageBoxIcon.Exclamation, _
                                    MessageBoxDefaultButton.Button1)
                Catch ex As SqlException
                    MessageBox.Show(ex.Message, _
                                    "SQL Exception", _
                                    MessageBoxButtons.OK, _
                                    MessageBoxIcon.Exclamation, _
                                    MessageBoxDefaultButton.Button1)
                End Try
            End If
        End With
    End Sub

When I backup, it seems to be going through and I do not get any errors. However, when I go to restore the database, I get the following error attached.

I have searched MSDN for this and I dont understand what they are trying to say. Can you give pointers in plain english?

Jan 7th, 2011, 11:09 PM
jmcilhinney

Re: Am I able to improve this code?

Quote:

Can you give pointers in plain english?

I don't really see how this is not plain English:

Quote:

Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

Does the log contain work you don't want to lose or doesn't it? If it does then change your BACKUP statement as the error message tells you to. If it doesn't then you can just overwrite it, so change your RESTORE statement as the error message tells you to. That's actually one of the clearest error messages I've seen.

Have you ever restored a backup in SQL Serever Management Studio? Have you selected the Options tab and checked the Overwrite box? That is the visual equivalent of use WITH REPLACE.
Jan 7th, 2011, 11:17 PM
BrailleSchool

Re: Am I able to improve this code?

Quote:

Originally Posted by jmcilhinney

I don't really see how this is not plain English:Does the log contain work you don't want to lose or doesn't it? If it does then change your BACKUP statement as the error message tells you to. If it doesn't then you can just overwrite it, so change your RESTORE statement as the error message tells you to. That's actually one of the clearest error messages I've seen.

Have you ever restored a backup in SQL Serever Management Studio? Have you selected the Options tab and checked the Overwrite box? That is the visual equivalent of use WITH REPLACE.

I have been attempting to update the SQL and regardless of what I try, I get errors. It's not liking it. I'll keep at it and see what I can do.
Jan 7th, 2011, 11:37 PM
jmcilhinney

Re: Am I able to improve this code?

Quote:

Originally Posted by BrailleSchool

I have been attempting to update the SQL and regardless of what I try, I get errors. It's not liking it. I'll keep at it and see what I can do.

We might be able to help, but if we don't know what you tried and what happened when you tried it then we don't know what you're doing wrong.

Here's the SSBO topic for the BACKUP statement:

http://msdn.microsoft.com/en-au/libr...QL.100%29.aspx

As you can see, 'BACKUP LOG WITH' is included in the syntax. That said, I would think that you would want to overwrite the existing log with the backup when you you restore.

Here's the SSBO topic for the RESTORE statement:

http://msdn.microsoft.com/en-au/libr...QL.100%29.aspx

As you can see, 'RESTORE WITH' is included in the syntax and REPLACE is included in the <general_WITH_options>.

Re: Am I able to improve this code?

I have been tinkering with the code and I had a few problems along the way. I backed up my database using Management Studio, then deleted the database and recreated a blank one. I then took my backup and went to restore it. It worked without a problem it looks like. Everything is in there. FKs, Diagram, Tables. Everything!

The code that I have currently is below:

vb Code:

Private Sub RibbonGroup_SupportSpaceTools_DatabaseManagement_BackupDatabase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RibbonGroup_SupportSpaceTools_DatabaseManagement_BackupDatabase.Click
        With diagBackupDB
            .AddExtension = True
            .CheckFileExists = False
            .CheckPathExists = True
            .CreatePrompt = True
            .DefaultExt = "bak"
            .DereferenceLinks = True
            .FileName = "DatabaseBackup"
            .Filter = "BAK Files|*.bak"
            .FilterIndex = 1
            .InitialDirectory = "C:\"
            .OverwritePrompt = True
            .RestoreDirectory = True
            .Title = ProductName & ": Database Backup"
            .ValidateNames = True
 
            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                Try
                    Using SetDatabaseConnection As New SqlConnection(GetDatabaseConnectionString)
                        'Specify the SQL query to backup the database.
                        Dim sSQL As String = "USE master;" & vbNewLine & _
                                             "ALTER DATABASE SupportSpace SET RECOVERY FULL;" & _
                                             "BACKUP DATABASE SupportSpace TO DISK = " & "'" & .FileName.ToString & "'" & "WITH FORMAT;" & vbNewLine & _
                                             "BACKUP LOG SupportSpace TO DISK = " & "'" & .FileName.ToString & "'"
 
                        Dim sSQLCommand As New SqlCommand
 
                        SetDatabaseConnection.Open()
 
                        With sSQLCommand
                            .Connection = SetDatabaseConnection
                            .CommandText = sSQL
                            .ExecuteNonQuery()
                        End With
 
                        SetDatabaseConnection.Close()
                    End Using
                Catch ex As InvalidOperationException
                    MessageBox.Show(ex.Message, _
                                    "Invalid Operation Exception", _
                                    MessageBoxButtons.OK, _
                                    MessageBoxIcon.Exclamation, _
                                    MessageBoxDefaultButton.Button1)
                Catch ex As SqlException
                    MessageBox.Show(ex.Message, _
                                    "SQL Exception", _
                                    MessageBoxButtons.OK, _
                                    MessageBoxIcon.Exclamation, _
                                    MessageBoxDefaultButton.Button1)
                End Try
            End If
        End With
    End Sub

vb Code:

Private Sub RibbonGroup_SupportSpaceTools_DatabaseManagement_RestoreDatabase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RibbonGroup_SupportSpaceTools_DatabaseManagement_RestoreDatabase.Click
        If MessageBox.Show("You are about to restore the " & ProductName & " database.  Please be advised that if you click on the " & _
                           "Yes button, the database restore will start and the restoration will completely overwrite the current database.  " & _
                           "Any updates or changes you have made to the current database on any date after the last backup will be lost.  " & _
                           "Once the overwrite has been completed, the changes made by the database restore cannot be reversed.  Please " & _
                           "click Yes to continue or click No to cancel the restore.", _
                           "Database Restoration Confirmation", _
                           MessageBoxButtons.YesNo, _
                           MessageBoxIcon.Warning, _
                           MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.Yes Then
 
            'Yes was clicked on by the end user, restore the database.
            With diagRestoreDB
                .AddExtension = True
                .CheckFileExists = True
                .CheckPathExists = True
                .DefaultExt = "bak"
                .DereferenceLinks = True
                .FileName = "DatabaseBackup"
                .Filter = "BAK Files|*.bak"
                .FilterIndex = 1
                .InitialDirectory = "C:\"
                .Multiselect = False
                .ReadOnlyChecked = False
                .RestoreDirectory = True
                .ShowReadOnly = False
                .Title = ProductName & ": Database Restore"
                .ValidateNames = False
 
                If .ShowDialog = Windows.Forms.DialogResult.OK Then
                    Try
                        Using SetDatabaseConnection As New SqlConnection(GetDatabaseConnectionString)
                            'Specify the SQL query to backup the database.
                            Dim sSQL As String = "USE master;" & vbNewLine & _
                                                 "RESTORE DATABASE SupportSpace" & vbNewLine & _
                                                 "FROM DISK = " & "'" & .FileName.ToString & "'" & "WITH REPLACE"
 
                            Dim sSQLCommand As New SqlCommand
 
                            SetDatabaseConnection.Open()
 
                            With sSQLCommand
                                .Connection = SetDatabaseConnection
                                .CommandText = sSQL
                                .ExecuteNonQuery()
                            End With
 
                            SetDatabaseConnection.Close()
                        End Using
                    Catch ex As InvalidOperationException
                        MessageBox.Show(ex.Message, _
                                        "Invalid Operation Exception", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Exclamation, _
                                        MessageBoxDefaultButton.Button1)
                    Catch ex As SqlException
                        MessageBox.Show(ex.Message, _
                                        "SQL Exception", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Exclamation, _
                                        MessageBoxDefaultButton.Button1)
                    End Try
                End If
            End With
        Else 'No
            MessageBox.Show("The database restoration successfully canceled.  No database modifications were completed.", _
                            "Database Restore Successfully Canceled", _
                            MessageBoxButtons.OK, _
                            MessageBoxIcon.Information, _
                            MessageBoxDefaultButton.Button1)
        End If
    End Sub

I would like to be able to show a progress of this backup. I believe you can use a BackgroundWorker for that, right? I'll have to check the forum for samples.