Selecting the Report | Data menu displays the dialog below.  This dialog gives you a lot of power in one small place.  From the Recover menu, I can select a single transaction and generate a command to undo that transaction.  But, what if I want to do multiple transactions?  That is where this dialog comes in handy.  Selecting the Committed User Transactions will generate a script to back out every committed transaction currently contained in the tran log.  The All transactions will do the same, but for any transaction in the log whether or not it has been committed yet.  The four checkboxes can be used independetly or together.  You can generate a file containing undo or redo SQL for a particular user, object, start time, or end time.  The start time and end time give you the ability to define the bounds of time on a set of transactions.  Anything on or after the start time will be picked up and anything on or before the end time will be generated.  You can output to a file or to the screen.  Finally, you can generate the same undo, redo, and report outputs as shown previously.

The Report | Syslogs menu brings up the same dialog box as above except the Committed Transactions, Undo SQL, and Redo SQL and greyed out.

Now that I breezed through everything that was in the interface, it all seems pretty simple.  Even though it is simple, the Log Analyzer provides you with one of the most powerful tools in a production environment.  How often have you heard that once a transaction is commited, it is done and there is nothing you can do about it?  Guess again.  You now have the ability to selectively roll back transactions and restore data to its previous state without going to a lot of effort.  In a large environment, this can easily save many hours of time and frustration for the DBAs.  For example take the case where a user accidentally modified a row early in the day and only realized it toward the end of the day.  In the meantime you had performed 8 tran log dumps.  The user can't tell you exactly when it happened, but only give you a ballpark.  In order to get the data back, you would have to go on a search mission by restoring a full backup to another server, successively applying a tran log, and checking with the user if that is the way the data should be.  You would then need to generate a SQL statement to put the data back the way it was before.  This is all fine until you realize that restoring the database will take 7 or 8 hours and needs more space than you have available on your servers.  You can't roll back tran logs on the production database, because everyone else would lose data.  What you can do is successively load the tran logs into the Log Analyzer and locate the offending transaction.  You can then generate a script and back that transaction out.  Problem solved, another happy user, and you'll still make it home on time for dinner.  (You should never let on to users that you have that capability unless you want to create a lot of work for yourself.  I'd also make the users sweat and wait a couple of hours when you do this, so that they are even more careful when doing data entry in the future.)

This also has a significant impact on your uptime.  Right now, many people do data recovery by applying backups to the database.  It might be necessary to revert a database to the state it was in at 8AM this morning.  For very large databases, it can take a significant amount of time to restore and while that is happening no on can use the database.  The vast majority of activity is simple selects against the database.   What you can do instead is to revoke editing permissions of the database, use the Log Analyzer to reverse engineer all transaction applied since 8AM, run that script against the database, and grant editing permissions.  This gives you the ability to revert the database to an earlier state, but the users experience no downtime.

Now, this is one method to overcome that problem.  It should be very strongly noted here that even though the Log Analyzer gives you the ability to reverse engineer your transactions, it is by no means a replacement to a sound backup strategy.  This is a tool that can become a very powerful addition to a well run production environment.

The Log Analyzer is produced by Platinum technology, inc.

Log Analyzer 2.02 1 2 3 4 5 6