Results 1 to 7 of 7

Thread: Am I able to improve this code?

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Apr 2005
    Location
    Debug.Print
    Posts
    3,885

    Am I able to improve this code?

    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:
    1. Private Sub RibbonGroup_SupportSpaceTools_DatabaseManagement_BackupDatabase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RibbonGroup_SupportSpaceTools_DatabaseManagement_BackupDatabase.Click
    2.         With diagBackupDB
    3.             .AddExtension = True
    4.             .CheckFileExists = True
    5.             .CheckPathExists = True
    6.             .CreatePrompt = True
    7.             .DefaultExt = "bak"
    8.             .DereferenceLinks = True
    9.             .FileName = "DatabaseBackup"
    10.             .Filter = "BAK Files|*.bak"
    11.             .FilterIndex = 1
    12.             .InitialDirectory = "C:\"
    13.             .OverwritePrompt = True
    14.             .RestoreDirectory = True
    15.             .Title = ProductName & ": Database Backup"
    16.             .ValidateNames = True
    17.             .ShowDialog()
    18.  
    19.             If CBool(Windows.Forms.DialogResult.OK) Then
    20.                 Try
    21.                     Using SetDatabaseConnection As New SqlConnection(GetDatabaseConnectionString)
    22.                         'Specify the SQL query to backup the database.
    23.                         Dim sSQL As String = "BACKUP DATABASE SupportSpace" & vbNewLine & _
    24.                                              "TO DISK = " & "'" & .FileName.ToString & "'" & vbNewLine & _
    25.                                              "WITH FORMAT"
    26.  
    27.                         Dim sSQLCommand As New SqlCommand
    28.  
    29.                         SetDatabaseConnection.Open()
    30.  
    31.                         With sSQLCommand
    32.                             .Connection = SetDatabaseConnection
    33.                             .CommandText = sSQL
    34.                             .ExecuteNonQuery()
    35.                         End With
    36.  
    37.                         SetDatabaseConnection.Close()
    38.                     End Using
    39.  
    40.                     'Everything went ok, advise the end user.
    41.                     MessageBox.Show("The database: SupportSpace was successfully backed up to: " & vbNewLine & vbNewLine & _
    42.                                     .FileName.ToString, _
    43.                                     "Database Backed Up", _
    44.                                     MessageBoxButtons.OK, _
    45.                                     MessageBoxIcon.Information, _
    46.                                     MessageBoxDefaultButton.Button1)
    47.                 Catch ex As InvalidOperationException
    48.                     MessageBox.Show(ex.Message, _
    49.                                     "Invalid Operation Exception", _
    50.                                     MessageBoxButtons.OK, _
    51.                                     MessageBoxIcon.Exclamation, _
    52.                                     MessageBoxDefaultButton.Button1)
    53.                 Catch ex As SqlException
    54.                     MessageBox.Show(ex.Message, _
    55.                                     "SQL Exception", _
    56.                                     MessageBoxButtons.OK, _
    57.                                     MessageBoxIcon.Exclamation, _
    58.                                     MessageBoxDefaultButton.Button1)
    59.                 End Try
    60.             End If
    61.         End With
    62.     End Sub

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,943

    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
    My usual boring signature: Nothing

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Apr 2005
    Location
    Debug.Print
    Posts
    3,885

    Re: Am I able to improve this code?

    Ok, I am now using the following to backup the SQL 2005 database:
    vb Code:
    1. Private Sub RibbonGroup_SupportSpaceTools_DatabaseManagement_BackupDatabase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RibbonGroup_SupportSpaceTools_DatabaseManagement_BackupDatabase.Click
    2.         With diagBackupDB
    3.             .AddExtension = True
    4.             .CheckFileExists = False
    5.             .CheckPathExists = True
    6.             .CreatePrompt = True
    7.             .DefaultExt = "bak"
    8.             .DereferenceLinks = True
    9.             .FileName = "DatabaseBackup"
    10.             .Filter = "BAK Files|*.bak"
    11.             .FilterIndex = 1
    12.             .InitialDirectory = "C:\"
    13.             .OverwritePrompt = True
    14.             .RestoreDirectory = True
    15.             .Title = ProductName & ": Database Backup"
    16.             .ValidateNames = True
    17.  
    18.             If .ShowDialog = Windows.Forms.DialogResult.OK Then
    19.                 Try
    20.                     Using SetDatabaseConnection As New SqlConnection(GetDatabaseConnectionString)
    21.                         'Specify the SQL query to backup the database.
    22.                         Dim sSQL As String = "BACKUP DATABASE SupportSpace" & vbNewLine & _
    23.                                              "TO DISK = " & "'" & .FileName.ToString & "'" & vbNewLine & _
    24.                                              "WITH FORMAT;"
    25.  
    26.                         Dim sSQLCommand As New SqlCommand
    27.  
    28.                         SetDatabaseConnection.Open()
    29.  
    30.                         With sSQLCommand
    31.                             .Connection = SetDatabaseConnection
    32.                             .CommandText = sSQL
    33.                             .ExecuteNonQuery()
    34.                         End With
    35.  
    36.                         SetDatabaseConnection.Close()
    37.                     End Using
    38.  
    39.                     'Everything went ok, advise the end user.
    40.                     MessageBox.Show("The database: SupportSpace was successfully backed up to: " & vbNewLine & vbNewLine & _
    41.                                     .FileName.ToString, _
    42.                                     "Database Backed Up", _
    43.                                     MessageBoxButtons.OK, _
    44.                                     MessageBoxIcon.Information, _
    45.                                     MessageBoxDefaultButton.Button1)
    46.                 Catch ex As InvalidOperationException
    47.                     MessageBox.Show(ex.Message, _
    48.                                     "Invalid Operation Exception", _
    49.                                     MessageBoxButtons.OK, _
    50.                                     MessageBoxIcon.Exclamation, _
    51.                                     MessageBoxDefaultButton.Button1)
    52.                 Catch ex As SqlException
    53.                     MessageBox.Show(ex.Message, _
    54.                                     "SQL Exception", _
    55.                                     MessageBoxButtons.OK, _
    56.                                     MessageBoxIcon.Exclamation, _
    57.                                     MessageBoxDefaultButton.Button1)
    58.                 End Try
    59.             End If
    60.         End With
    61.     End Sub

    and I am using the following code to restore the backup file
    vb Code:
    1. Private Sub RibbonGroup_SupportSpaceTools_DatabaseManagement_RestoreDatabase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RibbonGroup_SupportSpaceTools_DatabaseManagement_RestoreDatabase.Click
    2.         With diagRestoreDB
    3.             .AddExtension = True
    4.             .CheckFileExists = True
    5.             .CheckPathExists = True
    6.             .DefaultExt = "bak"
    7.             .DereferenceLinks = True
    8.             .FileName = "DatabaseBackup"
    9.             .Filter = "BAK Files|*.bak"
    10.             .FilterIndex = 1
    11.             .InitialDirectory = "C:\"
    12.             .Multiselect = False
    13.             .ReadOnlyChecked = False
    14.             .RestoreDirectory = True
    15.             .ShowReadOnly = False
    16.             .Title = ProductName & ": Database Restore"
    17.             .ValidateNames = False
    18.  
    19.             If .ShowDialog = Windows.Forms.DialogResult.OK Then
    20.                 Try
    21.                     Using SetDatabaseConnection As New SqlConnection(GetDatabaseConnectionString)
    22.                         'Specify the SQL query to backup the database.
    23.                         Dim sSQL As String = "USE master;" & vbNewLine & _
    24.                                              "RESTORE DATABASE SupportSpace" & vbNewLine & _
    25.                                              "FROM DISK = " & "'" & .FileName.ToString & "'"
    26.  
    27.                         Dim sSQLCommand As New SqlCommand
    28.  
    29.                         SetDatabaseConnection.Open()
    30.  
    31.                         With sSQLCommand
    32.                             .Connection = SetDatabaseConnection
    33.                             .CommandText = sSQL
    34.                             .ExecuteNonQuery()
    35.                         End With
    36.  
    37.                         SetDatabaseConnection.Close()
    38.                     End Using
    39.  
    40.                     'Everything went ok, advise the end user.
    41.                     MessageBox.Show("The database: SupportSpace was successfully restored from: " & vbNewLine & vbNewLine & _
    42.                                     .FileName.ToString, _
    43.                                     "Database Restored", _
    44.                                     MessageBoxButtons.OK, _
    45.                                     MessageBoxIcon.Information, _
    46.                                     MessageBoxDefaultButton.Button1)
    47.                 Catch ex As InvalidOperationException
    48.                     MessageBox.Show(ex.Message, _
    49.                                     "Invalid Operation Exception", _
    50.                                     MessageBoxButtons.OK, _
    51.                                     MessageBoxIcon.Exclamation, _
    52.                                     MessageBoxDefaultButton.Button1)
    53.                 Catch ex As SqlException
    54.                     MessageBox.Show(ex.Message, _
    55.                                     "SQL Exception", _
    56.                                     MessageBoxButtons.OK, _
    57.                                     MessageBoxIcon.Exclamation, _
    58.                                     MessageBoxDefaultButton.Button1)
    59.                 End Try
    60.             End If
    61.         End With
    62.     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?
    Attached Images Attached Images  

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Am I able to improve this code?

    Can you give pointers in plain english?
    I don't really see how this is not plain English:
    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.

  5. #5

    Thread Starter
    PowerPoster
    Join Date
    Apr 2005
    Location
    Debug.Print
    Posts
    3,885

    Re: Am I able to improve this code?

    Quote Originally Posted by jmcilhinney View Post
    I don't really see how this is not plain Englishoes 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.

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Am I able to improve this code?

    Quote Originally Posted by BrailleSchool View Post
    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>.

  7. #7

    Thread Starter
    PowerPoster
    Join Date
    Apr 2005
    Location
    Debug.Print
    Posts
    3,885

    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:
    1. Private Sub RibbonGroup_SupportSpaceTools_DatabaseManagement_BackupDatabase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RibbonGroup_SupportSpaceTools_DatabaseManagement_BackupDatabase.Click
    2.         With diagBackupDB
    3.             .AddExtension = True
    4.             .CheckFileExists = False
    5.             .CheckPathExists = True
    6.             .CreatePrompt = True
    7.             .DefaultExt = "bak"
    8.             .DereferenceLinks = True
    9.             .FileName = "DatabaseBackup"
    10.             .Filter = "BAK Files|*.bak"
    11.             .FilterIndex = 1
    12.             .InitialDirectory = "C:\"
    13.             .OverwritePrompt = True
    14.             .RestoreDirectory = True
    15.             .Title = ProductName & ": Database Backup"
    16.             .ValidateNames = True
    17.  
    18.             If .ShowDialog = Windows.Forms.DialogResult.OK Then
    19.                 Try
    20.                     Using SetDatabaseConnection As New SqlConnection(GetDatabaseConnectionString)
    21.                         'Specify the SQL query to backup the database.
    22.                         Dim sSQL As String = "USE master;" & vbNewLine & _
    23.                                              "ALTER DATABASE SupportSpace SET RECOVERY FULL;" & _
    24.                                              "BACKUP DATABASE SupportSpace TO DISK = " & "'" & .FileName.ToString & "'" & "WITH FORMAT;" & vbNewLine & _
    25.                                              "BACKUP LOG SupportSpace TO DISK = " & "'" & .FileName.ToString & "'"
    26.  
    27.                         Dim sSQLCommand As New SqlCommand
    28.  
    29.                         SetDatabaseConnection.Open()
    30.  
    31.                         With sSQLCommand
    32.                             .Connection = SetDatabaseConnection
    33.                             .CommandText = sSQL
    34.                             .ExecuteNonQuery()
    35.                         End With
    36.  
    37.                         SetDatabaseConnection.Close()
    38.                     End Using
    39.                 Catch ex As InvalidOperationException
    40.                     MessageBox.Show(ex.Message, _
    41.                                     "Invalid Operation Exception", _
    42.                                     MessageBoxButtons.OK, _
    43.                                     MessageBoxIcon.Exclamation, _
    44.                                     MessageBoxDefaultButton.Button1)
    45.                 Catch ex As SqlException
    46.                     MessageBox.Show(ex.Message, _
    47.                                     "SQL Exception", _
    48.                                     MessageBoxButtons.OK, _
    49.                                     MessageBoxIcon.Exclamation, _
    50.                                     MessageBoxDefaultButton.Button1)
    51.                 End Try
    52.             End If
    53.         End With
    54.     End Sub

    vb Code:
    1. Private Sub RibbonGroup_SupportSpaceTools_DatabaseManagement_RestoreDatabase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RibbonGroup_SupportSpaceTools_DatabaseManagement_RestoreDatabase.Click
    2.         If MessageBox.Show("You are about to restore the " & ProductName & " database.  Please be advised that if you click on the " & _
    3.                            "Yes button, the database restore will start and the restoration will completely overwrite the current database.  " & _
    4.                            "Any updates or changes you have made to the current database on any date after the last backup will be lost.  " & _
    5.                            "Once the overwrite has been completed, the changes made by the database restore cannot be reversed.  Please " & _
    6.                            "click Yes to continue or click No to cancel the restore.", _
    7.                            "Database Restoration Confirmation", _
    8.                            MessageBoxButtons.YesNo, _
    9.                            MessageBoxIcon.Warning, _
    10.                            MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.Yes Then
    11.  
    12.             'Yes was clicked on by the end user, restore the database.
    13.             With diagRestoreDB
    14.                 .AddExtension = True
    15.                 .CheckFileExists = True
    16.                 .CheckPathExists = True
    17.                 .DefaultExt = "bak"
    18.                 .DereferenceLinks = True
    19.                 .FileName = "DatabaseBackup"
    20.                 .Filter = "BAK Files|*.bak"
    21.                 .FilterIndex = 1
    22.                 .InitialDirectory = "C:\"
    23.                 .Multiselect = False
    24.                 .ReadOnlyChecked = False
    25.                 .RestoreDirectory = True
    26.                 .ShowReadOnly = False
    27.                 .Title = ProductName & ": Database Restore"
    28.                 .ValidateNames = False
    29.  
    30.                 If .ShowDialog = Windows.Forms.DialogResult.OK Then
    31.                     Try
    32.                         Using SetDatabaseConnection As New SqlConnection(GetDatabaseConnectionString)
    33.                             'Specify the SQL query to backup the database.
    34.                             Dim sSQL As String = "USE master;" & vbNewLine & _
    35.                                                  "RESTORE DATABASE SupportSpace" & vbNewLine & _
    36.                                                  "FROM DISK = " & "'" & .FileName.ToString & "'" & "WITH REPLACE"
    37.  
    38.                             Dim sSQLCommand As New SqlCommand
    39.  
    40.                             SetDatabaseConnection.Open()
    41.  
    42.                             With sSQLCommand
    43.                                 .Connection = SetDatabaseConnection
    44.                                 .CommandText = sSQL
    45.                                 .ExecuteNonQuery()
    46.                             End With
    47.  
    48.                             SetDatabaseConnection.Close()
    49.                         End Using
    50.                     Catch ex As InvalidOperationException
    51.                         MessageBox.Show(ex.Message, _
    52.                                         "Invalid Operation Exception", _
    53.                                         MessageBoxButtons.OK, _
    54.                                         MessageBoxIcon.Exclamation, _
    55.                                         MessageBoxDefaultButton.Button1)
    56.                     Catch ex As SqlException
    57.                         MessageBox.Show(ex.Message, _
    58.                                         "SQL Exception", _
    59.                                         MessageBoxButtons.OK, _
    60.                                         MessageBoxIcon.Exclamation, _
    61.                                         MessageBoxDefaultButton.Button1)
    62.                     End Try
    63.                 End If
    64.             End With
    65.         Else 'No
    66.             MessageBox.Show("The database restoration successfully canceled.  No database modifications were completed.", _
    67.                             "Database Restore Successfully Canceled", _
    68.                             MessageBoxButtons.OK, _
    69.                             MessageBoxIcon.Information, _
    70.                             MessageBoxDefaultButton.Button1)
    71.         End If
    72.     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.

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