Atomic Upgrade Scripts

Question

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:

SET XACT_ABORT ON;
GO

BEGIN TRANSACTION;
GO

CREATE TABLE dbo.Customer
(
     CustomerID int NOT NULL
    , CustomerName varchar(100) NOT NULL
);
GO

CREATE TABLE [dbo].[Order]
(
     OrderID int NOT NULL
    , OrderDesc varchar(100) NOT NULL
);
GO

/* This causes error and should terminate entire script. */
ALTER TABLE dbo.Order2 ADD
    A int;
GO

CREATE TABLE dbo.CustomerOrder
(
     CustomerID int NOT NULL
    , OrderID int NOT NULL
);
GO

COMMIT TRANSACTION;
GO

Answer 1

The way Red-Gate and other comparison tools work is exactly as you describe... they check @@ERROR and @@TRANCOUNT after every statement, jam it into a #temp table, and at the end they check the #temp table. If any errors occurred, they rollback the transaction, else they commit. I'm sure you could alter whatever tool generates your change scripts to add this kind of logic. (Or instead of re-inventing the wheel, you could use a tool that already creates atomic scripts for you.)