Red Gate logo


Once you've installed SQL Log Rescue, you will be able to explore your logs to see what's happened to your database and when. You can also recover data that has been accidentally deleted, inserted, or modified.

This walkthrough will show you:

  • How to set up a new project
  • How to view transactions
  • How to identify a problem
  • How to recover data

Step 1: Executing a dangerous query on our database

We'll start this walkthrough by viewing a database and executing a damaging transaction on it.

By re-running the original query, we can see that our data has been permanently deleted.

Server:Msg 208, Level16, State 1, Line 1
							Invalid object name 'socceradmin.players'.

We now want to go ahead and rescue our data.

Executing a dangerous query on our database

Step 2: Setting up a new project

To rescue this data, we will start a new recovery project in SQL Log Rescue.

We could, however, have chosen to open or edit an existing project.

Setting up a new project

Step 3: Choosing the database

We now need to choose the database we would like to investigate.

SQL Log Rescue can access databases on any server you have permission to access. In this case, we're going to look at the database WidgetSoccer.

Choosing the database

Step 4: Finding the backup files and log files

SQL Log Rescue then automatically finds the backup files and log files needed to investigate our database.

Finding the backup files and log files

Step 5: Warnings about backup and log files

SQL Log Rescue will then supply any warnings about the backup and log files.

In this case, SQL Log Rescue found no problems, so it will begin its detailed analysis of live log and backup files when you press Finish.

Warnings about backup and log files

Step 6: Viewing transactions

Next, the main transaction viewing window is displayed.

The top half of the screen is a grid for grouping and sorting the operations, and the bottom half shows the details of these transactions.

The grid in the top half of the screen helps you understand what has happened in the potentially huge number of transactions occurring on your database.

Viewing transactions

Step 7: Search, sort, filter

The box at the top left of the screen can be used to search, and you can sort and filter using the tabs at the top of each column. By dragging and dropping a column header, you can group by that column. Using these facilities, we can clearly identify what has happened.

In this case, by grouping by transaction type, we can see a table drop, clearly the cause of our problem. In the absence of SQL Log Rescue, this would be very serious.

Now that we've identified our problem transaction, we can move on to rescuing this data by undoing the table drop. We select this transaction only, and run our Undo Operations wizard

Search, sort, filter

Step 8: Review a summary of the SQL script

We are first presented with a summary of what the script will do.

Review a summary of the SQL Script

Step 9: Viewing warnings

By clicking the Warnings tab, we can see that SQL Log Rescue has identified a number of warnings, has ranked them in order of importance, and has suggested what to do in each case to minimize risk.

Viewing warningst

Step 10: Viewing SQL script

By clicking the SQL Script tab, we can inspect the properly formatted, transaction-based script, which SQL Log Rescue instantly produces.

In this case, with a dropped table, SQL Log Rescue has gone back to the latest backup, and has worked out what was deleted and what transactions have been made since that backup, in order to recreate the table exactly as it was.

Viewing SQL script

Step 11: Run script or launch Query Analyzer

Next, we choose whether to run the script directly from SQL Log Rescue or to edit it in SQL Query Analyzer.

We choose to run the script now.

Run script or launch Query Analyzer

Step 12: Viewing updated transaction log

We immediately see that the transaction log has been updated.

Viewing updated transaction log

Step 13: Viewing recovered data

By going back to Query Analyzer, we will be able to check that our dropped table has been recovered.

We can see that the data has returned intact and that it is exactly as it should be.

Having the power to do this on a damaged or otherwise corrupted database provides an extremely valuable resource to any company.

Viewing recovered data

And that's it! If you would like to see how SQL Log Rescue could help you, try it now. It's a free download.