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