dcsimg

Code example 1:

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 Code:

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