Run scripts on multiple servers in SQL Management Studio

I have a number of scripts that I need to run on multiple servers and databases. Once I have created a Solution with them all in, how do I make them run in a specific sequence, and how can I switch the focus of the entire set of scripts to the other databases I want to run them on?

Thanks,

-Rob

There is no way to do this included in SQL Management Studio. Solutions in SMS aren't like VS solutions, in that you can execute code pages and objects. In SMS the solution is just a convenient place to store and organize the script files you are working with.

You would have to open each script seperately and execute it against the server and database you wanted in the order you wanted.

Sorry, it's going to be a manual process. There are some 3rd party tools that might help some. Check Red Gate and APEX products.

|||

You can do this by using a batch file to execute either OSQL or SQLCMD (preferred) - both of which can be used to execute script files. If you pass server and database names as parameters into your batch file you can then pass the parameter values to OSQL or SQLCMD.

I don't have any examples to hand, but the following links should set you on the right track:

http://msdn2.microsoft.com/en-us/library/ms162806.aspx

http://msdn2.microsoft.com/en-us/library/ms162773.aspx

http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/percent.mspx?mfr=true

This is how we deploy changes to our databases in each of our environments - the only thing that changes between each environment is the server name parameter. We generally have one batch file per database and one SQL script per database object - one batch file can be used to execute hundreds of SQL script files if need be.

Chris

|||

Dan and Chris,

Thanks for your response on my question. Not the answers I was hoping to get, but thanks anyway!! I have used the DOS bat file method successfully in the the past, but it is a bit clunky and was hoping against hope that there would be a better method in the SQL management Studio. DO you think I may be looking in the wrong tool?

I can't believe that I am the only one who thinks that the lack of a tool or process to set up implementation of scripts is a serious gap in the SQL Management Studio.

Thanks again, Rob

|||

You have to remember that SQL Server is an RDBMS. Tools provide for development are extras that I find invaluable. Just look at how clunky the management and development tools that come with Oracle have been over the years.

On a good note, you may be able to make some progress by developing your procedures and sql code in Visual Studio instead of SMS. If you have VS 2005 professional edition you can create a sql server database project that will allow you to write t-sql code and execute it. You should be able to write a minor application that would execute the scripts for you against a list of databases and/or servers.

Sorry that there isn't an easier way to do it through SMS. Perhaps it will be a forthcoming feature. I'll suggest it on the product feedback group.