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

0

Here is what I do:

Before start of any DDL changes:

IF OBJECT_ID('[tempdb]..[#SchemaModificationErrors]') IS NOT NULL
BEGIN
  DROP TABLE [#SchemaModificationErrors]
END
GO
CREATE TABLE [#SchemaModificationErrors] (
  [Error] [int] NOT NULL,
  [Message] [varchar] (MAX) NULL,
)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION 
GO

Before any single DDL statement:

BEGIN TRY

After any single DDL statement:

END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION 
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
IF @@TRANCOUNT = 0
BEGIN
  INSERT INTO [#SchemaModificationErrors] ([Error], [Message]) SELECT 0, '@@TRANCOUNT was 0 after DDL Operation, implicit ROLLBACK detected.'
  BEGIN TRANSACTION 
END  
GO

At the end of all DDL statements:

DECLARE @errorCount int
SELECT @errorCount = COUNT(*) FROM [#SchemaModificationErrors]
IF ISNULL(@errorCount, 0) != 0
BEGIN
  IF @@TRANCOUNT>0 
    ROLLBACK TRANSACTION
  if @errorCount = 1 
  BEGIN
    PRINT 'Schema modification failed with 1 error.'
    SELECT [Error], [Message] FROM #SchemaModificationErrors
  END
  ELSE
  BEGIN
    PRINT 'Schema modification failed with ' + convert(varchar, @errorCount) + ' errors.'     
    SELECT [Error], [Message] FROM #SchemaModificationErrors
  END
END
ELSE
BEGIN
  IF @@TRANCOUNT>0 
  BEGIN
    PRINT 'Schema modification successful'
    COMMIT TRANSACTION
  END
END
GO
DROP TABLE #SchemaModificationErrors
GO

answered Oct 28 '09 at 12:29 PM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

I've been playing around with this. It seems that if your DDL statement references a table that does not exist, then the error is not caught in the CATCH block.

Oct 28 '09 at 01:16 PM NYSystemsAnalyst

No, if you look at this http://technet.microsoft.com/en-us/library/ms179296.aspx about a quarter of the way down it states that object name resolution errors aren't caught. That's why immediately afterwards there is the IF @@TRANCOUNT = 0 check...

Oct 28 '09 at 01:22 PM Matt Whitfield ♦♦

Then this solution will work most of the time. If one of the DDL statements causes one of these un-caught errors (meaning no entry into the errors table), then the DDL statements that followed that error will always be committed because you begin a new transaction after each Try...Catch block.

Oct 28 '09 at 02:01 PM NYSystemsAnalyst

Very good point. In that case, I believe the IF @@TRANCOUNT = 0 bit should then insert a row into the schema modification errors table saying 'hang on, i expected trancount to be 1'... What do you think?

Oct 29 '09 at 08:11 AM Matt Whitfield ♦♦

I have modified the 'after any single' bit to incorporate that now.

Oct 29 '09 at 08:14 AM Matt Whitfield ♦♦

(comments are locked)

Answer 2

0

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 ♦♦
5.1k ● 74 ● 78 ● 82

No, we don't have Enterprise Edition.
Yes, I agree that the upgrade should be an all or nothing procedure. But, it seems that writing a rollback script is basically doubling the work. IMHO, there should be a way to just write the roll forward script, and if it fails at any point, the entire thing gets automatically rolled back. It seems like this is the basic idea behind database transactions.

Oct 28 '09 at 12:31 PM NYSystemsAnalyst

(comments are locked)

Atomic Upgrade Scripts

Follow this question

Related Questions