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 & _
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
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 & _
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 & _
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?
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.
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.
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.
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 & _
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.
Last edited by BrailleSchool; Jan 8th, 2011 at 12:10 AM.