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.
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
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.
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
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.
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.