Log Explorer 3.03

Lumigent Technologies has released a new version of Log Explorer, one of the best-selling third-party products in the SQL Server space. Log Explorer reads a SQL Server 2000 or 7.0 transaction log and decodes the information into a readable format. Log Explorer lets you analyze transaction histories, but most important, it lets you selectively recover data. The newest release, Log Explorer 3.03, adds multiple enhancements to this valuable capability.

Selective Data Recovery

You can use SQL Server's own utilities for data recovery by restoring a transaction log to a point in time just before a data loss or corruption occurred—the last point when the data was correct. This capability is useful if you've lost data through an erroneous operation or transaction in the database. However, only one user can be in the database during the restore, so you must effectively take the database out of production while you perform the restore operation. When you use SQL Server's backup and restore facilities, you can't select just the transactions that you want to undo or redo. When you restore a log to the desired point in time, you also recover the database, and you can't restore the remaining correct transactions that come after that point in time.

Log Explorer enhances SQL Server recovery by letting you selectively recover data from a transaction log. You can browse a database's transaction logs, locate the bad operations or transactions, and generate a script to undo just those transactions. Depending on the context, you might not have to take the database out of production. If you use SQL Server to restore a database to a certain point in time, you can use Log Explorer to attach to the transactions that occurred after that point. Then, you can generate redo scripts to recover selected transactions that occurred after the failure.

Log Explorer 3.03 Enhancements

Log Explorer 3.0 builds on the important capability of performing selective recovery from transaction logs. I reviewed Log Explorer 3.03, which includes bug fixes for the 3.0 release. Let's look at the major enhancements in this latest release.

View DDL commands. Log Explorer 3.03 lets you browse data definition language (DDL) events, which it reconstructs by reading the transaction-log activity against a database's system tables. The View DDL Commands window, which Figure 1 shows, reveals changes to permissions and to system objects such as tables, views, functions, stored procedures, triggers, and indexes. You can also filter the DDL display to view only the commands you want to see.

Recover logged transactions containing BLOB data. Another enhancement is better support for binary large object (BLOB) data stored in text, ntext, and image data types. Log Explorer now supports all fully logged changes to the text and image columns. According to the Log Explorer 3.03 Release Notes, SQL Server doesn't always record BLOB data in the transaction log. For example, SQL Server doesn't log a delete of a row that results in freeing a text or image page (if the row was the only row on a physical page), nor does it log an update to a BLOB column. Because these operations aren't recorded in the transaction log, Log Explorer can't create scripts to recover them.

Recreate the schema of a dropped or truncated table. Log Explorer 3.03 can reconstruct the schema for a dropped table and insert it into the script that the Salvage Dropped/Truncated Table option generates. Earlier releases require you to manually recreate the table before applying the script.

Index filtered transaction-log records for faster browsing. When you're working with large transaction logs, especially logs that span many files, Log Explorer might have to scan data sequentially to find a particular transaction. You can direct Log Explorer to build an in-memory client-side index of your filtered result set on the transaction's log sequence number (LSN). This index exists only for the duration of the Log Explorer session and only in the Log Explorer client utility's memory space.

Log Windows-authenticated usernames. Because SQL Server writes only SQL Server—authenticated login names to a transaction log, earlier Log Explorer releases couldn't capture the Windows-authenticated username associated with a transaction. Log Explorer now displays the Windows NT 4.0 username with a transaction's data when you're browsing a log. Log Explorer 3.03 communicates with the SQL Trace API to capture the NT username of a server process ID (SPID), then stores it in a table called LUMIGENT_PROFILER. By default, this table is in the msdb database, but you can choose a different location at installation time. Log Explorer automatically purges rows from this table that are older than a specified number of days (the default is 30 days). You can configure this value by using the Server Manager utility, which I describe next.

Use Server Manager to control server-side components. Despite the name, the Server Manager utility doesn't manage servers—just the server-side components of Log Explorer. The Server Manager is a separate utility that you access through the Server Manager window, which Figure 2, page 30, shows. Server Manager lets you inspect and change several of Log Explorer's server-side registry parameters. For example, as Figure 2 shows, you can control whether Log Explorer captures NT usernames and how long it retains the data by entering the settings in the Session login Information Capture Mode text box. You can then click Update to send that information to the registry for the component to read. In earlier Log Explorer releases, you have to use T-SQL scripts to set these parameters.

Server Manager also lets you change the port that Log Explorer uses to connect to its server-side extended stored procedure component. Type the new port number in the Server Component Port# text box, and click Update to write the new port number to the registry. The Server Component box at the bottom of the Server Manager window lets you manage the Log Explorer server component's registry information. The Read Registry option tells the server-side component to read the registry in realtime (without requiring you to reload the component, which is a nice convenience); the Reload option reloads the server-side component, and the Unload option unloads the component. If you unload the component from a server, your Log Explorer utility won't be able to attach to any server's transaction logs until you reload the component on some server.

Use Log Explorer with Datacenter. Log Explorer 3.03 has Windows 2000 Datacenter Server certification, so you can use the utility on a Datacenter server. This certification means that the product survived extensive testing with Datacenter, so you can have confidence that the product is robust.

Other enhancements. In Log Explorer 3.03, you can restrict your recovery scripts to records from the log that have no subsequent changes. This ability means that you keep only the DDL statements that are most current. If you have Log Explorer 3.03's Enterprise Edition, the enterprise-level installation option lets you install the server-side component on several servers. The Professional Edition installs the server-side component on only one server.

Corrections and Bug Fixes

Log Explorer's designers have made many cosmetic fixes to the product, and they've fixed some bugs along the way. For example, Log Explorer 3.03 can now attach to databases' transaction logs by using the Bulk-Logged recovery model. This release also includes fixes to the Run Script dialog box. And Log Explorer now provides a server-side uninstall option in addition to the client-side uninstall facility. You can uninstall a server-side component from Control Panel's Add/Remove dialog box even if Log Explorer's client software isn't currently installed on the machine.

Lumigent continues to release interim bug fixes for Log Explorer. Most notably, Log Explorer 3.02 fixes a potential buffer overflow problem in the xp_logattach extended stored procedures. This fix makes the 3.02 release a crucial upgrade.

Remaining Problems

Log Explorer still has some problems. You can find a helpful list of reported problems in the Release Notes that come with the latest download of the product. Lumigent also includes these notes with the free trial version. I noticed a few minor problems that weren't listed in the Release Notes for Log Explorer 3.03. The Attach Log File dialog box defaults to attaching to the online database log, which could affect the performance of a busy production database. But Lev Vaitzblit, the head of Lumigent and the company's lead developer and designer, told me that Log Explorer affects SQL Server performance only slightly when reading an online transaction log and not at all when attaching to a backup file. Another problem is that the Attach Log File dialog box doesn't browse for SQL Servers on a network but looks for a computer name. So if you're using a named instance, you'll have to type in the name.

Also, as of Log Explorer's 3.03 release, Server Manager's online Help doesn't document the server component buttons Read Registry, Reload, and Unload. And if you unload the server component by using Server Manager, your attempt to connect Log Explorer to SQL Server will fail, but the error message won't cite unloading as a possible cause.

Finally, Log Explorer has a security problem. You can use the Log Explorer client utility to attach to a database log file if you have permissions to execute the xp_logattach stored procedure in the master database and if your login has access to that database. By default, installing Log Explorer doesn't grant you EXECUTE permissions on that stored procedure, which effectively means only a systems administrator (sa) can use xp_logattach. You can let other logins use Log Explorer on a server by granting EXECUTE permissions on the xp_logattach stored procedure. But those users will then be able to read the transaction log of any database on the instance to which they have database access. So a user who has EXECUTE permissions on the xp_logattach stored procedure and minimal access to a database can see database object activity that the user might not have permissions to see. When using Log Explorer, exercise careful control over permissions to the xp_logattach extended stored procedure.

A Necessary Tool

Log Explorer 3.03 is an important tool for SQL Server DBAs. It's the only available tool that lets you selectively recover data from a transaction log. When you need to recover data, you need to do it right away. And Log Explorer 3.03 gives you the ability to recover data quickly and completely.