With my database upgrade scripts, I typically just have one long script that makes the necessary changes for that database version. However, if one statement fails halfway through the script, it leaves the database in an inconsistent state.
How can I make the entire upgrade script one atomic operation? I've tried just wrapping all of the statements in a transaction, but that does not work. Even with SET XACT_ABORT ON, if one statement fails and rolls back the transactions, the rest of the statements just keep going. I would like a solution that doesn't require me to write IF @@TRANCOUNT > 0... before each and every statement. For example:
asked Oct 28 '09 at 12:20 PM in Default
Here is what I do:
Before start of any DDL changes:
Before any single DDL statement:
After any single DDL statement:
At the end of all DDL statements:
Do you have Enterprise Edition? A database snapshot would make this extremely clean.
The other thing you can do, and what I've done, is keep a rollback script handy for each change. If it fails, run the rollback to that point.
I've viewed a deployment as a go-no go chance. If one thing fails, everything rolls back, not just that one part. If we decide to do a partial deployment, I'd rebuild the roll forward/roll back scripts.
answered Oct 28 '09 at 12:26 PM
Steve Jones - Editor ♦♦