Introduction:

SQL Log Rescue is Red Gate product. It is used in conjunction with SQL Server 2000 for analysis of the transaction logs of the database. By attaching itself to the transaction logs, it does not affect the performance of the database. Its used for protecting data and auditing the database.

With the help of SQL Log Rescue, one can

1) Recover the dropped tables,

2) Undo the wrong queries

3) Can know who is issuing the wrong commands

4) Know the users of the database

The SQL Log Rescues evaluation version can be obtained at

http://www.red-gate.com/products/SQL_Log_Rescue/index.htm.

The main aim of this document is to analyze the "SQL Log Rescue" tool and compare its performance with the Lumigent Log explorer. Log explorer is also a similar kind of tool. I have also presented a comparative study of these tools.

1. Installation Procedure

The installation steps are quite straightforward.

1) Click the SQLLogRescueSetup.exe.

It checks the necessary hardware and software requirements.

2) Click "Next" button.

3)

Accept the license agreement and click the "Next" button.

4) Click the "Next" button.

5) The user has a option to install the full software or

else only the server components or client components.

6) Provide the necessary authentication details and click

"Next" button.

7) Click the "Install" button.

2. Attaching SQL Log Rescue to the Database

1) Click "Next" to use the evaluation version.

2) Click "Next" button.

3) Click "Next" button.

4)

The backups taken for "Northwind" Database appear in the

box.

5) Click the "Next" button. The "SQLLogRescue" analyzes the

transaction logs.The backups can be in "SQL Backup"(Tool provided by Red Gate

for efficient backups) format or the Microsoft tape formats.

6)

Click on "Save Recovery Project" to save the project on the

hard disk.

After saving the project, click the "Finish" button.

The "SQL LogRescue" is attached to the live log of the

Northwind database.

3. Menus in SQL Log Rescue

3.1 File Menu

The Current project settings can be edited by clicking File Project Settings

Click the "Next" button.

The user can open a new recovery project, edit the current

project or open an existing project through the "Project Settings".

3.2 View Menu

3.2.1 Row Data Window

Select a transaction and click View Row Data Window.

It shows the value of each of the columns in that row.

3.2.2 Row History Window

Click on any update transaction. It will show the previous

values contained by the row.

3.2.3 Undo Script Window

Select an update transaction. Click View Undo Script window.

The View Redo

Script window does exactly the reverse.It performs the same operation again.

3.2.4 Grouping the transactions

The transactions can be grouped according to the type, time,

transaction id, table and the user. This can be done by navigating to View Group by in the View menu.

The log entries are grouped according to the transaction ID.

Click on any transaction. The transaction details can be

seen by clicking the "+" sign.

Grouping can also be done by one more way.

Drag any column header to the grayed area.

3.3 Tools Menu

3.3.1 Install and Uninstall extended stored procedure

When the "SQL Log Rescue" tool is being installed, an

extended procedure "xp_LogRescue" installed in the master database. The

user has an option to install the extended procedure later after the tool installation.

This can be done by navigating to Tools

Install Extended Stored Procedure

The user can also uninstall the extended procedure by

navigating to Tools Uninstall

extended stored procedure.

3.3.2 Undo and Redo Operations

Select any transaction. Click the box provided for that

transaction. The "Undo operation" and "Redo operation" in the tool menu gets

enabled.

Click "Undo operations". The "SQL Log Rescue" generates the

script. It displays the warnings and the scripts.

The user can save the script and run it later in Query

analyzer or click the "Next" button.

Click the "Finish" button.

The user can neither run the script from SQL Log Rescue nor

launch the script in query analyzer in the evaluation version.

3.3.2.1 Recovering dropped tables

Suppose if a table gets dropped by mistake. The user does

not have backup.SQL Log Rescue comes to help in this case.

Refresh the log in the SQL Log Rescue by pressing "F5".

Click "Undo Operations".

The undo script is generated.

The dropped table and the data can be recovered.

4. Features of SQL Log Rescue

1. User can easily analyze the transaction logs

2. User can filter the transaction logs according to the

type of operation, table, user and time.

For example,

The user gets to see the logs related to only "Truncate

table".

3. The user can search for a particular word(s)

Type the search criteria in the text box next to the

"Search" button. Click the "Search" button.

4. Grouping the transaction according to transaction ID,

table, type of operation performed on the database. Grouping is explained in

the section 3.2.4

5. Generating Undo and Redo scripts easily. See section 3.2.3

6. Recovering the dropped tables and the data. See Section 3.3.2

7.Viewing the row history and the row data.

5. Issues I faced during the transaction Log analysis

1. Timeout error.

The SQL Log Rescue examines the backup files and transaction

logs of the database the user wants to attach the tool to. It hangs in the

"Analyzing Transaction Log" state for a long time and then gives out the

Timeout error message.When I checked in the Redgate forum,it was mentioned that

the SQL Log Rescue was not able to find the transaction log files.If the query

analyzer successfully returns the result of this query,then the SQL Log Rescue has

some issue in finding the transaction log.

"select top 1 filename from <name of database>..Sysfiles1 where

((status & 0x40) !=0)"

I tried running this query.It returned the path of the

transaction log file of the Northwind database.The SQL Log Rescue cannot find

the transaction log if the returned path by the query is a relative one.I tried

restarting it again and again and got it right in one of the attempts.One of

the solution suggested by the Redgate forum is install the extended procedure

manually.

In the help file which is accompanied with SQL Log Rescue,

the reasons for this error is that there must be some transactions in the

database which must not be complete. So the user has to wait for its

completion.I was using the Northwind database after a long time.So there wasnt

any chance for hanging transactions.

2. SQL Log rescue cannot access the live log.

No proper solutions mentioned in the red gate forum and the

Help file.

3. SQL Log Rescue did not show the current transactions

performed on the database. I tried refreshing it again and again, but it could

not attach to the live log of the database. I restarted the tool again and

tried attaching it to the Northwind database. This time it was showing the

current transactions.

The tool exhibits an inconsistent behavior.

Comparison with Lumigent Log explorer

I have discussed about the Log explorer in detail in my

article "A look

at Log explorer".

Conclusion:

SQL Log Rescue on a whole,is a great tool for data recovery

management. It will prove very handy for SQL Server DBAs.