Worked example: Getting Started

See Also

This worked example provides a basic overview of how to use SQL Log Rescue.

In the example, the Magic Widget Company has a SQL Server 2000 database running on a live web server. This database contains tables that have been modified in error, and three records that have been deleted must be restored.

You will see how to:

1. Set up the databases if you want to follow the example on your own system.

   You will need access to a SQL Server 2000 to do this.

2. Create a project to analyze the database in SQL Log Rescue.
3. View the log entries for the databases.
4. View details of individual log entries, such as the operation details and the row history.
5. Undo operations to restore the deleted records.

Setting up the databases

If you want to follow the example using your own system, you must create the Widgets database on your server. If this database exists already on your server, delete it. Then, click here to open the SQL creation file for the databases, and run the file in Microsoft® SQL Server™ 2000 Query Analyzer.

The script creates the Widgets database, backs it up, inserts some data, and modifies the data.

Creating the project

1. If you have not yet started SQL Log Rescue, select it from your Start menu; if SQL Log Rescue is already running, click Project Settings 
2. On the Recovery Project Action page of the Project Settings wizard, click Create a new recovery project, and then click Next.

   

3. On the Select Database page, in the Server list click your server, and in the Database list click the Widgets database.

   

4. Click Next.

   SQL Log Rescue automatically retrieves the backup for the Widgets database and displays it in the Choose Database Backups page.

   

   The Choose Database Backups page enables you to change the backups that SQL Log Rescue will include in the analysis.

5. Click Next to use the retrieved backup file.

   If you have not installed the SQL Log Rescue extended stored procedure on the server, you are prompted to install it. Click Yes on the prompt box to install the extended stored procedure.

   SQL Log Rescue displays the Backup File Warnings page.

   

   Any problems with the included backups are displayed here. For example, if you had omitted to include a full database backup, a warning would be displayed. For this example, there are no warnings.

6. Click Finish.

   SQL Log Rescue analyzes the database, and the log entries from the live transaction log are displayed in the main window.

   

7. To save the project, click Save , then on the Save Recovery Project dialog box enter a name for the project (for example Widgets), and click Save.

   SQL Log Rescue projects are saved with the suffix .slr

Viewing the log entries

The log entries table (top pane) displays the list of log entries.

For ease of viewing, you can group the log entries by a particular column. In this case, we will use the Transaction ID column: drag the column header to the area above the table. You can then expand and collapse the individual groups as required.

You can also sort, group, and filter the columns, as you will see later in this example. For full details of how to use the log entries view see Viewing log entries.

Viewing log entry details

You can use the tabs at the bottom of the main window to view the log entries in more detail. If you cannot see the tabs, on the View menu, click Dock All Windows to display the default view.

1. In the Table column, click the down-arrow and click [dbo].[WidgetDescriptions] to filter on this table.

   SQL Log Rescue displays only the log entries for the WidgetDescriptions table.

2. Expand the transaction groups, and click the Update log entry.

   

   The Row Data tab shows details of the operation.

   

   The updated values are displayed in blue text. A browse button is displayed for the Description and Picture values. This is because they are large object data types. Click the buttons to see details of the large objects.

3. To clear the Table filter, in the Table column header click the down-arrow and click (All).

If the appropriate backups have been included, you can also view the full history of a row.

1. In the text box above the log entries table, type DW, then click Search.

   SQL Log Rescue searches the table names and row details and displays the log entries that contain the string.

   

2. Click one of the log entries, then click the Row History tab.

   The Row History tab shows details of the operations that have occurred on that row.

   

3. Click Clear Results to return to the full list of log entries.

You can view the SQL code for undoing and redoing operations.

1. Click the Type column to sort the rows by operation type, then click again to change the order to descending.

   The Type column is shaded to show that it is the sort column, and to indicate the order.

2. Click the Drop table operation, which is now displayed near the top of the list.

   

3. Click the Undo Script tab.

   SQL Log Rescue displays the SQL code to undo the table drop.

   Note that for large table reconstruction, SQL Log Rescue shows only a section of the Undo script in this pane.

For more information see Viewing Log Entry Details.

Undoing operations

To undo operations, you first select the log entries you want to undo by clicking the appropriate check boxes in the left-hand column of the log entries table. For this worked example, you want to undo all the deletions.

1. In the Type column, click the down-arrow and on the filter menu, click Delete.
2. Click Select All  to select all of the displayed log entries.

   

When you have selected the log entries, click Undo Operations . The Undo Operations wizard displays a summary of the operations that the SQL script will perform when you run it.

A warnings tab is displayed for you to review. For this example, three standard warnings are displayed to inform you that triggers, foreign keys, and cascade deletes are not disabled.

You can ignore the warnings for this example.

To see the SQL script to undo the log entries, click the SQL Script tab.

When you have reviewed the script, click Next. The Data Recovery page is displayed.

You can now choose either to run the SQL script from within SQL Log Rescue, or to launch SQL Query Analyzer so that you can edit the script. In this example, we will choose to run the script (the default).

Click Finish to start the undo operation.

The script is run, and the log entries table is updated. Clear the filter on the Type column by clicking the down-arrow and clicking All. Operations performed by SQL Log Rescue are shaded, and is displayed next to the entries to indicate that they are part of a transaction performed by SQL Log Rescue.

You have successfully recovered the deleted records.

 

 

 

---

© Red Gate Software Ltd 2005. All Rights Reserved.