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.

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

graphics/note_icon.gif
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.

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

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
```

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`

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

Create the GetBackupDevices routine by entering the code in Listing 7.10 into the new module you created in How-To 7.1. The first task attempted by this routine is to connect to the server that is selected. Next, the names of the backup devices that are within the SQL Server are loaded into the list box called lstBackupDevices.

Listing 7.10 `modSQLDMORoutines.vb`: Retrieving Backup Device Names for a Given SQL Server

Public Sub GetBackupDevices(ByVal strSQLServer As String, _
                                        ByRef lstBackupDevices As ListBox)

        Dim oDevice As SQLDMO.BackupDevice

        '-- Log on to the SQL Server.
        Dim osvr As SQLDMO.SQLServer = New SQLDMO.SQLServer()

        osvr.LoginSecure = True
        osvr.Connect(strSQLServer)

        lstBackupDevices.Items.Clear()

        '-- Iterate through the backup devices.
        For Each oDevice In osvr.BackupDevices
            lstBackupDevices.Items.Add(oDevice.Name)
        Next

End Sub

On the btnBackup button, add the code in Listing 7.11 to the Click event. After connecting to the chosen SQL Server, a Backup object is instantiated. Next, certain properties of the Backup object, called oBackup, are set to values that are specified on the form. The SQLBackup method is called off the Backup object, and the connection is closed. Last, the variables are cleared (set to Nothing) and a message box is displayed.

Listing 7.11 `frmHowTo7_2.vb`: Performing the Backup

    Private Sub btnBackup_Click(ByVal sender As System.Object, _
                    ByVal e As System.EventArgs) Handles btnBackup.Click

        '-- Create a connection to the server
        Dim osvr As New SQLDMO.SQLServer()
        osvr.LoginSecure = True
        osvr.Connect(Me.lstSQLServers.SelectedItem)

        '-- Create a Backup object, and set the necessary properties
        '   based on options chosen on the form.
        Dim oBackup As New SQLDMO.Backup()

        With oBackup

            If Me.rbFull.Checked Then
                .Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database

            Else
                .Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Differential
            End If

            .BackupSetDescription = Me.txtBUSetDescription.Text
            .BackupSetName = Me.txtBUSetName.Text
            .Database = Me.lstDatabases.SelectedItem
            .Devices = "[" & Me.lstBackupDevices.SelectedItem & "]"
            If Me.rbNoLog.Checked Then
                .TruncateLog = _
                   SQLDMO.SQLDMO_BACKUP_LOG_TYPE.SQLDMOBackup_Log_NoLog
            ElseIf Me.rbNoTruncate.Checked Then
                .TruncateLog = _
                   SQLDMO.SQLDMO_BACKUP_LOG_TYPE.SQLDMOBackup_Log_NoTruncate
            Else
                .TruncateLog = _
                   SQLDMO.SQLDMO_BACKUP_LOG_TYPE.SQLDMOBackup_Log_Truncate
            End If
            .Initialize = Me.chkInitialize.Checked

            '-- Execute the backup
            .SQLBackup(osvr)

        End With

        '-- Disconnect from the server and clean up.
        osvr.DisConnect()

        osvr = Nothing
        oBackup = Nothing

        MessageBox.Show("Database Backed Up", "Task Completed", _
                          MessageBoxButtons.OK)

End Sub

Add the code in Listing 7.12 to the Click event of btnVerify. After connecting to the SQL Server, the code iterates through each of the backup devices for the server and locates the one that the form specifies.

After the specific backup device is located, the ReadBackupHeader method is called, with an SQLDMO.QueryResults object returned. Each row of the QueryResults is read, and then the information is displayed in a text box called txtVerifyDisplay. From there, the SQLServer object is disconnected.

Listing 7.12 `frmHowTo7_2.vb`: Performing the Backup

Private Sub btnVerify_Click(ByVal sender As System.Object, _
                        ByVal e As System.EventArgs) Handles btnVerify.Click

        Dim oDevice As SQLDMO.BackupDevice
        Dim oResults As SQLDMO.QueryResults

        Dim intRowCount As Integer
        Dim intColCount As Integer

        '-- Create a connection to the server
        Dim osvr As New SQLDMO.SQLServer()
        osvr.LoginSecure = True
        osvr.Connect(Me.lstSQLServers.SelectedItem)

        '-- Iterate through the devices
        For Each oDevice In osvr.BackupDevices

            If oDevice.Name = Me.lstBackupDevices.SelectedItem Then

                '-- If found, display the results
                oResults = oDevice.ReadBackupHeader

                For intRowCount = 1 To oResults.Rows

                    For intColCount = 1 To oResults.Columns

                        Me.txtVerifyDisplay.Text &= _
                          oResults.ColumnName(intColCount) & " - "

                        Me.txtVerifyDisplay.Text &= _
                          oResults.GetColumnString(intRowCount, _
intColCount) & vbCrLf & vbCrLf
                    Next
                Next
            End If
        Next

        '-- Disconnect and clean up
        osvr.DisConnect()
        osvr = Nothing
        oDevice = Nothing
        oResults = Nothing

End Sub

Add the code in Listing 7.13 to the Click event of btnClose.

Listing 7.13 `frmHowTo7_2.vb`: Performing the Backup

Private Sub btnClose_Click(ByVal sender As System.Object, _
                    ByVal e As System.EventArgs) Handles btnClose.Click

        Me.Close()

End Sub

Figure 7.7. Creating a form for backing up and verifying a SQL Database gives you control over what options the user has when performing the task.

graphics/07fig07.gif

How It Works

After the form loads, the user can select the SQL Server, database, and backup device. When the user clicks the button labeled Perform Backup, a SQLDMO Backup object is created. The Backup object gives you the same capabilities as if you were using the Enterprise Manager-except that you control them. Only put options on the form that you want to have the user set, and then set the other options yourself, as you deem necessary. After setting the options, the SQLBackup method performs the backup.

By using the QueryResults object off the BackupDevice, you have a means of looking at some of the properties of the backup and verifying them to make sure the database did indeed get backed up.

Comments

You could enhance this utility in a number of ways, a couple of which include the following:

Add a text box for letting the user specify a filename for the backup, rather than using a backup device.
Allow the user to add backup devices. Currently, only existing backup devices can be used.

Just be careful on what options you give the user so he doesn't shoot himself in the foot.

7.2 Back Up and Verify a SQL Server Database

Technique

Table 7.3. Additional SQL-DMO Objects Used for Backing Up and Verifying a Database

Steps

Table 7.4. Controls and Their Property Settings

Listing 7.8 frmHowTo7_2.vb: Calling the Routine That Loads Available SQL Servers into a List Box

Listing 7.9 frmHowTo7_2.vb: Populating the lstDatabases and lstBackupDevices List Boxes

Listing 7.10 modSQLDMORoutines.vb: Retrieving Backup Device Names for a Given SQL Server

Listing 7.11 frmHowTo7_2.vb: Performing the Backup

Listing 7.12 frmHowTo7_2.vb: Performing the Backup

Listing 7.13 frmHowTo7_2.vb: Performing the Backup