CODEWITHSUNDEEP
sql-serverbatch-fileautomationsql-server-2000sqlcompare
MaxiWheat
MaxiWheat

Reputation: 6251

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

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 script rolls back everything. But currently our system uses a method that splits the script on batch separators (the GO statement) and then runs 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.

Upvotes: 3

Views: 10500

Answers (5)

Juan Carlos
Juan Carlos

Reputation: 586

7 years later i had the same issue so many times so I made it and published the project:

TAKODEPLOY

Here are some features:

  • Get all databases from a single instance and apply a name filter. Or just a single direct connection.
  • Mix database sources as much as you want. Example, two direct and one full instance with or withut a filter.
  • Script editor (Avalon Text, same monodevelop uses)
  • Scripts are parsed and errors are detected before executing.
  • Scripts are 'splitted' by GO statements.
  • Save your deployment into a file
  • Get a list of all databases before deploying.
  • See in realtime what is happening (PRINT statements are recommended here!).
  • Automatic rollback to independent database if any error occurs.
  • Transparent Updates via Squirrel.

You can get it at: https://github.com/andreujuanc/TakoDeploy

Upvotes: 1

Lukasz Lysik
Lukasz Lysik

Reputation: 10610

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

UPDATE

Red Gate now have a tool called SQL Multi Script, which basically does exactly what you want. I supports SQL 2000 to 2008 R2 and running queries on multiple databases in parallel which improve performance.

Upvotes: 3

HLGEM
HLGEM

Reputation: 96552

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.

Upvotes: 0

gbn
gbn

Reputation: 432180

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

Upvotes: 5

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171351

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

Upvotes: 0

Related Questions