Backup and Restore a SQL Server Database - September 2010

If you download this tip please consider making a modest donation.

I recently wrote some code that will backup and restore a SQL Server 2005 database. I found some code on MSDN which I encapsulated in a couple class modules. It is contained in class modules cDoBackup.vb and cDoRestore.vb. cDoRestore.vb is actually just an alias for cDoBackup.vb and just inherits all of its functions. I expanded the code to create an application that performs those functions. It becomes the September 2010 tip-of-the-month.

The Demo Application

I wrote a VB.Net, Asp.Net, VS2008 application to demonstrate the code. The intro page:


The introduction page.

Backup - Click 'Do Backup' from the intro page to display this page. By default the AdventureWorks database is selected. Enter any valid SQL Server database name. Optionally enter connection information. Click 'Do Backup' from this page to perform the backup.


Information about the success of the backup, the name of the backup,
the folder containing the backup and some statistics about the backup is displayed.
The backup file name is constructed using the database name,
the property sFilenameBackupIdentifier, and the date and time of the backup.
sFilenameBackupIdentifier default is '_Full_'.
In this example it was set to '_SQLServer_FullBackup_'

Restore - Click 'Do Restore' from the instructions page. The following page is displayed. Enter the fully qualified name of the folder which contains the backup files. Then click the 'Find Backups' button. The default is App.Path with subfolder Backup.


Enter the fully qualified folder name which contains the backed-up database files
then click 'Find Backups' to display the following page.


This page contains a list of backed-up database files.
The list contains the backup file name, it's size and
the number of tables, columns and views contained in the database.
Click the appropriate 'Restore' link to restore the database using the selected file.
Click the appropriate 'Delete' link to immediately delete the file.


This page is displayed upon completion of the database restoration.

Methods and Properties

Method Description Example
.DoBackup Start the backup process. Dim oBackup as New cDoBackup
If oBackup.DoBackup Then
.DoRestore Start the restore process. Dim oRestore as new cDoRestore
If oRestore.DoRestore Then
Properties    
.sServerInstance The server instance. If empty use the default server instance. .ServerInstance = "Dell1705"
.sLogin The user's login ID. .sLogin = "larry"
.sPassword The user's password associated with the login ID. .sPassword = "mypassword"
.sDBName The name of the database.
If using Backup then the name of the database to be backed-up.
If using Restore then the restore process populates the property with the name of the database being restored.
.DBName = "AdventureWare"
.sPathBackup The folder into which the backup file will be stored.
Only used with Backup.
.sPathBackup = sAppPath & "Backup\"
.sBackupName During Backup the backup file name is returned in this property. The name is constructed using this format:

    nnnnnnnn_ffffffff_yyyymmdd_hhmmss.bak

Where:
    nnnnnnnn is the database name
    ffffffff is sFilenameBackupIdentifier, see below
    yyyymmdd is the date
    hhmmss is the time

A sample:

    AdventureWorks_SQLServer_FullBackup_20100902_055446.bak

During Restore the name is extracted from sFullBackupName and stored in this property.

.sFullBackupName During Backup the fully qualified file name is returned in this property. I consists of sPathBackup plus sBackupName.

During Restore this property contains the fully qualified file name of the backed-up SQL Server database.

.sFilenameBackupIdentifier The string used as part of the construction of the backup file name. The default is: _Full_ If the default is not used then the identical identifier is needed during the Backup and Restore process.

Sample:

    .sFilenameBackupIdentifier = "_SQLServer_FullBackup_"

.bWriteStatsFile = [True, False] Write a text file containing the values shown next. The file name is identical to the backup file name except the extension is .txt not .bak. Default is True. The file contains one record in the format:

    iTables:iColumns:iView

for example: 70:479:17

.bWriteStatsFile = False
.iTables The number of tables, columns and views contained in the database are reported in these properties. You can use these statistics to verify that the correct number of these objects were restored.
.iColumns
.iViews

Sample Calling Code

Use code similar to this to backup and restore a database:

Backup
Dim oBackup As New cDoBackup 'used to do the backup
With oBackup
  .sDBName = oTextboxDB.Text.Trim
  .sPathBackup = sAppPath &
"Backup\"
  .sServerInstance = ""
  .sFilenameBackupIdentifier = gsFilenameBackupIdentifier 'used to build file name
  If Not oRadioLocal.Checked Then 'default is 'default server' instance
    .sServerInstance = oTextBoxServer.Text.Trim
    .sLogin = oTextBoxUser.Text.Trim
    .sPassword = oTextBoxPassword.Text.Trim
  End If
  If
.DoBackup() Then
   
DoDisplayStats(oBackup, Me.TableMainBackupResults)
    Return True
  End
If
End
With
Restore
Dim oRestore As New cDoRestore
With oRestore
  .sServerInstance =
""
  .sDBName = sDBName
  .sFilenameBackupIdentifier = gsFilenameBackupIdentifier
'used to build file name
  If Not oRadioLocal.Checked Then 'default is 'default server' instance
    .sServerInstance = oTextBoxServer.Text.Trim
    .sLogin = oTextBoxUser.Text.Trim
    .sPassword = oTextBoxPassword.Text.Trim
 
End If
  .sFullBackupName = sFullname
  If .DoRestore() Then
    sStats = .iTables.ToString & ":" & .iColumns.ToString & ":" & .iViews.ToString
    Return True
  End
If
End
With

Download VB.Net Code

Download the source code by clicking on the zipped file name.

Download - Source Zipped File Size
VS2008 VB Solution BackupRestoreSQLServerDB.zip 80K

Previous Tip   Tip Index   Next Tip

Home

Updated 2011/04/16