SQL Server Magazine

ApexSQL's ApexSQL Log (ASL) is a database-recovery and log-auditing utility for SQL Server 2005, 2000 (including 64-bit versions), and 7.0. Making use of SQL log files, MDF data files, and (with SQL Server 2005 and 2000) user-oriented data related to the authenticating user ID ASL collects with its connection monitor, ASL can undo multiple- and single-statement database updates, reconstruct dropped database objects, and recover data from truncated tables and damaged MDF files that you can't reconnect to SQL Server. ASL is licensed by SQL Server instance and is composed of several components. You can install the two client components, a GUI and a command-line interface, wherever convenient. On the SQL Server system, ASL is implemented as two sets of extended stored procedures. One set—along with an accompanying database table—implements a tool that collects user-related information. In my tests, installation was uneventful. I installed the full product on a SQL Server 2005 system, then installed the client-side GUI and command-line interface on a Windows XP system. After using the vendor-supplied key to activate ASL, I verified that the database chosen for testing was operating in the full recovery mode necessary for SQL Server to maintain the requisite transaction logs. After taking a full backup, I tested log-audit operation and recovery from simple delete and update transactions. I found that the ASL GUI has two primary modes of operation: recovery wizard mode and server explorer mode. In either mode, ASL lets you connect its GUI console to one or more online, detached, and backup log and database files for its use.

I started by using Server Explorer to connect to my test database and its active transaction log. Defining a filter used to select the data you want to recover is the next step. ASL lets you filter activity from transaction logs in a variety of ways. In addition to basic date/time and table filters, you can select log records for display based on other criteria, including the type of operation (e.g., insert, update, delete), transaction status (e.g., committed, aborted), table field values, transaction description strings, and Server Process ID (SPID). When running the connection monitor on SQL Server 2005 or 2000 systems, you can also filter on the authenticating user ID and the originating client host name. I filtered only for the two tables I had changed, and the server explorer window displayed the delete and update transactions. A right-click menu let me create an Undo script, which appeared in an editable window. I executed it and verified that the updates I had applied had now been reversed. In a similar manner, I tested the recovery wizard to restore deleted rows. I encountered no surprises. In another test, I deleted 261 rows from a table, changed to the simple recovery model, did a full backup of the database, and deleted six more rows. Using the recovery wizard, I ran a recovery for deleted records. Because transaction log data isn't available with the simple recovery mode, ASL was forced to look for deleted records within active database data pages. ASL was able to create a script for recovering 198 of the deleted records, including five of the six most recently deleted records. When you're recovering deleted records from a simple recovery model database, filtering options aren't available, so it would be wise to examine the script for unwanted record insertions. From an auditing perspective, the tools are pretty weak. You can view filtered results and group them in single columns. A find function lets you search within filtered results, but is the tool offers no "find and select all" when you want to search record data. ApexSQL Log is extremely easy to use. Its ability to examine online, detached, and backup database and transaction log files gives you an excellent chance to recover from errant data modifications. The log-filtering options are flexible and straightforward, whether you're simply auditing a transaction log or seeking to restore data. The ability to extract deleted records from simple model database data pages that haven't been overwritten can also be a useful last-resort option. ApexSQL Log is a good utility to have in your DBA toolkit.

End of Article