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.
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 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.
I did not need to make a single reference to technical support while using the tool.
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.
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.
|Ease of Use
||Pretty much everything you would expect out of the tool based on what it's advertised to do.
||Speed problems aside, the functionality received for the price paid makes this a must have and it's priced right.
||I was able to install and operate without reference to technical support.
|Lack of Bugs
||Not a crash.
||Adequate and clear. Nothing wrong with it.
||It was really, really slow. Not slow enough to preclude using the tool, or I would have given it a "1" However it was slow enough to make using the tool somewhat painful.
||It couldn't be easier.
||It's very easy to use, but you need to understand how SQL Server logs work in order to appropriately use the tool.
||I'd give it a "5" if it wasn't for the speed.
Web Site: http://www.red-gate.com/sql/sql_log_rescue.htm
Developer: Red-Gate Software
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)