“Undo” some transactions from backups

Question

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.

Answer 1

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".

Answer 2

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.