x

Atomic Upgrade Scripts

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
more ▼

asked Oct 28, 2009 at 12:20 PM in Default

NYSystemsAnalyst gravatar image

NYSystemsAnalyst
312 5 5 6

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Oct 28, 2009 at 12:29 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 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, 2009 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, 2009 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, 2009 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, 2009 at 08:11 AM Matt Whitfield ♦♦
I have modified the 'after any single' bit to incorporate that now.
Oct 29, 2009 at 08:14 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 28, 2009 at 12:26 PM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 77 79 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, 2009 at 12:31 PM NYSystemsAnalyst
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1950
x987
x69

asked: Oct 28, 2009 at 12:20 PM

Seen: 1930 times

Last Updated: Oct 28, 2009 at 12:20 PM