In my previous article, "Using SQL Multi Script to identify SQL Backup versions" I described a SQL script that would interrogate the master database of a given SQL Server instance and detect whether or not SQL Backup was installed on that instance and, if so, which version.

In this short, follow-up article, I take you step-by-step through the process of executing that script against multiple servers, using SQL Multi Script. This will produce an aggregated report over all your servers, showing which of them have SQL Backup installed and which versions.

In the following example, I am going to assume that you are using SQL Multi Script for the first time. If you have previously used SQL Multi Script on your servers, then you may be able to skip the first step.

When you start a fresh copy of SQL Multi Script, you begin with a screen that looks like the following:

Figure 1: A fresh install of SQL Multi Script looks like this.

Setting up SQL Multi Script to run our script against all of our servers is very easy. Just follow these steps:

Step 1

Click on the Configure button under Database Distribution List, on the right hand side, and the following screen appears:

Figure 2: You must tell SQL Multi Script what SQL Server instances you want to run the script against.

In the above screen, the Databases to Add portion of the screen shows you all of the SQL Server instances that have already been registered with SQL Server Management Studio. If you need to add additional SQL Server instances, you can easily do so by clicking on the Add a SQL Server Not Listed button. Under each SQL Server instance is a list of the databases on that instance. I have opened up the PELE SQL Server instance to show you this.

In our case, the script we want to run only needs to run against the master database of each SQL Server instance. In the following screen, you'll see that I've selected the master database of three SQL Server instances and added them to the Databases to Execute Against section of the screen:

Figure 3: For every SQL Server instance and database you want to run a script against, add it to a SQL Server instance distribution list.

In addition, I have added these databases to the Default Distribution List. You can create as many different distribution lists as you need and reuse them over and over again. Once you are done adding SQL Server instances and databases to the distribution list, click OK.

Step 2

Once the distribution list has been completed, the screen now looks like this:

Figure 4: The Database Distribution List is complete and ready to be used.

Our next step is to add the script to SQL Multi Script so that it can be run against the SQL Server instances we just selected.

To do this, click on the New button and paste the script into the script window, like I have done below.

Figure 5: While you can’t see the entire script above, it is there and ready to run against the SQL Server instances listed in my Database Distribution list.

After I pasted the script in the Script windows, I also renamed it to Indentify_SQLBackup_Versions.sql. This way, SQL Multi Script will be able easily reuse the script anytime you want to run it against your servers.

Step 3

The setup has been completed, and now all we have to do is to execute the script. Once I click on the Execute Now button, the script will be run against all three of my SQL Server instances, gather the results from all the scripts from each instance, and produce an aggregated report. The results are below in two abbreviated screen shots:

Figure 6: We get two results screens. In Results 1, we see a list of all the servers than don't have SQL Backup installed.

Figure 7: In the Results 2 screen, we see a list of all the SQL Server instances that have SQL Backup installed on them, along with their Version Number and Serial Number

At this point, we have our results, and we can view them from the Results window, or we can save them for later review. Also notice that the Results screen shows you what script was run, what SQL Server instances the script was run against, and the aggregated results from all the SQL Server instances.

Summary

It has taken you much longer for you to read this article than it took me to actually set up and run this script in SQL Multi Script. In fact, now that the Database Distribution list and script have been added to SQL Multi Script, you can run the script over and over again, as much as you like without having to start from step one.

As you can see, by using SQL Multi Script to automate a manual process, a lot of time has been saved. While my example of only three instances may not make using SQL Multi Script a time saver, it will if you have ten or more servers.

This is just one example of how SQL Multi Script can save you time. Just think of all the other scripts you could run automatically on all of your SQL Server instances if you had a copy. If you haven’t tried SQL Multi Script out yet, download a trial copy today and find out how it can boost your productivity.

Author profile:

A Microsoft SQL Server MVP with over 10 years' SQL Server experience, Brad founded the popular community site SQLServer-Performance.Com where he now acts as the technical editor and forum moderator. Brad is also a frequent speaker at SQL PASS, SQL Connections, SQL Server user groups, and other industry seminars and he is the author or co-author of more than 12 technical books and over 100 published articles. He spends what time he has left with his family in Hawaii.

This article is a follow-up to an article that describes how SQL Multi Script can be used to identify on which servers SQL Backup has been installed and the version of SQL Backup being used. Read the first article or find out more information about SQL Backup.