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

avatar image

NYSystemsAnalyst
312 5 7 10

(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

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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

avatar image

Steve Jones - Editor ♦♦
5.1k 79 93 87

(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.

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:

x2017
x1069
x87

asked: Oct 28, 2009 at 12:20 PM

Seen: 2198 times

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

Copyright 2016 Redgate Software. Privacy Policy