One of the less commonly known things you can do with SQL Server Management Studio (SSMS) is execute a script across multiple instances from inside SSMS. This makes mundane and tedious tasks somewhat easier by allowing you to execute the same Transact-SQL script one time and it effect multiple servers without changing your connection.
- Open SSMS (SQL Server 2008 and newer)
- Click the tab to select “Registered Servers” (or use Ctrl+Alt+G)
- Right-click on the tree-view and select “New Server Group…”
- Create a group, like your production group, development server group, etc.
- Add servers to the new group by selecting “New Server Registration”
- Once you have the new group and it contains some servers, right-click on the group and select “New Query”
- The new query window will open and create a connection to all the servers in the selected group
- You can verify this is true by looking at the bottom of the query window. The pink toolbar will show the number of servers you are currently connected to from this connection. Pink indicates you are connected to more than one server.