Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

VBScript Class to Return Backup Information

By Bruce Szabo,

VBScript Class to Return backup information >

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.

 
Total article views: 6736 | Views in the last 30 days: 7
 
Related Articles
ARTICLE

VBScript Classes to Query SQL Server for Backup Information

A VBscript class is created that can be used to query the maintenance plans on an SQL server to dete...

FORUM

Private Key

TDE Private Key

SCRIPT

Generate C# class code for table

For any supplied table, my proc, usp_TableToClass, generates class code in C#, including fields, pro...

FORUM

database properties not accessing

database properties not accessing

FORUM

Event class in Trace

Event Class in Trace

Tags
backup / restore    
programming    
sql server 7    
sql-dmo    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones