7.2 Back Up and Verify a SQL Server Database
Backing up a database is probably one of the most important features to incorporate into
your application. If you leave it up to the user to use the Enterprise Manager to back up
the database, then it won't happen. You can arrange to have the backup scheduled, but
sometimes you need to back it up at a moment's notice. This How-To shows you how to
create a custom dialog box that will allow the user not only to back up your SQL Server
database, but also to verify the backup.
Your users need to have a method for backing up and verifying their databases without
using Enterprise Manager. How do you create a dialog box that would let them back up
and verify their databases?
Technique
To accomplish this task, you will once again use the SQL-DMO objects. A couple of the
new objects you will use are the Backup and BackupDevices objects. You can see some
of the additional objects, along with the properties and methods that will be used, in
Table 7.3.
Table 7.3. Additional SQL-DMO Objects Used for Backing Up and Verifying a
Database
Object Property/Method Description
Backup Action This property allows you to specify what type
of backup that you want to take place. The
choices are SQLDMOBackup_Database,
SQLDMOBackup_Differential,
SQLDMOBackup_Files, and
SQLDMOBackup_Log.
BackupSetDescription This property allows you to specify a
description for the backup set.
BackupSetName This property is the Backup set name.
Database This property allows you to specify the
database to back up.
Devices This property specifies the devices you will be
backing up to.
Initialize This method initializes the Backup process.
TruncateLog This property allows you to specify how to
handle the truncate log when backing up.
SQLBackup This method performs the actual backup.
BackupDevices This is a collection of backup devices for a
SQL Server.
BackupDevice Name Thisis the name of a specific backup device.
ReadBackupHeader This method reads in the header information
for a backup, returning a QueryResults object.
QueryResults ColumnName This is the individual property information
about a backup.
GetColumnString This is the actual value for individual header
information.
Using the objects listed in Table 7.3, you will create a form with options for the user to
back up his database and verify the information after it has been saved.
Note
Not all the possible options will be included in the form created for this
How-To. For example, you could allow the user to back up the database
to a separate file and give him additional options for the type of backup
to perform.
Odds are good that you will not want to give the users all the options they
could have; that is one of the reasons you want to create the dialog box
here instead of letting users use the Enterprise Manager.
Steps
Open and run the VB.NET -Chapter 7 solution. From the main Windows form, click on
the command button with the caption How-To 7.2.
As with How-To 7.1, a user clicks on the SQL Server that he wants to display the
databases of. He can then choose the database and backup device. From there, the user
can click the Backup button to perform the backup. You can then click on the verify
button to have information about the backup displayed in the text box at the bottom of the
form. The form will look similar to the one displayed in Figure 7.7.
1. Create a Windows Form. Then place the controls shown in Figure 7.7, with the
following properties set as in Table 7.4.
Table 7.4. Controls and Their Property Settings
Object Property Setting
Label Name Label1
Text SQL Servers
ListBox Name lstSQLServers
Label Name Label2
Text Databases
ListBox Name lstDatabases
Label Name Label3
Text Backup Devices
ListBox Name lstBackupDevices
Command Button Name btnBackup
Text &Backup
Label Name Label4
Text Backup Set Name
TextBox Name txtBackupSetName
Text MyTestBackup
Label Name Label5
Text Options
Panel Name Panel1
Groupbox Name grpAction
Text Action
Radio Button Name rbFull
Checked True
Text Full
Radio Button Name rbIncremental
Checked False
Text Incremental
Groupbox Name grpTruncateLog
Text Truncate Log
Radio Button Name rbNoLog
Checked True
Text No Log
Radio Button Name rbNoTruncate
Checked False
Text No Truncate
Radio Button Name rbTruncate
Checked False
Text Truncate
Label Name Label5
Text Backup Set Description
TextBox Name txtBackupSetDescription
Text MyTestBackup
Checkbox Name chkInitialize
Text Initialize?
TextBox Name txtVerify
Multiline True
Scrollbars Both
Command Button Name btnVerify
Text &Verify
Command Button Name btnClose
Text &Close
2. On the form, add the code in Listing 7.8 to the Load event. This will look familiar
to How-To 7.1. For an examination of the LoadSQLServers routine, check out
step 4 in that How-To.
Listing 7.8 frmHowTo7_2.vb: Calling the Routine That Loads Available SQL
Servers into a List Box
Private Sub frmHowTo7_2_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
'-- Load up the SQL Servers
LoadSQLServers(Me.lstSQLServers)
End Sub
3. On the lstSQLServers list box, add the code in Listing 7.9 to the
SelectedIndexChanged event. This routine calls both the GetSQLDatabases,
described in step 6 of How-To 7.1, and GetBackupDevices, described in the next
step.
Listing 7.9 frmHowTo7_2.vb: Populating the lstDatabases and
lstBackupDevices List Boxes
Private Sub lstSQLServers_SelectedIndexChanged(ByVal sender As
System.Object,
ByVal e As System.EventArgs) _
Handles lstSQLServers.SelectedIndexChanged
GetSQLDatabases(Me.lstSQLServers.SelectedItem, Me.lstDatabases)
GetBackupDevices(Me.lstSQLServers.SelectedItem, Me.lstBackupDevices)
End Sub
