Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let's suppose that we are planning to execute a "risky action" on a database. Naturally we've tested on non-production databases and everything seems to be fine. We backup the database. Then we execute the risky action. After some number of additional transactions, we realize a horrible mistake. Now we'd like to "undo" the risky action but maintain the subsequent transactions.

I am aware that crisis tools exist that will help undo transactions based on transaction logs. Since I don't actually have a crisis, I am curious about whether this can be accomplished without those tools in the right situation.

I have in mind that something like the following could be accomplished, but I don't know whether it's actually possible on SQL Server.

Timeline of our invented crisis

  1. Make full backup of database (B)
  2. Execute risky action (T1)
  3. Transaction 2 occurs (T2)
  4. ...
  5. Transaction n occurs (Tn)
  6. Realize that we need to undo T1.

"Undo"

  1. Restore B into a new database (B')
  2. Somehow apply T2 through Tn to B'

Is this something that can be done with built-in commands? I've tagged SQL Server 2008 since that is the package I most frequently use. However, if a solution also works on SQL Server 2005 (or only on 2008 R2), I'd like to hear about that too.

share|improve this question

2 Answers 2

Not possible exactly as you intend, but here are some tools:

For your point in time, try a DB Snapshot:

http://msdn.microsoft.com/en-us/library/ms175876.aspx

To revert from DB Snapshot:

http://msdn.microsoft.com/en-us/library/ms189281.aspx

Now that would revert in total, not leaving the intended T2, but would keep you from having to deal with restoring backups.

What you could do is with the DB still live and with a DB Snapshot available, then use a tool like Redgate for DML compare (compare live DB to DB Snapshot):

http://www.red-gate.com/products/sql-development/sql-data-compare/

You'd have to then pick and choose what DML you wanted to apply, and use Redgate to generate the script. Then you could revert with the snapshot, and apply the Redgate script to include only the DML you wanted to retain.

That's as close to meeting your requirement as comes to mind. Otherwise there's no way to say "roll this one back and not that one".

share|improve this answer
    
A data comparison tool could work. But, as you say, I'll still have to do the hard work of figuring out what T2 did and what the rest did. Thanks for the tip! –  Eric Feb 15 '12 at 18:52

Undoing the T1 transaction only without changing the subsequent transactions is not possible without a 'crisis tool'. Restoring to a point in time would 'undo' all the transactions from T2 to Tn and you would again need a 'crisis' tool to redo them. The good news is that RedGate Rescue can do that and it's free. The bad news is that it supports only SQL Server 2000. ApexSQL Log supports more versions, but you have to pay for it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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