Backed Up Databases

Introduction

In the first part of this series a script was used to query an SQL server for databases that were being backed up as part of the maintenance plans. This allows one to determine if a database is part of a maintenance plan. It would, in most cases, be nice to have the pertinent backup information on hand. The following class will return the relevant backup information from the maintenance plan so it can be viewed in a more user friendly manner.

If the script presented from the first series is combined with this script one would be able to loop through all the databases in the maintenance plans and return their individual backup information to a user interface. By taking these classes and converting them to ASP scripts a web page can be created to display the current backup situation on a given SQL server.

Some of these techniques will be presented in upcoming articles. In this article, however, a script to present the backup information is going to be presented.

An Example

The code for this article can be found here. The following is an example of the code needed to return the backup information for a given database. By entering the server and database name one can query to find the last backup for a give database.

There are two message boxes here that return the backup information. The message boxes demonstrate two ways information can be returned from the class. The first method is to use GetBackUpHist. This method of the class returns a text string with all the backup information put together. The second method takes each individual element and builds the text string. This is useful to add formatting or to write information to a file if the this class was used as part of an inventory type script.

set objDBInfo = new clsDBBackupInfo
objDBInfo.SQLServer = "MYSERVER"
objDBInfo.UserID = "MYUSERID"
objDBInfo.Password = "MYPASSWORD"
objDBInfo.Database= "MYDATBASE"
msgbox objDBInfo.GetBackupHist
strDBMsg = ""
strDBMsg = strDBMsg & "Database " & objdbinfo.Database & vbCRLF
strDBMsg = strDBMsg & "Start Time " & objdbinfo.StartTime & vbCRLF
strDBMsg = strDBMsg & "EndTime " & objdbinfo.EndTime & vbCRLF
strDBMsg = strDBMsg & "Duration " & objdbinfo.Duration & vbCRLF
strDBMsg = strDBMsg & "Plan " & objdbinfo.Plan & vbCRLF
strDBMsg = strDBMsg & "Success " & objdbinfo.Success & vbCRLF
strDBMsg = strDBMsg & "Message " & objdbinfo.Message & vbCRLF
msgbox strDBMsg

set objDBInfo = nothing

The UserID and Password properties are optional. If the SQL server is running with integrated security and the logged in user is an administrator on the SQL server the informatio will be returned without the UserID and Password properties.

The Class

The beginning of the class has an explanation for the properties and methods of the class. This section is not enumerated. The enumerated section of the code starts by initializing the needed variables (lines 1-18). The only code needed in the initialize routine sets the security variable to integrated security by default. The terminate routine closes the connection to the server.

Lines 28-116 are where the let properties are defined. These are the five settings the user has the ability to control. In this case the user can set the SQLServer, the Database, the UserID, the Password, and the Security. When the SQLServer property and the Database properties are set a check is made to see if both properties have been set (lines 30 and 68). If both properties have been set the rest of the let property routines behave the same for these two propeties. A SQL statement is constructed, a connection is open and a recordset is returned. The record set is checked to make sure it is not empty and the values are read into the variables. When the recordset values are read into the private variables they are then available as properties to the users via the get statements which will be discussed below.

The UserID and Password properties need to be set, as mentioned above, if the server will not be accessible via integrated security. The security setting does not need to be set as it is set to integrated by default. This setting might be used if one wanted to change servers and databases. One server may be able to use integrated security while another needs an SQL login.

The class has eight get properties which are the properties the user can get once the object has been instantiated. The SQLServer and Database properties should be known so they may not need to be returned. The other six properties (lines 118 - 148) can be used by the user to format the database backup information. StartTime, EndTime and Duration give the user an idea of how long a backup takes. The success property lets the user know if the backup was successful. The plan property lets the user know which database maintenance plan the backup is a member of and the message property lists where physically the backup was written.

Lines 151 - 168 are a private routine to open a connection to the database. Lines 170-172 are a private routine to close the connection to the database. The close routine is called by the terminate routine. The final method is the GetBackuHist. This method returns a string with the same information returned by the individual properties. This method is used mostly for troubleshooting or in a case where a script needs to return information without regards to format.

'****************************************************
'*
'*CLASS clsDBBackupInfo
'*
'****************************************************
'*The purpose of this class is to list the backups for a given database.
'*The information can be retrieved via a text message using the GetBackupHist()
'*method or using the individual elements using the gets.
'* 
'*  LETS
'*SQLServer- Server whose maintenance plans you want to query
'*Database- Database we want to look up last the last backup for
'*
'*  GETS
'*SQLServer- Server Name
'*Database- Database Name
'*Plan- Plan name containing the backup
'*Success- was the last backup a success
'*EndTime- when the last backup ended
'*StartTime- when the last backup started
'*Duration- the length of time the last backup took
'*Message- message for the last backup usually the location of the backup file
'*
'*Public Functions
'*GetBackupHist() -Returns a string containing the backup information and populates the GETS.
1 class clsDBBackupInfo
2private strSQLServer
3private strDataBase
4private objCon
5private SQL2
6private RS1
7private str
8private fd
9private ConnectionString
10private strPlan
11private boolSuccess
12private dtEndTime
13private dtStartTime
14private dtDuration
15private strMessage
16private boolSecurity
17private strUserID
18private strPassword
19
20Private Sub Class_Initialize()
21boolSecurity = TRUE
22End Sub
23
24Private Sub Class_Terminate()
25Call closeConnection
26End Sub
27
28Public Property Let SQLServer ( byVal tmpSQLServer )
29strSQLServer = tmpSQLServer
30if len(strSQLServer) > 0 and len(strDatabase) > 0 then 
31Dim SQL2
32Dim RS1
33Dim str
34Dim fd
35
36SQL2 = SQL2 & "SELECT TOP 1 * FROM sysdbmaintplan_history " 
37SQL2 = SQL2 & "WHERE (activity LIKE " & "'" & "backup database" & "'" & ") AND (database_name = " & "'" & strDatabase & "') "
38SQL2 = SQL2 & "ORDER BY end_time Desc"
39
40Call openConnection()
41
42Set RS1 = objCon.Execute(SQL2)
43
44if not RS1.eof then
45for each fdin RS1.Fields
46str= str&fd.name & "     " & fd.value & vbCRLF
47next
48strPlan= RS1("Plan_name")
49boolSuccess= RS1("Succeeded")
50dtStartTime= RS1("Start_Time")
51dtEndTime= RS1("End_time")
52dtDuration= RS1("Duration")
53strMessage= RS1("Message")
54else 
55strPlan= ""
56boolSuccess= ""
57dtStartTime= ""
58dtEndTime= ""
59dtDuration= ""
60strMessage= ""
61end if
62Set RS1 = Nothing
63end if
64End Property
65
66Public Property Let Database ( byVal tmpDatabase )
67strDatabase = tmpDatabase
68if len(strSQLServer) > 0 and len(strDatabase) > 0 then 
69Dim SQL2
70Dim RS1
71Dim str
72Dim fd
73
74SQL2 = SQL2 & "SELECT TOP 1 * FROM sysdbmaintplan_history " 
75SQL2 = SQL2 & "WHERE (activity LIKE " & "'" & "backup database" & "'" & ") AND (database_name = " & "'" & strDatabase & "') "
76SQL2 = SQL2 & "ORDER BY end_time Desc"
77
78Call openConnection()
79
80Set RS1 = objCon.Execute(SQL2)
81
82if not RS1.eof then
83for each fdin RS1.Fields
84str= str&fd.name & "     " & fd.value & vbCRLF
85next
86strPlan= RS1("Plan_name")
87boolSuccess= RS1("Succeeded")
88dtStartTime= RS1("Start_Time")
89dtEndTime= RS1("End_time")
90dtDuration= RS1("Duration")
91strMessage= RS1("Message")
92else 
93strPlan= ""
94boolSuccess= ""
95dtStartTime= ""
96dtEndTime= ""
97dtDuration= ""
98strMessage= ""
99end if
100Set RS1 = Nothing
101end if
102End Property
103
104Public Property Let Security ( byVal tmpSecurity )
105boolSecurity = tmpSecurity
106End Property
107
108Public Property Let UserID ( byVal tmpUserID )
109strUserID = tmpUserID
100boolSecurity = FALSE
101End Property
112
113Public Property Let Password ( byVal tmpPassword )
114strPassword = tmpPassword
115boolSecurity = FALSE
116End Property
117
118Public Property Get SQLServer
119SQLServer = strSQLServer
120End Property
121
122Public Property Get Database
123Database = strDatabase
124End Property
125
126Public Property Get Plan
127Plan = strPlan
128End Property
129
130Public Property Get Success
131Success = boolSuccess
132End Property
133
134Public Property Get EndTime
135EndTime = dtEndTime
136End Property
137
138Public Property Get StartTime
139StartTime = dtStartTime
140End Property
141
142Public Property Get Duration
143Duration = dtDuration
144End Property
145
146Public Property Get Message
147Message = strMessage
148End Property
149
140
151Private Sub openConnection()
152
153Set objCon = WScript.CreateObject("ADODB.Connection") 
154
155ConnectionString= "Provider=sqloledb;"
156ConnectionString= ConnectionString&"Data Source=" & strSQLServer & ";" 
157ConnectionString= ConnectionString& "Initial Catalog=MSDB;"
158if boolSecurity = TRUE then
159ConnectionString= ConnectionString& "Integrated Security=SSPI;"
160else
161ConnectionString= ConnectionString& "User Id="& strUserID& ";" 
162ConnectionString= ConnectionString& "Password="& strPassword& ";"
163end if
164
165
166objCon.Open ConnectionString
167
168End Sub
169
170Private Sub closeConnection()
171objCon.Close
172End Sub
173
174Public Function GetBackupHist()
175Dim SQL2
176Dim RS1
177Dim str
178Dim fd
179
180SQL2 = SQL2 & "SELECT TOP 1 * FROM sysdbmaintplan_history " 
181SQL2 = SQL2 & "WHERE (activity LIKE " & "'" & "backup database" & "'" & ") AND (database_name = " & "'" & strDatabase & "') "SQL2 = SQL2 & "ORDER BY end_time Desc"
182
183Call openConnection()
184
185Set RS1 = objCon.Execute(SQL2)
186
187if not RS1.eof then
188for each fdin RS1.Fields
189str= str&fd.name & "     " & fd.value & vbCRLF
190next
191strPlan= RS1("Plan_name")
192boolSuccess= RS1("Succeeded")
193dtStartTime= RS1("Start_Time")
194dtEndTime= RS1("End_time")
195dtDuration= RS1("Duration")
196strMessage= RS1("Message")
197else 
198str = "No Backups for " & strDatabase & " on " & strSQLServer
199strPlan= ""
200boolSuccess= ""
201dtStartTime= ""
202dtEndTime= ""
203dtDuration= ""
204strMessage= ""
205end if
206 
207GetBackupHist = str
208Set RS1 = Nothing
209
210End Function
211
212End Class
'****************************************************
'*
'*END CLASS clsDBBackupInfo
'*
'****************************************************

Conclusions

This routine is used to query maintenance plans for information regarding backups. The routine allows one to draft formatted messages using the properties of the class. The class can be used in conjunction with other routines to create a reporting mechanism for SQL backup procedures. In the next article both this script and the previous script will be used in conjunction with SQL-DMO to find servers and query the maintenance plans on those servers.