Backed Up Databases

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

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 maintence 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 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. Below an explanation of the code will explain how it works and elaborate on key points.

1. Class clsServerBackupInfoList
2.
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
12. 
13. Private Sub Class_Initialize()
14. boolEOF = true
15. End Sub
16. 
17. Private Sub Class_Terminate()
18. if RS1.State <> 0 then
19. RS1.close
20. Set RS1 = Nothing
21. end if
22. Call closeConnection
23. End Sub
24. 
25. Public Property Let SQLServer ( byVal tmpSQLServer )
26. strSQLServer = tmpSQLServer
27. if len(strSQLServer) > 0 then 
28. Set objCon = WScript.CreateObject("ADODB.Connection") 
29. 
30. ConnectionString= "Provider=sqloledb;"
31. ConnectionString= ConnectionString&"Data Source=" & strSQLServer & ";" 
32. ConnectionString= ConnectionString& "Initial Catalog=MSDB;"
33. ConnectionString= ConnectionString& "Integrated Security=SSPI;"
34. 
35. objCon.Open ConnectionString
36. SQL2 = SQL2 & "SELECT distinct database_name FROM sysdbmaintplan_history " 
37. SQL2 = SQL2 & "WHERE (activity LIKE " & "'" & "backup database" & "'" & ")"
38.
39. Set RS1 = objCon.Execute(SQL2)
40.
41. if not RS1.eof then
42. strDatabase = RS1("database_name")
43. boolEOF = False
44. else 
45. boolEOF = True
46. end if
47. end if
48. End Property
49.
50. Public Property Let Database ( byVal tmpDatabase )
51. strDatabase = tmpDatabase
52. End Property
53.
54. Public Property Get SQLServer
55.    SQLServer = strSQLServer
56. End Property
57.
58. Public Property Get Database
59.    Database = strDatabase
60.     End Property
61.
62. Public Property Get EOF
63. EOF = boolEOF
64. End Property
65.
66.  Public Sub MoveNext
67. RS1.MoveNext
68. if not RS1.EOF then
69. strDatabase = RS1("Database_name")
70. boolEOF = false
71. else 
72. boolEOF = True
73. end if
74. end sub
75. 
76. Private Sub closeConnection()
77. objCon.Close
78. End Sub
79. 
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.

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.