SQL Server, excecute batch T-SQL script on multiple databases

Question

Our SQL Server 2000 instance hosts several databases which are all similar, one for each of our client. When comes the time to update them all, we use Red Gate SQL Compare to generate a migration script between the development database and a copy of the current state DB of all the clients database.

SQL Compare generates a script which is transactional, if one step fails, the scripts rollback everything. But... currently our system uses a method that splits the script on batch separators (the GO statement) and then run each command separately, which ruins all the transactional stuff. The GO statement is not supported when querying the database by programmation (in classic ASP)

I want to know how I could run that script (keeping the transactions) on all those databases (like 250 DB), programmatically or manually in a tool? In Query Analyzer, we need to select each DB and press Run which is quite long for the number of DB we have.

Answer 1

Not sure if this will work, but try replacing the GO statements with semicolons, and running the entire statement in one batch.

Answer 2

I use external sqlcmd command line tool. I have the same situation on the server I work.

I have the script in *.sql file and the list of databases on the 2nd file. I have small *.bat script which iterate through all the databases and execute script using sqlcmd command.

In more details I have like this:

• DB.ini file with all the databases on which I want to deploy my script
• sql/ directory where I store all scripts
• runIt.bat - script which deploys scripts

The command line looks more-less like this:

sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -d <database_name> -T

In SQL Server 2000 it was osql utility

Answer 3

If you can use SSMS from SQL 2005 or 2008, then I'd recommend the free SSMS Tool pack

Answer 4

If I recall, you can also create a script in SQL Compare to change everything back to the state it started in. You might want to generate both.

When I did this sort of deployment (it's been awhile), I first loaded to a staging server that was made exactly like prod before I started to make sure the scripts would work on prod. If anything failed (usually because of the order that scripts were run, can't set a foreign key to a table that doesn't exist yet for instance). I also scripted al table changes first, then all view changes, then all UDF changes, then all stored proc changes. This cut down greatly onthe failures due to objects not yet existing, but I still usually had a few that needed to be adjusted.

Answer 5

There's a technique here that will help you:

http://weblogs.sqlteam.com/robv/articles/4099.aspx

You would modify it to run against a list of databases rather than servers.

Answer 6

I need a windows application for this, where i can select multiple databases by giving server ip and click on a run script method , it will also ask me to upload script file. If anyone has this application, I would appreciate sharing it.