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
'****************************************************
'*
'* 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
'*
'****************************************************
