ASP 101 - Active Server Pages 101 - Web05
The Place ASP Developers Go!

Please visit our partners

Windows Technology Windows Technology
15 Seconds
ASP 101
ASP Wire
VB Forums
VB Wire
internet.commerce internet.commerce
Partners & Affiliates

ASP 101 is an site
ASP 101 is an site
Internet News
Small Business
Personal Technology

Corporate Info
Tech Jobs
E-mail Offers

ASP 101 News Flash ASP 101 News Flash

 Top ASP 101 Stories Top ASP 101 Stories
The Top 10 ASP Links @
What is and Why Do I Need It?
An Overview of ASP.NET

Query your database with ADO
Show All Tips >>
ASP 101 RSS Feed ASP 101 Updates

Using SQL-DMO To Backup and Verify a Database

by Carvin Wilson


The SQL Distributed Management objects (SQL-DMO) extend the functionality of SQL Server by providing developers a way to execute common tasks using programming and scripting languages. The first article in this series entitled "Using the SQL Distributed Management Objects", provided an overview of SQL-DMO. This article expands upon those concepts by showing you how to use SQL-DMO to create an Active Server Page application to backup and verify a SQL Server database.

What You Will Need

You will need knowledge of how to backup a SQL Server database. Additionally make a reference to the SQL-DMO library in your global.asa file. The below snippet is a reference for SQL Server 2000.

<!--METADATA TYPE="TypeLib" NAME="Microsoft SQLDMO Object Library" UUID="{10010001-E260-11CF-AE68-00AA004A34D5}" VERSION="8.0"-->

The sample application and code snippets will work for SQL 7.0, MSDE, and SQL Server 2000.

Joy and Pain

Working with the SQL-DMO object can be both a joy and pain. It provides you with so much rich functionality it often becomes confusing trying to figure out what properties and methods you really need. On that note, I'm only going to discuss the objects, properties, and methods used within the sample application. You can find unlimited information and code samples in the SQL Server books on-line. You can also download the sample application that accompanies this article. It provides ASP code for all topics covered below.


The code for the SQLDMO.SQLServer object should look very familiar. It allows you to connect to a given SQL server using either SQL Server on Windows NT authentication. Take a look at the snippet below.

  Dim srv
  Set srv = Server.CreateObject("SQLDMO.SQLServer")
  srv.LoginTimeout = 15
  srv.Connect "servername", "username", "password"

This code creates a connection to SQL Server using SQL Server authentication. To login using NT authentication set the LoginSecure property to TRUE. This will cause the username and password parameters to be ignored and your NT login information will be used.


We will use the SQLDMO.Database object to get a list of databases from the server. This information is used to complete a backup request form in the sample application. The snippet below demonstrates using this object to populate a combo box.

  Dim srv
  Dim objDB
  Set srv = Server.CreateObject("SQLDMO.SQLServer")
  srv.LoginTimeout = 15 
  srv.Connect "servername", "username", "password"
  Set objDB = Server.CreateObject("SQLDMO.Database")
 <SELECT name="fdatabase">
  For Each objDB In srv.Databases
    If objDB.SystemObject = False Then
    End If


The SQLDMO.BackupDevice object will provide a list of backup devices currently installed on the server. I recommend using backup devices to backup your information, they will allow you to use the verify functionality of SQL-DMO to check the validity of the backup. The code snippet below uses this method to get a list of devices from the server.

  Dim srv
  Dim objDevice
  Set srv = Server.CreateObject("SQLDMO.SQLServer")
  srv.LoginTimeout = 15 
  srv.Connect "servername", "username", "password"
  Set objDevice = Server.CreateObject("SQLDMO.BackupDevice")
  For Each objDevice In srv.BackupDevices
    Response.Write objDevice.Name + "<BR>"


This is basically the core object we will use to backup the database. It has a vast variety of properties that can be used to provide the same level of backup functionality as the SQL enterprise manager. Lets take a moment to discuss the properties used in the sample application.

  1. BackupSetName - A name for the backup.
  2. Database - The database you want to backup.
  3. Action - Either full or incremental. There are more options; however, the sample application uses only two.
  4. BackupSetDescription - A description of the backup.
  5. Files - Use the file option if you do not want to backup to a device. It is nothing more than a file path and name for the backup that will reside on the server. For example C:\pubs.bak. If you use a file, the device option must be blank.
  6. Devices - A list of backup devices created on the server. If you use a device the files option must be blank.
  7. TruncateLog - Options used to backup or truncate transactions logs. The following options are given:
    • NoLog - Records referencing committed transactions are removed. Transaction log is not backed up.
    • NoTruncate - Transaction log is backed up. Records referencing committed transactions are not removed, providing a point-in-time image of the log.
    • Truncate - Transaction log is backed up. Records referencing committed transactions are removed.
  8. Initialize - If set to true then this backup becomes the first of the device overwriting any other backup media.

Lets take a look at the backup.asp within the sample application.

<%@ Language=VBScript %>
<!--contains all the login information -->
<!--#include file=login.asp -->
  Dim objBackup 
  'creating the backup object
  set objBackup      = Server.CreateObject("SQLDMO.Backup")
  'setting the properties
  objBackup.BackupSetName  = Request("fname")
  objBackup.Database       = Request("fdatabase")
  objBackup.Action         = Request("fAction")
  objBackup.BackupSetDescription = Request("fdescription")
  objBackup.Files        = Request("fbackupfile")
  objBackup.Devices      = Request("fdevice")
  objBackup.TruncateLog  = Request("flog")
  objBackup.Initialize   = Request("finit")
  'backing up the database
  objBackup.SQLBackup srv
  'disconnecting from the server
  'clean up
  set srv = nothing
  set objBackup = Nothing
The backup was started, use the <A HREF="devices.asp">verify</A>
option to see if it completed successfully.
<A HREF="default.asp">Click here</A> to return.

Verifying The Backup

If we were using Visual Basic or C++ we could use events to check the progress of the backup; however, this is not possible with ASP. We will use the SQLDMO.BackupDevice object's ReadBackupHeader method to confirm the backup. The code below code is from verify.asp, its takes the name of a backup device and provides information on the latest backup.

<%@ Language=VBScript %>
<!--Login information -->
<!--#include file=login.asp-->
  Dim objDevice
  Dim objResults
  Dim iCount
  Dim xCount
  'Create the backup device object
  Set objDevice  = Server.CreateObject("SQLDMO.BackupDevice")
    'Loop through the devices until we find a match
    For Each objDevice In srv.BackupDevices
      If objDevice.Name = Request("fname") Then
        'We found a match now read the results
        Set objResults = objDevice.ReadBackupHeader
        For iCount = 1 To objResults.Rows
          For xCount = 1 To objResults.Columns%>
          <%Next %>
        <%Next %>
     <%End If%>
 set srv = nothing
 set objDevice = nothing
 set objResults = nothing

The ReadBackupHeader method returns a QueryResults object. I use the Rows property of this object to see how many rows are returned. I then loop through the rows and columns to retrieve the information.

How Can I Use This Stuff

I find it useful for executing remote backups and restores. We have not covered restores yet, but SQL-DMO gives you this power as well.


I hope that this article has provided you with some insight into the power of SQL-DMO. As always if you have any questions, comments, or just want to stop by and buy me some coffee of your next trip to Seattle, I can be reached at

Home |  News |  Samples |  Articles |  Lessons |  Resources |  Forum |  Links |  Search |  Feedback
The Network for Technology Professionals



Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers