Running Scripts Across Multiple SQL Server Instances Using Toad Group Execute

Hi follow Toadies!

As a SQL Server administrator you're likely managing dozens if not hundreds of databases. It's extremely time consuming to run a script on each and every one of those databases and then check the results. To make things even trickier, maybe you want to run a script on only some instances but not others.

For example, let's say you're looking for a server job but you don't know what server it's on. You'll probably go through and connect to each system, then query each one, and look for that job.

Toad for SQL Server has a slick Group Execute function that lets you target what instances you want to run your query against. You click the run button once, and Toad will connect to each instance and run your script then aggregate the results.

In this example, I'm connected an instance called WIN7 and I have an Editor window open.

If I go to the menu Editor > Windows and make sure Group Execute is checked on. This enables the embedded Group Execute tab within the Editor window.

If I click on this tab, then check the box "Enable", it puts my Editor in a Group Execute Mode. I can check multiple connections I want a script to be executed against from the Editor window.

Now back in the Editor window. I have a script here that lists the Scheduled Jobs on the instance. I click the execute button and it will run this script against all the connections I checked on the previous step. It displays the results for both executions at the bottom.

How awesome is that? If I had to check hundreds of instances it would save me a ton of time.

Go back to the Group Execute tab and you'll see a notification if the script ran successfully. You can toggle the mode on and off by unchecking the Enable box. If you want, you can group connections into custom folders through the right click menu. That way you can check off groups of SQL Server instances at a time, like I'm doing for my Development servers.

By the way, this Group Execute feature is also available in Toad for DB2 and Toad for SAP Databases.

Until next time, Toadies!

-Mathew Phan