SQL Maintenance Plans and Backed Up Databases: Pt. 3

Thursday Jun 27th 2002 by Bruce Szabo

The final article in Bruce Szabo's three-part series on SQL Maintenance Plans demonstrates the use of SQL-DMO to find the servers in a domain so their maintenance plans can be read.

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 an SQL server's maintenance plans. The second part of this series demonstrated how to use a VBScript class to display the information from the maintenance plans. The final article in the series will demonstrate how to use SQL-DMO in order to find the servers in a domain so their maintenance plans can be read.

Although SQL-DMO is used in this article to find the servers, this only scratches the surface of how it can be used. In future articles, SQL-DMO will be used as a tool to demonstrate a number of tasks that can be accomplished on a SQL server via VBScript. For an introduction to SQL-DMO refer to Andy Warren's home page on Swynk.

The example below uses SQL-DMO to query available servers, where it will then be possible to use the classes from the previous articles to output the backup information. This is a useful tool in an environment with a number of SQL servers, especially a development environment, where SQL is installed on a number of desktops.

An Example

The code for this article can be found here. Below is all the code that is needed in order to run this program and return the relevant information. It should be pointed out that trusted security is being used in this case, so this program is best run by a DBA with access to all the SQL servers or by a domain administrator.

The sample for this program simply launches the subroutine that performs all the work. In this case the subroutine is subEnumerateDBBackups. If all the supporting subroutines are present this call will write out the backup information for all the databases found on all the SQL servers. The servers are found using SQL-DMO's ListAvailableSQLServers() method.

Call subEnumerateDBBackups

The subEnumerateDBBackups Subroutine

Although the subEnumerateDBBackups subroutine is discussed here, all the routines needed to support this routine are in the code download. The code in the download could be manipulated in a number of ways to return information about backups or other SQL server information.

Lines 1-8 initialize the variables that will be used. Line 10 creates an SQL-DMO application object, while line 11 calls the ListAvailableServers method. This method finds all the available SQL servers. Line 13 is used to continue in the event an error occurs while trying to connect to a server.

Line 15 begins a loop that will connect to each server in the oNames collection. These were the servers returned by the ListAvailableServers method. On line 16 the server object is instantiated. Line 18 sets the security to trusted. If SQL security was to be used, a username and password would have to follow the oName variable on line 19. Line 19 is where the connection to the server is made. Lines 20 - 28 catch any errors and create a string that can be written to the same file as all the other information. This allows one to scan all the servers and find out which ones had backups done and which cannot be reached for any reason.

Line 31 creates a string variable that will hold the backup information for each server. On line 32 the class from article one of this series is instantiated. The SQLserver property is set on line 33 and line 34 adds the server name to the string variable. Line 35 starts a loop to look at each of the databases that the objDBlist object returns. Line 36 adds the Database name to the string variable. On line 40 the VBscript object from article two is instantiated. The SQLServer and Database properties are set on lines 41 and 42 and the backup information is added to the string variable on line 43. Line 44 destroys the object and moves to the next database. Line 46 continues the loop. Line 47 writes the information to a log file that can be read using notepad or any other text editor. Line 48 destroys the database list object and line 49 moves to the next server. Lines 51-55 disconnect and destroy the other objects created in this routine.

Click here for the subEnumerateDBBackups code (opens in new window).

If the code from the download is used without any modification a text file will reside in the root of the C drive called sqlbackupinfo.txt. This will contain a listing of the SQL servers enumerated and the databases that were found in the maintenance plans on those servers. If a server could not be reached, this is also noted in the file.


SQL-DMO is useful for providing information about SQL servers programmatically. This allows one the ability to script maintanence tasks for SQL server or to provide information about SQL server. In future articles other uses of SQL-DMO will be demonstrated. This three-part series should provide the foundation for backing up and monitoring the backups of SQL server programatically.

» See All Articles by Columnist Bruce Szabo

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved