Database reckless modifications. ROLLBACK and COMMIT cure.

One day you might be forced to run some T-SQL scripts on production database and accidentally delete/update all records in a table instead of specified in where statement. It happened to me in a past it might happen to you as well. Usually you think that 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 for new queries:

BEGIN TRAN

...

ROLLBACK

Use transaction for all your modifications. Inside 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 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:

IdFirstNameLastName
1ViktarKarpach

(No column name)
1

This SQL Server behaviour can be fixed by using SET XACT_ABORT ON, so 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 Wednesday, February 17, 2010 by | Add Comment

New Comment

Your Name:
Email (for internal use only):
Comment:
 
Code above:

Categories

Recent Tweets

  • Simon Ince's Blog: Hierarchies with HierarchyID in SQL 2008 http://t.co/xSDwiF6rRS.
  • Visual Studio 2010 WAS painfully slow - CodeProject http://t.co/Usba1x6CZy

Valid HTML5