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