2

I am coding up a script to merge some accounts (and other tables) as an out-of-band customer request. All the updates will be done in a single transaction. They are all updates (no inserts or deletes) - effectively changing the "account_id" of some entities to achieve the account merge.

That's all fine, but my boss wants to be able to roll back the affected accounts after other transactions, i.e. DAYS down the road on a very active server.

My ideas are: -

  1. print out all the affected rows (select all the previous data to console) so I could manually move everything back if required (there aren't too many affected tables - about 8)
  2. export the transaction and "somehow" roll that individual transaction back - without affecting any subsequent transactions.
  3. something else?

Many thanks!

  • What version and edition of SQL Server you are using ? If you are using SQL Server 2008 and up, then you can use CDC (Change Data Capture) -- this depends on your Edition as Enterprise has it and standard does not have it – Kin Shah Apr 11 '13 at 20:12
  • SQL Server 2008 R2 Enterprise. But I'm on a very tight deadline and will have to go with what I know and is of minimal risk (need to assess performance impact of CDC, etc). I have told boss about CDC for future improvements. Thanks! – David Kerr Apr 12 '13 at 11:58
1

There's no way to roll back a specific transaction like that using the native tools. There are (or at least there used to be) some third party tools which would let you do this, but it depends on a lot of things.

Your best bet will be to log the before and after values for all the rows that you change during out of band update, then if you need to roll back those changes you've got the needed values. You might want to log the new values as well as the old values so that you can see if the values have been since changed again so that you can then figure out if you want to roll back those changes.

  • Frankly this is why I believe that any non-trivial database needs audit tables. Any database that stores financial data needs them twice as much. – HLGEM Apr 10 '13 at 18:28
  • Thanks mrdenny. I'm already doing some logging, but I'm looking for something less manual, and also simple, if possible :). This solution will require manually making the reversions. The thing is, this rollback requirement is unlikely to be needed, so I don't want to spend too much time on it. – David Kerr Apr 11 '13 at 8:14
0

I had to do something similar. What I ended up doing was creating a 'staging_DB'.

Lets say that the table to be manipulated is called Accounts.

First, I did a 'select into' (for the rows that would be updated) into a table called Accounts_original. This table was created in the staging_DB and will contain the values of the rows before the update.

Then I inserted the to-be values into another table called Accounts_to_update which was also created in the staging_DB. In my case, this was just another 'select into'. Note that Accounts, Accounts_original, and Accounts_to_update all have the same table definitions.

Two scripts were created to update the Accounts table: update_rows_accounts.sql and reset_rows_accounts.sql. The update_rows_accounts.sql script updated the Accounts table with the respective values from the Accounts_to_update table. The reset_rows_accounts.sql updated the Accounts table with the respective values from Accounts_original table, thereby resetting the Accounts table values to its pre-updated state.

The update scripts were a bit tricky and had to use a join statement. Note that the primary keys should not be updated and are used in the joining clauses. Sample below:

begin tran
UPDATE [Accounts]
set  
-- Accounts.id=tab2.id (primary key)
[Accounts].[value1]=tab2.[value1]
,[Accounts].[value2]=tab2.[value2]
,[Accounts].[valuen]=tab2.[valuen]
FROM [Accounts] inner JOIN staging_db.dbo.[Accounts_to_update] tab2 on ([Accounts].[id]=tab2.[id])  

-- commit rollback

Hope this works for your scenario.

  • Thanks StanleyJohns. My problem is slightly complicated by needing to update FK references in about 8 tables. I think I will do something similar to your suggestion, but I'm thinking of a table of changes, which will have the following columns: "table", "old-value", "new-value". The rollback script will just reverse changes based on the entries. – David Kerr Apr 11 '13 at 8:16
0

I assume you will use table before update triggers to record the old and new values in your logging table. You might also need columns for: TransactionID with another table recording the transaction details (ie transid. user. Date. time incl secs and ns) OR Add user date time to the logging table.

To undo a transaction you will need to identify what to reverse and in what order. Latter can be tricky.

H

  • This is a manual intervention and there are no triggers. I am writing a script that makes an audit trail with what changed (before and after), the table and operation. A crude audit system, but it is HIGHLY unlikely we'll need rollback (careful developer, 2 x code-reviews, as-live test). – David Kerr Apr 12 '13 at 9:30
0

The solution I've finally gone for is based upon the reality that it is highly unlikely we'll need to rollback and we don't want the expense of a full audit system. I am an experienced developer, it will be reviewed by 2 x developers and thoroughly tested on as-live. The ROI doesn't warrant anything more!

Solution: -

  1. an audit-lite AccountMergeInfo table, just for this specific task, with columns
    • ticket - identifies the work ticket so this can be done again and kept separate
    • action - MOVE, DELETE, etc
    • tableName -
    • entityId - entity being moved - so it can be moved back
    • oldAccountId - where it came from so we can roll back
    • newAccountId
    • notes - some blurb, some field from the moved entity to help identification
  2. simple enough script that inserts a record into AccountMergeInfo table before doing the real update. 1 x SP per type of action being performed

    CREATE PROCEDURE sp_moveLicences(
        @ticket AS NVARCHAR(50),
        @fromAccountId AS VARCHAR(25),
        @toAccountId AS VARCHAR(25),
        @notes AS NVARCHAR(100))
    AS BEGIN
        INSERT INTO
            AccountMergeInfo (
                ticket, 
                action,
                tableName, 
                entityId,
                fromAccountId,
                toAccountId, 
                notes)
            SELECT
                ticket = @ticket,
                action = 'UPDATE',
                tableName = 'licences',
                entityId = licence_id,
                fromAccountId = @fromAccountId,
                toAccountId = @toAccountId,
                notes = @notes
            FROM 
                licences
            WHERE 
                owning_account_id = @fromAccountId;
    
            UPDATE 
                licences
            SET
                owning_account_id = @toAccountId,
                comments = comments + CHAR(13) + CHAR(10) + @notes
            WHERE
                owning_account_id = @fromAccountId;
    END
    
  3. A rollback script will at least have data in the database to work from

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.