Scripted SQL Deployment in a Transaction with Rollback Note: I believe this differs from other questions simply due to the size of the creation script I want to run under a single transaction.



Background

I'm working on automating the deployment of the data side of an application. I'm working with the team's existing scripts. They seem to drop everything and recreate. I don't have much time to make it perfect.

I'm thinking of running their drop-all script outside of a transaction, but running a single long "genesis" creation script inside a transaction at least so if that part should fail, its at a clean point and easier to recover from.

Genesis.sql contains 34k lines and seems to all be CREAT(ions) of functions, procedures, tables, types etc.

Question

Can I do this:

SET XACT_ABORT ON
BEGIN TRAN

# Genesis!

COMMIT TRAN


?

What's really confusing everyone is the impact of the GOs in the script with some saying that it will commit the transactions and other people (on the web) saying GO is just an instruction for the client to send a batch.

Follow-up

So far, this hasn't worked, new stuff is written and I see this message:


  The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.


However, I think its mostly due to syntax errors in one or two scripts (that all get concatenated to 30k lines) and the parser is thrown out.

It seems I'll need to dummy run the scripts into an empty, local DB first, to discover syntax errors, then attempt against the target environment server.
Is your database online during your release or do you have a maintenance window?

If you have a maintenance window and are running Enterprise Edition you could create a database snapshot pre-deployment.

Then you can easily revert to the snapshot if your release process does not execute properly.