Backed Up Databases
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 part 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 determing the backups occuring on those servers.
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 maintence plans on the server. An example of
how this can be done using a class is demonstrated below.
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
set objDBList = nothing
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 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 following code shows the class and the properties associated with the class. Below an explanation of the
code will explain how it works and elaborate on key points.
1. Class clsServerBackupInfoList
3. private strSQLServer
4. private strDataBase
5. private objCon
6. private SQL2
7. private RS1
8. private str
9. private fd
10. private ConnectionString
11. private boolEOF
13. Private Sub Class_Initialize()
14. boolEOF = true
15. End Sub
17. Private Sub Class_Terminate()
18. if RS1.State <> 0 then
20. Set RS1 = Nothing
21. end if
22. Call closeConnection
23. End Sub
25. Public Property Let SQLServer ( byVal tmpSQLServer )
26. strSQLServer = tmpSQLServer
27. if len(strSQLServer) > 0 then
28. Set objCon = WScript.CreateObject("ADODB.Connection")
30. ConnectionString = "Provider=sqloledb;"
31. ConnectionString = ConnectionString & "Data Source=" & strSQLServer & ";"
32. ConnectionString = ConnectionString & "Initial Catalog=MSDB;"
33. ConnectionString = ConnectionString & "Integrated Security=SSPI;"
35. objCon.Open ConnectionString
36. SQL2 = SQL2 & "SELECT distinct database_name FROM sysdbmaintplan_history "
37. SQL2 = SQL2 & "WHERE (activity LIKE " & "'" & "backup database" & "'" & ")"
39. Set RS1 = objCon.Execute(SQL2)
41. if not RS1.eof then
42. strDatabase = RS1("database_name")
43. boolEOF = False
45. boolEOF = True
46. end if
47. end if
48. End Property
50. Public Property Let Database ( byVal tmpDatabase )
51. strDatabase = tmpDatabase
52. End Property
54. Public Property Get SQLServer
55. SQLServer = strSQLServer
56. End Property
58. Public Property Get Database
59. Database = strDatabase
60. End Property
62. Public Property Get EOF
63. EOF = boolEOF
64. End Property
66. Public Sub MoveNext
68. if not RS1.EOF then
69. strDatabase = RS1("Database_name")
70. boolEOF = false
72. boolEOF = True
73. end if
74. end sub
76. Private Sub closeConnection()
78. End Sub
80. End Class
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 open 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 setup 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.
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.