Using SQL-DMO To Backup and Verify a Database

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 objectSet objDevice  = Server.CreateObject("SQLDMO.BackupDevice")
    'Loop through the devices until we find a matchFor Each objDevice In srv.BackupDevices
      If objDevice.Name = Request("fname") Then'We found a match now read the resultsSet objResults = objDevice.ReadBackupHeader
        For iCount = 1 To objResults.Rows
          For xCount = 1 To objResults.Columns%><B><%=objResults.ColumnName(xcount)%><B>:&nbsp;<%=objResults.GetColumnString(icount,xcount)%><br><%Next%><HR><%Next%><%End If%><%Next%><%
 srv.Disconnect
 set srv = nothingset objDevice = nothingset 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 [email protected].