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

Please visit our partners


Windows Technology Windows Technology
15 Seconds
4GuysFromRolla.com
ASP 101
ASP Wire
VB Forums
VB Wire
WinDrivers.com
internet.commerce internet.commerce
Partners & Affiliates














ASP 101 is an
internet.com site
ASP 101 is an internet.com site
IT
Developer
Internet News
Small Business
Personal Technology

Search internet.com
Advertise
Corporate Info
Newsletters
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 @ Microsoft.com
What is Adovbs.inc and Why Do I Need It?
An Overview of ASP.NET

QUICK TIP:
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

Overview

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.

SQLDMO.SQLServer

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.

SQLDMO.Database

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
%>
  <OPTION><%=objDB.Name%></OPTION>
<%
    End If
  Next
%>
 </SELECT>

SQLDMO.BackupDevice

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>"
  Next
%>

SQLDMO.Backup

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 %>
<HTML>
<BODY>
<!--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
  srv.disconnect
  'clean up
  set srv = nothing
  set objBackup = Nothing
%>
<P>
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.
</P>
</BODY>
</HTML>

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 %>
<HTML>
<BODY>
<!--Login information -->
<!--#include file=login.asp-->
<P>
<%
  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%>
           <B><%=objResults.ColumnName(xcount)%></B>: 
           <%=objResults.GetColumnString(icount,xcount)%><br>
          <%Next %>
          <HR>
        <%Next %>
     <%End If%>
   <%Next%>
<%
 srv.Disconnect
 set srv = nothing
 set objDevice = nothing
 set objResults = nothing
%>
</BODY>
</HTML>

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.

Summary

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 cwilson@harborviewsolutions.com.


Home |  News |  Samples |  Articles |  Lessons |  Resources |  Forum |  Links |  Search |  Feedback

Internet.com
The Network for Technology Professionals

Search:

About Internet.com

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