Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Rescue Review

By Grant Fritchey,

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.

Ease of Use
Feature Set 4 Pretty much everything you would expect out of the tool based on what it's advertised to do.
Value 5 Speed problems aside, the functionality received for the price paid makes this a must have and it's priced right.
Technical Support N/A I was able to install and operate without reference to technical support.
Lack of Bugs 5 Not a crash.
Documentation 3 Adequate and clear. Nothing wrong with it.
Performance 2 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.
Installation 5 It couldn't be easier.
Learning Curve 4 It's very easy to use, but you need to understand how SQL Server logs work in order to appropriately use the tool.
Overall 3 I'd give it a "5" if it wasn't for the speed.

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)

Total article views: 9811 | Views in the last 30 days: 5
 
Related Articles
FORUM

Transaction Log Backup Script issues

Transaction Log Backup Script issues

FORUM

In Sql Server Can we recover new log file(.ldf) if we added that after full backup

In Sql Server Can we recover new log file(.ldf) if we added that after full backup

FORUM

Transaction Log Backup fails on Monday Morning

Transaction log backup can't find full backup

FORUM

Recover data from TRUNCATE

Recover Data

FORUM

SQL SERVER 2008 Backup job/Script

BACKUP JOB/SCRIPT

Tags
product reviews    
reviews    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones