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

A Look at SQL Log Rescue

By S. Srivathsani,

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

Features

SQL Log Rescue

 

Lumigent Log Explorer

 

 

Backups

SQL Log Rescue requires a full log backup and contiguous transaction log backups for accurate analysis and retrieval of data

In Log explorer, the user has to just attach it to the database. The backups files are not needed

Mailing Functionality

No

In Log explorer, the user can configure mails for some set of events

Indexing the Log records

No

Yes. This makes the browsing of log faster.

Undo/Redo transactions

Yes

Yes

Recover data and Dropped tables

Yes

Yes

Exporting Log Records

No

Yes

Row Revision History

Yes

Yes

Viewing BLOB/CLOB/NTEXT data

Yes

Yes

Viewing "Create" commands

No

Yes

Load Analysis according to the table,user and throughput

No

Yes

Searching for a particular word(s) in the log

Yes

No

Pricing

195$ for single license

$1680 for single license

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.

Total article views: 3885 | Views in the last 30 days: 4
 
Related Articles
FORUM

Mailing reports in button click

Sending mail on button click of web page

FORUM

Printing Report by clicking an ASP.NET button.

Printing Report by clicking an ASP.NET button programatically.

FORUM

Need One Click Button to Run All Reports

One Click Button to run all reports

ARTICLE

SQL Rescue Review

Having a good tool to read your SQL Server transaction log can be a lifesaver, or a job saver. RegGa...

FORUM

Error when I click on "Start Mirroring" button.

Error when I click on "Start Mirroring" button.

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