Database Backup/Restore Utility With SQL-DMO Using VB.NET

Introduction to SQL-DMO
SQL-DMO uses the Microsoft® SQL Server™ ODBC driver to connect to and communicate with instances of SQL Server.  SQL-DMO clients require the SQL Server ODBC Driver, version 3.80 or later, which ships with SQL Server 2000.  All required SQL-DMO components are installed as part of an instance of the Microsoft® SQL Server™ server or client. SQL-DMO is implemented in a single dynamic-link library (DLL). We may develop SQL-DMO applications on either a client or a server.

The following are the main declarations of SQL-DMO objects used:

Imports SQLDMO 

Dim oSQLServer As New SQLDMO.SQLServer
Dim WithEvents oBackup As New SQLDMO.Backup
Dim WithEvents oRestore As New SQLDMO.Restore

oSQLServer is used to connect to SQL Server instance, oBackup to work with the backup operation and oRestore to work with the restore operation.  The word WithEvents raises the respective PercentComplete (gives the progress) event of either oBackup or oRestore objects, when either of them is in the process of operation.

The following are the hard-coded constants used as main parameters:

Const _INSTANCE As String = "."
Const _USER As String = "sa"
Const _PWD As String = ""
Const _BACKUPFILE As String = "c:NorthwindBackup.bkp"
Const _DATABASE As String = "Northwind"

_INSTANCE specifies instance name (in this context localhost).  _USER specifies the userid to logon to instance with the respective password specified using _PWD.  The entire backup is taken into an incremental backup file (every backup gets added to an already existing backup, but within that same file) specified using _BACKUPFILE.  The restore process gets the latest backup within the same backup file.  The backup and restore operations work on the database specified with _DATABASE.

The following is the code to work with the Backup process using SQL-DMO:

Private Sub doBackup()
        With oBackup
            '.Devices = "[NorthwindBackup]"
            .Files = _BACKUPFILE
            .Database = _DATABASE
            .BackupSetName = "MyNorthwindBkp"
            .BackupSetDescription = "Backup from VB.NET application"
            oSQLServer.Connect(_INSTANCE, _USER, _PWD)
            .SQLBackup(oSQLServer)
            oSQLServer.DisConnect()
        End With
        MessageBox.Show("Backup Completed Sucessfully", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

I hope the steps are very simple.  Specify the Backup file name, database name, backup set name, and some description. Connect using oSQLServer object; take the backup using the SQLBackup method of
oBackup object by using the connection at oSQLServer object. Similarly, we can have the code for Restore process as follows:

Private Sub doRestore()
        With oRestore
            '.Devices = "[NorthwindBackup]"
            .Files = _BACKUPFILE
            .Database = _DATABASE
            .ReplaceDatabase = True
            oSQLServer.Connect(_INSTANCE, _USER, _PWD)
            .SQLRestore(oSQLServer)
            oSQLServer.DisConnect()
        End With
        MessageBox.Show("Restore Completed Successfully", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

NOTE: Make sure you close the SQL Server Enterprise Manager before operating the Restore process, as it maintains an exclusive connection to the database.  If you drop the existing database and try to restore it, you have no need to close it.