Using SQL-DMO To Backup and Verify a Database

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 objectset 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 upset srv = nothingset objBackup = Nothing%><P>
The backup was started, use the <AHREF="devices.asp">verify</A>
option to see if it completed successfully.
<AHREF="default.asp">Click here</A> to return.
</P></BODY></HTML>