Introduction

SQL Log Rescue is yet another focused tool from Red Gate. It's used to view and recovery SQL Server 2000

data from the transaction logs. This enables you to recover from mistakes, bad data inserted, deleted, or

updated, that have been committed to the database. Using the tool you can brow the log to look at

transactions, browse row history, recover deleted data, undo updated data, and recover dropped & truncated tables.

It works with either native SQL Server backups or backups created using SQL Backup from Red Gate (a backup compression tool).

It's not the kind of tool that you'd drag out for a casual scroll through the transaction log. If you need to get

back a copy of the financial data (your raise is in there) or you've got to provide a record of changes to comply

some of the more arcane parts of Sarbanes-Oxley, then this tool will be invaluable.

Environment

I tested the client from my company laptop. It's a Dell Latitude D600 running a 1.4ghz Pentium with 2GB of Ram

on Windows XP Pro, Version 2002, SP2. I directed the client at several different servers, including my local

copy of SQL Server 2000 SP3a and a test machine running SQL Server 2000 SP3a on Windows Server 2003 Standard with

duel hyperthreaded Xeon 3.2ghz processors with 4GB of Ram. I worked against small databases for the most part;

4GB, but I did run some tests against a copy of a medium sized production database; 25GB.

Installation

Installation of the client is extremely simple. Follow the prompts and the tool will be installed. It does

require an extended stored procedure, xp_LogRescue, be created on each server from which you intend to work on

log recovery. This can be done directly from the client to any server where you need it. It doesn't need to

be installed prior to attempting to recover logged operations since the tool operates directly from the native

SQL Server log and backups.

Using Log Rescue

Getting started working with Log Rescue is a simple matter of following the wizards. You're prompted to create a

new recovery project when app opens for the first time. You simply connect to a server, select a database,

designate backups, if any, from which recovery information can be gleaned, and install the extended procedure if

prompted and then you're off to the races. The tool scans the log backups and the current, active, log on the database

and presents the information as a series of transactions. This is the one, and only, shortcoming I found it in the

tool during all the operations I did for testing. The initial scan and load is extremely slow. It took almost

two minutes with a small database (4.5GB with a 1.4GB log), and nearly 20 minutes with a medium sized one (12.9GB database with a 5GB log). The medium sized one

was backed up every 15 minutes, so the log isn't very full, but I included 24 hours worth of log backups totaling 5.7GB in size.

Each action that the log records has enough information that you can discretely identify them when browsing through

the tool. Time, Table, User, Action Type and Transaction Id are laid out in a grid. Selecting any one of the rows of

the grid updates the data in the detail window below it. The detail window can be shown as Row Data, Row History, Undo

Script, or Redo Script. The Row Data gives precise information about the action selected. In addition to the columns

listed above, it includes the Log Sequence Number and a view of the precise data affected. In the case of an update,

the data before and after the update are shown. Browsing through this information was quick and simple and clear.

Row History shows the various actions taken against the row of data represented across all of the log currently

being explored. You can see when the row was created, updated and deleted, across the entire life of the row. If

multiple updates took place during a day of operation and you've got the log backup files, you'll be able to see, not

only who affected the row, but what exactly they did each time. Red Gate has made it easy to browse the information

because you can also apply grouping to change the list from a simple recording of each transaction to individual transaction

Id's or time, table, or type.

Browsing transactions is nice, but fixing bad transactions is great. This is where the Undo and Redo scripts come

into play. Displaying either of these windows will show a TSQL script for either rolling back the transaction or

performing it a second time. These scripts can be copied for storage or use later. The Undo & Redo operations were very

easily accessible from buttons on the top of the screen. I could simply check the transactions that I wanted to recover

and click these buttons in order to start generating scripts.

You are not required to run the scripts through Query Analyzer. You can simply have them be applied by the tool, on

the spot. When you fire the "Undo Operations" or "Redo Operations" process, you're given the option of running the

script directly from the tool or launching the script in Query Analyzer. I would recommend keeping a copy of the

script around for auditing purposes, which the Operations window will give you the option to do. The best thing about

it is, you've got the transaction line in front of you so that after performing one operation, say an Undo, if you

decide that was an incorrect action, you can simply fire the opposite operation, a Redo, to get things back to the

way they were, from the same interface, no other actions required. While performing these operations, the tool keeps

track of possible issues, such as triggers, foreign keys and cascading deletes needing to be disabled during the

operation. The scripts generated are clear, easy to read, and about as safe as this type of evolution could be. They

ran very well during my tests.

Recovering a dropped table is almost as simple as recovering a deleted row. There is a catch. SQL Log Rescue needs a full backup in order to capture the original table structure and data, from which it will rebuild the table and data, and apply all the changes to that table up to the moment of the drop table statement. I found this operation a bit difficult to test only because by default we use SQL LiteSpeed for all our back up operations, so I had two choices. Either use the conversion utility included with that third party tool, or run a separate, native SQL Server, backup. Either way worked fine. Red Gate also offers a backup utility, similar to SQL Lite Speed, with which SQL Log Rescue will work. This functionality was not tested during my review of the product.

Log files, and the subsequent transaction lists, can be pretty huge. There's a nice little utility right on the front of the interface where you can type in a text string and it will search through table names as well as Old and New values in the row data. this makes it very easy to track down the bad bits of information that you want to remove, or the important bits you want to recover.

Support

I did not need to make a single reference to technical support while using the tool.

Conclusions

This type of tool is a must have. What do you do when a user on the updates data, central to the system, in the middle of an active business day? You can't simply run a restore without rolling back all the other thousands of transactions that have occurred. This tool will allow you to do that. For that reason alone, it's probably going to become one of your most valuable assets. Do have junior, or less careful, DBA's with access to the system? Have you ever lost a table or three during the course of a business day. You can recover dropped tables with this tool.

The interface is clean and intuitive. The icons and terminology used make navigation easy and quick. The various views of the transactions and the ability to group by various data points improve browsing. The recovery scripts are well written. Once the log has been read, the tool is very fast when grouping, regrouping, browsing and generating scripts.

It would be nice if it was a bit faster when loading the logs. Reading a large set of transactions into the tool, and let's face it, if you don't have a large set of transactions, this tool begins to lose some value, takes far too long. Using a one day sample of our production system, with log backups running every 15 minutes, LogExplorer placed locks on the backup files while it was reading for so long that the 15 minute backup fired while it was reading, and failed. This isn't a show stopper because once the log was read by the tool, the file was available for backups again, but it could cause problems depending on how tightly managed your log backups become. It does require that backups being referenced be either SQL Server native or Redgate Backup compressed files. If you're using a different third party backup mechanism, functionality will be reduced or extra steps have to be taken.

All of the functionality provided, combined with the price, make this worth purchasing and using, but the loading speed really needs to be improved.

Ratings

I will rate each of the following using a scale from 1 to 5. 5 being the best and 1 being the worst. Comments are in the last column.

Product Information

Web Site: http://www.red-gate.com/sql/sql_log_rescue.htm

Developer: Red-Gate Software

Pricing:

Single Machine - US$295 ($369 w/ support and upgrades)

5 Machine license - US$890 ($1,113 w/ support and upgrades)

10 machine license - US$1,590 ($1,988 w/ support and upgrades)