SQL Server – How to Revert a Transaction After a Certain Number of Days

sql serversql-server-2008

I have a Sql script file which does a bunch of operations. I have transaction inside the script so if something happens the whole script is rolled back.

The question I have is that say the script executed successfully and while testing the application certain errors were found.

Now I want to revert all the operations that happened inside the script after say x days. This is a transactional db and I don't have to worry about any new data addition since this is just a staging environment..All I care about is to revert all the structural or data modifications that the SQL Script did by itself. Can someone please tell me what the options are for that.

Do I need to write the exact opposite statements in the rollback script for the operations that the original script did.

Example:
Main Script -Insert Into Customers values(1,'Test','USA')
Rollback script – delete from Customers where id = 1 ..and so on for other transactions(I have simplified this I have over 300 operations in the SQL Script file)

Thanks

Thanks

Best Answer

There isn't anything built into SQL Server that will undo statements. Your options are:

Restore from backup or from database snapshot
Buy a third party development tool like Red Gate's SQL Compare & Data Compare - which may require creating another database, making your changes, and then comparing the differences between them
Buy a log reader tool like Quest Litespeed - which can read Litespeed's transaction log backups and generate undo scripts for specific transactions
Writing your own undo script - whatever changes you plan to make, write the undo scripts yourself to undo your changes

Related Solutions

Sql-server – Document versioning

The simplest way is to just store previous versions of the whole document rather than attempting to calculate deltas. Lots of systems do it that way even though the XML fields use a lot of space. It might be worth evaluating the data storage requirements for doing this before you go to whole lot of trouble to capture differences.

Sql-server – Transaction and Try-catch in SQL Server Job

I would rather recommend a pattern like the one from Exception Handling and Nested Transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end

This pattern checks the XACT_STATE() in the catch block to guard against uncommittable transactions:

Uncommittable Transactions and XACT_STATE
If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION. The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction. When a batch finishes, the Database Engine rolls back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application. This indicates that an uncommittable transaction was detected and rolled back.

Your code is checking for @@TRANCOUNT in places where it cannot be 0, it uses a mixture of informational PRINT messages and SELECT result sets for communicating success, it does not handle errors that are recoverable. Ideally the exceptions should propagate to the client, in this case to the Agent job (ie. your catch should re-raise).

Related Question