Minimizing risk when undoing or redoing operations with Log Rescue

by Andras Belokosztolszki

SQL Log Rescue enables you to see the history of your database and generate scripts to undo or redo individual transactions. This is a great help if you need to recover a dropped table or reverse the actions of a particularly malicious or incompetent user. Care must be taken, however, about what actions are reversed, or your database can easily end up in an inconsistent state.

A simple example that demonstrates this danger is a database table that stores account balance information. In this database, transactions involve two accounts: one to be debited, and the second to be credited. A consistency rule for the database would be that the sum of all debits equals the sum of all credits.

Stored procedures together with transactions can ensure that these two parts of a transaction are always executed together, thus maintaining consistency. But undoing only a part of such a transaction would leave the database in an inconsistent state. Moreover, statistical information might be periodically recorded about the transaction amounts. Undoing a money transfer transaction would invalidate these statistics. 

Maintaining transaction features

All operations on a database are performed as transactions. A transaction is basically a sequence of operations executed as a single atomic operation. Transactions do not see partial results, or temporary data of other transactions. Once a transaction is performed, the changes that are made to the database persist.

One way of maintaining transaction features is to use write-ahead logging. This technique records information about transactions to one or more files. These files are known as transaction logs in SQL Server. They contain entries for every database operation that changes data. Here's an example of a log record sequence for a simple money transfer transaction:

• Transaction myTransaction begins.
• The balance column of the accounts table in the row with the 123 identifier changes from $5,000 to $10,000.
• The balance column of the accounts table in the row with the 345 identifier changes from $25,000 to $20,000.
• Transaction myTransaction committed.

It is important to note that this log entry does not say anything about stored procedures, or about what database tables and rows were read. It records changes only. In step 2, for example, the $5,000 amount indicates the current value of the 123 account at the time of the financial transaction, but the transaction log does not store the fact that $5,000 is the balance information.

Let's say that account history follows this sequence:

01 January 2005 $5,000
10 January 2005 $10,000
15 January 2005 $3,000

There were two transactions in the account's history; one credited it with $5,000, and the second debited it with $7,000.

We would like to undo the $5,000 deposit and see a minus $2,000 final balance. But from the database's perspective there were two operations: Change value to $10,000, and change value to $3,000. Thus executing an undo operation of the first transaction after the last transaction would change the balance to $5,000. This is not what we wanted (although $5,000 clearly sounds better than minus $2,000).

Logic can't be recorded

So why can't SQL Log Rescue calculate the difference and subtract $5,000 from the final amount? The answer is simple: There is logic in the transaction that cannot be recorded in the transaction log.

In this example, we want the difference to be deducted, because the original operation was an addition. But what if the original operation was a multiplication? Furthermore, what does it mean to undo a financial transaction? The stored procedure that performs the money transfer might only allow a transaction to occur if sufficient funds are available. Therefore, if the first transaction – depositing $5,000 – had not been performed, the second transaction would never have occurred either, since that would have left the account balance negative.

SQL Log Rescue shows transactions

This logic, of course, is not present in the transaction log, therefore SQL Log Rescue cannot handle it. SQL Log Rescue does enable you, however, to see the history of particular data records, and to compensate for it. It can show, for example, the history of the above account, and knowing the positive balance requirement you can decide what database operations need to be undone, and adjust the final balance manually.

Operations are part of a transaction that takes the database from a consistent state to another consistent state. This requires that you consider the transactions associated with the operations you want to undo. Probably you will want to undo all the operations of these transactions as well. This is one of the reasons that SQL Log Rescue shows you the transactions associated with each specific operation. If you group the log entries by the transaction ID, it allows you to select, in a single step, all the entries that belong to a particular transaction.

Note that the transaction log stores only data modification information. Reading from the database is not recorded in the transaction log. This causes some hidden dependencies.

Until now we were considering a single transaction. Now let us assume that we record some daily statistics about the total transferred amount. In the transaction log there is absolutely no information about the relation between the transaction that calculates and writes the statistics to the database and all the transactions that affect the data on which statistics are calculated. Undoing some financial transactions would require redoing statistical calculations, but the transaction log misses both the relation of the transactions as well as the calculation itself. It is therefore vital that one does not just blindly undo transactions in the database.

A few rules to follow...

There are a few rules worth following when undoing or redoing operations with SQL Log Rescue:

• It is generally safe to recover a dropped table that no longer exists in the database.
• If you want to undo a particular operation, then have a look at its data-row history. This tells you whether the row in question has been further modified, or whether there is no need to undo the operations, since the row is deleted already.
• Once you look at the operations you want to undo, examine all the other operations that have been performed in the same transactions associated with your selected operations. Generally it is a good idea to undo operations on a per-transaction basis.
• Consider all the other transactions that occurred after your first selected operation, and try to determine hidden dependencies based on the stored procedures or applications that access the database.  Hidden logical dependencies might require that you adjust data that is affected by these remaining transactions.

SQL Log Rescue is a very powerful tool that protects users from lost data and eliminates expensive downtime, but it cannot go about its job blindly. It requires that users consider the ramifications associated with undoing certain transactions.

Andras Belokosztolszki is a senior programmer with Red Gate Software, and a principal developer of SQL Log Rescue.