KARPACH

WEB DEVELOPER BLOG

Database reckless modifications. ROLLBACK and COMMIT cure.

One day you might be forced to run some T-SQL scripts on a production database and accidentally delete/update all records in a table instead of specified in a where statement. It happened to me in the past it might happen to you as well. Usually, you think that the world is over. Then you remember about backup, but still, there is a possibility that some data would be lost forever. The best solution is as usual prevention of a disaster.

There is a reason why SSMS Tools pack by default creates the new queries:

BEGIN TRAN
 
...
 
ROLLBACK

Use transaction for all your modifications. Inside a transaction, you can test if your update/delete does exactly what you want it to do.
Here is a little illustration:

CREATE TABLE TestTable
(
   Id int identity primary key ,
   [Description] varchar(max)
)
GO

BEGIN TRAN

insert into TestTable
values
('Test1'),
('Test2'),
('Test3')

select * from TestTable -- First query, returns all records

delete TestTable
where Id=2

select * from TestTable -- Second query, returns two records

ROLLBACK -- COMMIT

select * from TestTable -- Third query, no records because of ROLLBACK

Results:

Query 1:

1 Test1
2 Test2
3 Test3

Query 2:

1 Test1
3 Test3

Query 3:

After you are done with your testing, just change ROLLBACK to COMMIT.

Update:

BEGIN TRAN and ROLLBACK protects you from logical mistakes and syntax errors. However, some exceptions stop execution leaving the transaction open. For example:

BEGIN TRAN

CREATE TABLE Clients
(
        Id INT PRIMARY KEY,
        FirstName VARCHAR(50),
        LastName VARCHAR(50)
)

INSERT INTO Clients VALUES (1,'Viktar','Karpach')
INSERT INTO Clients_No_Such_Table VALUES (2,'Joe', 'Doe')
INSERT INTO Clients VALUES (3,'Mike', 'Skott')

SELECT * FROM Clients

ROLLBACK

Results:

(1 row(s) affected)
Msg 208, Level 16, State 1, Line 11
Invalid object name ‘Clients_No_Such_Table’.

As you can see SELECT statement was never executed:

SELECT * FROM Clients
SELECT @@TRANCOUNT

Results:

Id FirstName LastName
1 Viktar Karpach

(No column name)
1

This SQL Server behavior can be fixed by using SET XACT_ABORT ON, so a default query snippet becomes:

SET XACT_ABORT ON

BEGIN TRAN

...

ROLLBACK

Or you can use a longer version snippet with BEGIN TRY / END TRY:

BEGIN TRY
      BEGIN TRANSACTION
            SET NOCOUNT ON                                      
            
                ...                       
            
            --ROLLBACK
            COMMIT TRAN -- Transaction Success!
END TRY

BEGIN CATCH

      IF @@TRANCOUNT > 0
              
            ROLLBACK TRAN --RollBack in case of Error

            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;

            SELECT @ErrorMessage = ERROR_MESSAGE(),
                     @ErrorSeverity = ERROR_SEVERITY(),
                     @ErrorState = ERROR_STATE();

            -- Use RAISERROR inside the CATCH block to return 
            -- error information about the original error that 
            -- caused execution to jump to the CATCH block.
            RAISERROR (@ErrorMessage, -- Message text.
                              @ErrorSeverity, -- Severity.
                              @ErrorState -- State.
                                    );
END CATCH
Posted on February 17, 2010 by