SQL Server Transaction rollback

Question

I have the below code. This code catches any error in the script while it's being executed and rolls back all the changes that were made from the start. This solves my problem of rolling back the transactions if anything happens.

My question is if I have to write a rollback script(which can be used later on after testing the application) for all the transactions I made during this script execution do I need to go statement by statement and do the exact opposite

Eg - in main script I do Insert Into Star Values 1 then in rollback script I do delete from star where id = 1 or is there some other automated way of doing it.

Like we can can call the SQL Server transaction log somehow and tell it to reverse the transactions it did during our script execution later on.

--This works to roll back the changes during script execution
SET XACT_ABORT ON;
GO

BEGIN TRANSACTION
-- Batch 1
BEGIN TRY 
  CREATE TABLE Persons (
    PersonID int    
); 
END TRY
BEGIN CATCH 
  ROLLBACK TRANSACTION;
END CATCH;
GO  
-- Commit transaction
IF XACT_STATE() = 1
BEGIN
  COMMIT TRANSACTION;
  PRINT 'Transaction committed.';
END;

Ultimately this is what I want is to rollback the updates my script made at a later point in time..say 3 days after..So I run my script today and it makes a bunch of changes..After 3 days using the application I sense something is wrong so I want to undo all the changes that the script did.

I'm confused. It sounds like you just want to not commit the transaction at the end. I read your question 3 times, but still don't get what you mean. What's the script you speak of? Is it something else that is going to make a bunch of inserts and then you want to back out of this? — scsimon
Ok, so in that case I think you'd be better off just backing up your DB and running this in a test environment, unless you were able to keep track of everything you changed and reverse it. — scsimon
Backing db and restoring it is not an option..Is there any other way. — SP1
There is no other way in SS2008 without 3rd party products. Once the transaction is committed, you can't "undo" it, and there's no way to "reverse" your script because how will you know what to update the row to? There's no record of what the row was before the update. — Tab Alleman
There is no way to do that with transactions. If you want to "rollback" changes of data in the table, you can consider to use audit tables (data versioning). Whenever you change the data in your actual table, you store the old values into audit table by triggers. Then, once you want to "rollback" the changes, you can look into audit table, get old values for specific date and update an actual table. — Vitali

Mr Slim Mr Slim · Accepted Answer · 2017-09-15T10:38:58

Based on your question, I am finding it a little difficult to determine your actual real world use case. However, Database Snapshots MAY be an option for you. More details can be found in here in TechNet

In the event of a user error on a source database, you can revert the source database to the state it was in when a given database snapshot was created. Data loss is confined to updates to the database since the snapshot's creation.

However, the snapshot works at the DB level and would not be specific to the TRANSACTIONS initiated within your script, rather to all CRUD and DDL operations on the database.

SQL Server Transaction rollback

1 Answers