Thursday, February 28, 2008

[Maint] Running sql code on multiple servers

This isn't mine. A big "hey!" to Mark Hill, who came up with this many many years ago.

Very simple premise. For each .sql file in the directory of the below file (which you will save as a .bat), it will run it against each server listed, and save the results of each to a separate results file. Practically, it lets you run the same code against a bunch of servers in parallel. Hence the "maint" tag of this.

No, it's not nearly as easy/cool/pretty as SQL Farm or SQL Multi Script or the like, but it works well. And let's be honest - if we wanted our stuff to be shiny and pretty, we wouldn't be SQL DBAs, would we? We'd be building C# apps and Flash sites and the like.

Bonus points for coming up with a way to use a separate file that just has a list of servers.
(if you don't see all the code, just highlight the beginning and end and copy)

-----code begins-----
title %0
for %%a in ( .\*.sql ) do (
Start OSQL -i "%%a" -o "%%a.SERVER1_Output.txt" -E -S"SERVER1" -dMaster -n -w500
Start OSQL -i "%%a" -o "%%a.SERVER2_Output.txt" -E -S"SERVER2" -dMaster -n -w500
)
-----code ends-----

No comments: