RedGate sells a product call SQL Multi Script. The product allows you to asynchronously execute T-SQL scripts to large quantities of servers. There are a number of useful administrative tasks which can be made easier with this product.
One task, however, that is difficult to perform in SQL Multi Script is adding servers to the distribution lists. To be clear, adding one server, or maybe a half dozen, is easy. The tedium comes into play when you have an existing enterprise with dozens or hundreds of SQL Servers. Manually adding each server and saving the distribution list becomes time consuming.
I have created a solution which solves that problem, if you are using SQL Server’s Central Management Server to keep track of your server list. This solution could easily be modified to read from a text file or Excel as well, I’m just not providing the complete code for those avenues.
Bulk loading servers into a distribution list
SQL Multi Script saves the server entries for your distribution lists in XML format in an Application.dat file.
%AppData%\Roaming\Red Gate\SQL Multi Script\Application.dat
Snippet from the Application.dat file
Generate the XML
In order to load our server list, all we will need to do is generate the XML and copy/paste. The below query can be run on your SQL Server Central Management Server and generate the two sets of XML.
Click here for the code in text format.
Make sure that you close SQL Multi Script before opening the Application.dat file. If you don’t, there is a chance that SQL Multi Script will overwrite your changes, reverting them back to its original state.
With the Application.dat file open in your favorite text editor, copy/paste the entire [Multi-Script Database Elements] column into the databaseLists XML element.
Next, copy/paste the entire [Multi-Script Server Elements] column into the addedServers XML element.
Save the file and launch SQL Multi Script. You should see your distribution list populated on the right.