SQL Maintenance Plans and Backed Up Databases: Pt. 1

May 28, 2002

Introduction

In a development environment the number of SQL servers can become quite extensive. If a production environment exists within the same infrastructure as the development environment, keeping everyone informed about the state of backups can become quite difficult. In addition, backing up all the data can require a lot of space. In some instances the data may be a duplicate of other databases that are backed up in other places.

In order to efficiently backup data and maintain the ability to recover from disasters, it is important to keep track of not only the SQL server but also the databases contained on each server. The ability then for the users in the enviroment to keep track of which data is backed up is imperative.

The following script queries the maintenance plans on a server to find which databases are backed up. The script uses a VBscript class and could easily be ported to an ASP page to keep an up-to-date record of when a database was last backed up. This script simply pulls the information about the databases currently in the maintenance plans. In parts two and three of this series this information will be pulled together with other scripts to develop an ability to poll the servers in an environment and determine the backups occuring on those servers.

Assumptions

There are some assumptions made concerning how the backups will be performed. The backups should be set up using a maintenance plan. If a maintenance plan is used, it is then possible to query the MSDB database to find out which databases are backed up. The table that holds this information is sysdbmaintplan_history. The nice thing is this table holds all the information for all the maintenance plans. If a number of seperate maintenance plans are setup, it is possible, programmatically, to search for only the desired information.

The first step in programmatically getting the information back will be to find out which databases are currently being backed up using the various maintenance plans on the server. An example of how this can be done using a class is demonstrated below.

An Example

The following is all the code that is needed, when the class module is used, to get a list of databases backed up via the various maintenance plans. The code for this article can be found here.

strDBList = strDBList & " Database "
set objDBList = new clsServerBackupInfoList
  objdblist.SQLServer = "MyServer"
  if not objDBList.EOF then
    do while not objDBList.EOF
      strDBList = strDBList &  objDBList.Database & vbCRLF
      objDBList.MoveNext
    loop
  end if
set objDBList = nothing
msgbox strDBList

The code above does the following. It instantiates the class. Once this is done, the desired server is assigned to the server property of the object. It is then possible to cycle through the databases using the MoveNext method and list the servers by getting the database property. In this case, each database is appended to a string and the string displays the list of databases that have backups in the maintenance plan using a message box.

It should be noted that this table is historical so unless it is cleaned out there could be databases listed that are no longer on the server or that have been removed from the backup plans. For this reason it will be important to use another routine to extract the dates of the last backup.

The Class

The following code shows the class and the properties associated with the class. An explanation of the code follows below with details on how it works and an elaboration on key points.

Click here for Class code (opens in new window)

Line 1 defines the class and lines 3 - 11 set up the private variables needed. In the initialize subroutine the end of file (EOF) property is set to true, so when the object is instantiated the user knows no records exist. Lines 17 - 23 set up the termination subroutine which is called when the object is destroyed. This routine cleans up any of the connections and recordsets used by the class.

The first of the two let statements in this routine is SQLServer. The SQLServer property allows one to set the server to be queried. Once the server is set, it is verified to be set (line 27) and then a connection object is created(line 28). The connection string for the object is put together on lines 30 - 33, and the connection is opened on line 35. This class uses integrated security. If one wanted to use SQL security, the connection string would have to be modified to include a username and password.

In order to query the database, a SQL statement is needed. This statement is constructed on lines 36 and 37. This SQL statement could be used in Query Analyzer to show the information that the class will bring back. The recordset is retrieved on line 39, but it is lines 41 - 46 that check to make sure the database property is set correctly. Line 41 checks to make sure at least one record was brought back; if not, the EOF property is set to true. Lines 50-52 set the database property for input, although it is not used here. The other property gets are set on lines 54-64. These are the properties the user is able to access. The last supporting method is set up on lines 66-74. This method is needed in order to cycle through the databases on the server. The final routine on lines 76-78 is used to close the connection to the database and is called in the terminate routine.

Conclusions

A class is presented that can be used to query the maintenance plans on a server for the database backups. This class can be used in conjunction with others to help create a web page displaying databse backup information. In the next few articles these classes will be presented. These classes could be further coupled to SQL-DMO in order to automatially query the SQL servers in an environment and gather the backup information.

» See All Articles by Columnist Bruce Szabo