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 strDBListThe 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.
