With its powerful functionality that lets DBAs analyze and isolate database transactions, generate T-SQL scripts, and recover data from user accidents, Lumigent Technologies' Log Explorer for SQL Server has been extolled in numerous reviews. In fact, many SQL Server gurus have gone on record claiming that if they had only one tool to help them with DBA tasks, Log Explorer would be it. But that was before Red Gate Software entered the scene with SQL Log Rescue. How do the two products stand up in a head-to-head comparison? Is Red Gate's SQL Log Rescue worth switching to? Or should DBAs the world over stick with tried-and-tested Log Explorer? In this review, I'll pit the products against each other. I think you'll find the results quite surprising.
The marketing folks at Lumigent and Red Gate would have you believe that recovery from user error requires nothing more than a few clicks to generate a SQL script,which you can run against your database to fix everything. True, both tools make the task of recovering accidentally modified data drastically easier than restoring a copy of the affected database and recovering data by hand. However, recovery of production data in a heavily trafficked system is typically more complicated than merely getting a copy of data as it was before it was erroneously modified or deleted.
As anyone who has been there knows,the trick is integrating all of the salvaged data without overwriting or losing modifications that occurred after the data was incorrectly modified. For example, if someone in shipping accidentally sets inventory levels for all products to a value of 500 units, recovering inventory values back to their original, accurate levels (just before that mistake) is only one part of the solution.The other part is decrementing and incrementing inventory levels after the mistake to restore an accurate representation of inventory levels. (And the trick is to do all of this without taking the system down for maintenance.) Obviously, managing all of this complexity by hand is an absolute nightmare in even the best environment and can be effectively impossible with some databases.
Fortunately, both Red Gate's SQL Log Rescue and Lumigent's Log Explorer let you not only undo transactions, but redo (or replay) them.With this functionality, you can 1) locate the mistake, 2) undo it and create a T-SQL script for recovering the data as it existed immediately before the mistake, and 3) replay subsequent, legitimate, transactions against the affected data to put it in order. So that's exactly how I evaluated both products in this review—according to their ability to isolate user errors, undo mistakes, and replay subsequent transactions to bring everything back to the proper order. (See Table 1 which shows a comparison of features, and Table 2 which shows my detailed rating and impressions of features.)
LOG EXPLORER 4.0 FOR SQL SERVER
PROS: Excellent support for Data Definition Language (DDL) operations; provides auditing and alert functionality; excellent interactive tutorial
Although Lumigent's Log Explorer provides excellent, dependable functionality, the UI feels a bit cluttered at times. (Figure 1 shows an example of the UI, the transaction log browse screen.) Doubtless, this cluttered appearance is just a side effect of the product's growth through successive versions, but I think the product could benefit from a facelift, at least to provide easier navigation of transactions. For example, although Log Explorer offers specialized Row Revision History and RowTransaction History panes or views, both of these panes require you to filter data in a targeted table by at least one of the table's domains. Worse, the filtering interface lets you use only equality operators; therefore, you can't use a filter such as "CustID > 1," so you have to know exactly what data you're profiling, which can hinder browsing and other analysis.
This UI quibble aside, Log Explorer is an excellent product. It easily passed all my testing requirements, letting me recover data just as expected. Beyond meeting the basic need to recover data, Log Explorer also provides you with the ability to audit all logins against a given SQL Server (storing login and logout data in any database you specify), and set up email alerts against any suspicious operations the product detects on the server. I also found Log Explorer's export functionality helpful in generating reports about how data was corrupted and even recovered. It's also worth noting that Log Explorer provides better explicit handling of Data Definition Language (DDL) recovery operations— more so than Red Gate's SQL Log Rescue.
Overall, Lumigent's Log Explorer is an excellent product—one that I can heartily recommend. My major criticism of the product is its price, at least when compared to Red Gate's SQL Log Rescue, which provides-similar core functionality at a fraction of Log Explorer's $1680 single-license cost.
SQL LOG RESCUE 1.0
PROS: Excellent UI; unbeatable functionality for the price
Without a doubt, Red Gate's SQL Log Rescue presents viable competition for Lumigent's Log Explorer. Although SQL Log Rescue doesn't provide explicit support for most DDL recovery operations (the functionality to recover from DDL operations exists, but isn't listed in the UI), the product provides comparable functionality at a fraction of the cost.The thing I enjoyed most about Log Rescue was its navigable and easy-to-use UI. Possibly Log Rescue's coolest feature (beyond its ability to recover data, of course) is the UI's grouping functionality that lets you easily slice and dice data, rearranging it in whatever ways make sense while you try to locate offending transactions. For example, although the UI default view shows each operation in a normal tabular display, I can group transactions by Table, then by User. All I need to do is drag the Table column to the grouping pane, then drag the User column to the grouping pane, and voila! I've now grouped data by table and by user modification to each table (as you can see in Figure 2, which shows grouped data in the top pane). Using this grouping functionality, I found that I could easily isolate specific transactions and also evaluate and analyze overall patterns and activities. Better yet, Log Rescue also lets you search loaded log and backup data, which makes the process of finding offending transactions a breeze. Additionally, Log Rescue's interface arranges new values and old values side by side for easy visual comparison (as you can see in Figure 2, in the Row Data pane).
Functionally, SQL Log Rescue easily passed all my testing requirements. The product let me isolate a mistake, undo the mistake with a script, and replay subsequent transactions against the restored data to bring everything to where it logically should have been. In fact, in terms of recovery functionality, Log Rescue can confidently compete with Log Explorer. Although Log Rescue doesn't offer the same degree of support for recovery of DDL operations, Log Rescue does offer better support for binary data recovery. Log Rescue also makes loading backup and log data easy by offering to prepopulate options from the backup history stored in the msdb database, which is a nice touch. So, despite its few limitations, I'm impressed with Log Rescue. What impresses me most about it is the fact that Red Gate was able to deliver such a great product in a version 1.0 release and at only $195 for a single license.
Both products provide similar core functionality, but each has its own strengths and weaknesses that you'll need to evaluate in terms of your organization's requirements. (Although it didn't fit within the scope of this review, ApexSQL Log, from ApexSQL, offers similar functionality to both of the products reviewed in this article, including support for SQL Server 2005.) If price weren't a concern, I'd still choose the less expensive SQL Log Rescue because of its vastly superior UI. However, because price is always a concern, Log Rescue is the obvious choice for most environments, unless you need comprehensive DDL recovery functionality or want auditing and alerting. At $195 per license, I don't think any organization can afford NOT to buy and deploy Red Gate's SQL Log Rescue.