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.