Simple 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.

SQL Distributed Management Objects (SQL-DMO) is a collection of objects encapsulating the Microsoft® SQL Server™ database and replication management. SQL-DMO is a dual interface COM, in-process server implemented as a dynamic-link library (DLL). SQL-DMO encapsulates SQL Server components, presenting the attributes of the component piece to you as the properties of an object instance. We can alter the properties of the instance, or use object methods to automate SQL Server administration, including replication.

An instance of SQL Server may be viewed as a collection of components.  These components may also include several processes or services of SQL Server.  An SQL-DMO object exposes the attributes of a Microsoft® SQL Server™ 2000 component to the other applications. The referenced component may be a new or existing database object, a replication or SQL Server Agent component, or could encapsulate a SQL Server management process.

We interact with SQL-DMO objects with the help of the following members with respect to that that object:

• Properties
• Methods
• Events

As they are part of the fundamentals of dot net (or any OOPS) and not a part of SQL-DMO, I leave it to the programmer to understand their functionalities.

Before proceeding further, let us grasp a small idea about the hierarchy of SQL-DMO objects.  For quick reference, I included a huge GIF file (as part of a downloadable file) of an SQL-DMO object tree extracted from SQL Server Books On Line.  I suggest you download and open it before proceeding to the next page. (See link above near the top of this page). 

Understanding SQL-DMO Objects

If we carefully look at that object tree, the SQLServer object forms the trunk of the SQL-DMO object tree.  Every branch or sub-branch can be considered a SQL-DMO object that has a relation to the MS-SQL server component in some way.  Even though there exist several branches from that object, there are mainly three in-depth objects coming out as branches (highlighted in different colors).  They are

• Database
• JobServer
• Replication

Let us take a look at the most commonly used SQL-DMO objects.

SQLServer object contains the objects and collections that implement SQL Server administrative tasks for SQL-DMO. The object allows SQL-DMO applications to connect to an instance of SQL Server by name, establishing the context for administrative tasks.  Without this object, generally we cannot connect to an SQL Server instance at all.

Database object is a major component of the SQL-DMO object tree. This object contains collections that define the tables, stored procedures, data types, and users of a database. Methods of this object allow you to perform essential database maintenance functions, such as creating a new SQL Server database, backing up a database, and so on.

Replication object represents the entire replication system for an instance of SQL Server, and is the root of all replication objects.  It can perform all types of replications defined in SQL Server.

JobServer object exposes attributes associated with SQL Server Agent. SQL Server Agent is responsible for executing scheduled jobs and notifying operators of SQL Server error conditions or other SQL Server execution or job states.

Backup object defines a backup operation related to a SQL Server database or log.  With this object we can back up a SQL Server database or database transaction log.

Restore object defines a restore operation related to a SQL Server database or log.  With this object we can restore an entire or partial SQL Server database or database transaction log from an existing backup.

Table object exposes the attributes of a single SQL Server table. With this object we can create a SQL Server table, change an existing SQL Server table by adding or dropping columns, export data from, or import data to, an existing SQL Server table, generate a Transact-SQL script to recreate an existing SQL Server table, remove a table from a SQL Server database, and so on.

Column object represents the properties of a single column in a SQL Server table.  With this object, we can define the columns for a new SQL Server table, define a new column for an existing SQL Server table, drop an existing column from a SQL Server table, list the references of a SQL Server column, and so forth.

There exist several collections to get the existing objects from a SQL Server instance through SQL-DMO.  I suggest you refer to SQL Server books online for further information on other SQL-DMO objects.  The next page introduces you to SQL-DMO programming.

Programming SQL-DMO using .NET

As specified above, SQL-DMO comes as a COM interface (unmanaged).  It can be used with any OLE automation controller as a development platform.  Now we shall see how to work with VB.NET and SQL-DMO using Visual Studio.NET 2003 Enterprise Architect as the main objective.  Working with VC#.NET is also quite similar to the steps we will discuss now (except for the syntactical differences in programming statements).

Using Visual Studio.NET, proceed with following steps:

• Go to File >> New >> Project
• From the New Project Dialog, select Visual Basic Projects as Project Type and select Windows Application as Project Template (Figure 1).
  
  
  Figure 1
• Provide ‘SQLDMOSample’ as name of the project as shown in Figure 1. (In case of C#, select Visual C# Projects accordingly).
• Drag a button from toolbox. Go to the properties of that button and ‘name’ it as btnCheck and change ‘text’ property to Check Connectivity. 
• Go to Project Menu and click on Add Reference as in Figure 2.
  
  
  Figure 2
• Click on the COM tab, search for Microsoft SQL DMO Object Library, double-click on it and click OK as in Figure 3.  You should see SQLDMO as a reference in Solution Explorer as in Figure 4.
  
  Figure 3
  
  
  Figure 4
• In the click event of btnCheck copy the following code.  You need to change instance name, User ID and Password according to your configuration.

Private Sub btnCheck_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCheck.Click

        Dim oSQLServer As New SQLDMO.SQLServer

        oSQLServer.Connect("instanceName", "UserID", "Password")

        MessageBox.Show("Connected Successfully")

        oSQLServer.DisConnect()

        MessageBox.Show("Disconnected Successfully")

      End Sub

• Press F5 to execute and you should a see a message box “Connected Successfully” (Figure 5), followed by another message box, “Disconnected Successfully”.
  

  
  Figure 5

So, finally you tested the connectivity to SQL Server 2000 instance using SQL-DMO object SQLServer.

Backup/Restore utility using SQL-DMO with .NET

This is a very simple utility to take a backup of an entire specified database and restore it whenever needed using just two buttons, two labels and a progress bar on the windows form.  Even though I hard coded some parameters such as instance name, user id, password, file name, database name, and so on, you can further extend this utility to present a better UI so that the tool accepts everything from the user dynamically.  Make sure that my focus basically is not on UI, but on using SQL-DMO with a simple little code.  I hope you agree with me.

The utility basically contains only a single tiny windows form with the following controls on it:

• LblMessage to provide status messages like ‘Backup process started..’ etc.
• LblPercent to provide the status of process operation (backup/restore) in the form of a percentage value.
• ProgressBar1 to provide the status of a process operation (backup/restore) in a fancy way i.e., just for a better appearance.
• BtnBackup to start the process of Backup Operation.
• BtnRestore to start the process of Restore Operation

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

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 main backup or restore processes are explained on the next page.

Understanding Backup and Restore processes

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)

        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)

        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.

Remarks: The entire source code of this article exists in the form of zip downloadable (which includes the SQL-DMO object tree GIF).  I developed this small tool using Microsoft SQL Server 2000 Enterprise Edition and Visual Studio.NET 2003 Enterprise Architect.  It may also work with other SQL Server versions prior to MS SQL Server 2000, but don’t hesitate to drop in if the tool doesn’t work with other versions (basically I didn’t test it on other versions).  As SQL-DMO is a COM component, you can also use it with Visual Basic 6.0 (but with very slight syntactical changes from the above code).  You can also develop web based database administration tools using classic ASP or ASP.NET with SQL-DMO.

I leave it to the programmers for further enhancements.  Any doubts, comments, suggestions, bugs, errors or feedback are welcomed at jag_chat@yahoo.com