ApexSQL Knowledgebase: ApexSQL Log Basics Part 2 of 3: Dropped or Deleted Data Recovery

LINKS

General

Apex SQL Audit
Apex SQL Clean
Apex SQL Code
Apex SQL Diff
Apex SQL Doc
Apex SQL Edit
Apex SQL Enforce
Apex SQL Log
Apex SQL Script

Knowledgebase Home

SITE FEED

Site Feed

Support Forum

The fastest and most effective to get answers to your questions quickly. Click Here for more info.

FAQs

Quick answers to common questions. Click Here for more info.

Troubleshooting

Known issues and basic troubleshooting techniques for problems or unexpected behaviors. Click Here for more info.

Knowledgebase

Technical tips, How-to articles, and other tutorials about ApexSQL Tools. Click Here for more info.

Online Documentation

All of our help files for download or convenient viewing online. Click Here for more info.

Videos

Watch product demos, training videos, or tutorials of our products' main features. We frequently upload new videos so check often for updates. Click Here for more info.

ApexSQL Knowledgebase

Tips and How-to Articles for Apex SQL Tools

ApexSQL Log Basics Part 2 of 3: Dropped or Deleted Data Recovery

DESCRIPTION
The following steps show how to recover deleted or dropped data. This type of recovery can be done using traditional transaction log sources (online, backup, and detached) or from online data files. The latter allows recovery even when database is not in FULL RECOVERY mode and no relevant transaction log data is available. Quality and completeness of data will depend on the post-event database activity.

SOLUTION

STEP 1: Open the "Recovery Wizard"

From the File menu, select "Recovery Wizard".

STEP 2: Choose a Data Source

Connect to a SQL Server database by selecting a server, providing login credentials, and choosing a database.

STEP 3: Select Recovery Option

Select any of the following Recovery options:

• Recover Data Loss from Delete Operations
• Recover Data Loss from Drop Table Operations
• Recover Lost Objects from Drop Operations

Click "Next".

STEP 4: Select availability of quality Data Source

Choose "Relevant data is available". Relevant transaction log data here means that you have a Detached Database file, a Transaction Log backup, or an online Transaction Log (if your database is set to Full Recovery Mode). If any of these is available, select Relevant data is available. If not, select Relevant data is not available—in which case, recovery will be done from the online data file. It is important to know that to recover BLOB data, the second option must be selected. BLOB data can only come from online data files.

You can also specify a Time Range. To do so, simply check From and/or To and enter your dates. This will limit the recovery to events that transpired between the selected dates.

STEP 5: Select Log Data Source(s)

From the list of logs, select the log file you want to recover. The online transaction log file of the current database is selected by default. You can also add a Transaction Log backup or a detached one. Click "Next".

STEP 6: Select Tables

Select the tables that you want to recover. Click "Next".

STEP 7: Select Recovery Script Path

On the Script File Name, specify where you want to save your generated recovery script. Click "Finish".

STEP 8: Execute generated recovery script

The result of a successful recovery operation is a saved script that, when run, will recover the database’s table structures and data. This script can be copied to the clipboard or to SQL Server's Query Analyzer. It can also be saved to a file or even executed directly to the database. Here, we choose to copy the script to Query Analyzer and run it there.

STEP 9: Verify successful execution of script

When the script execution completes, the results are displayed. You have successfully recovered your data.

SEE ALSO
ApexSQL Log Basics Part 1 of 3: Working with a SQL Server Transaction Log
ApexSQL Log Basics Part 3 of 3: Truncated Data Recovery

AUTHOR
Janice Lee

LAST REVIEW DATE
20 August 2007

Labels: ApexSQL Log

posted by Janice Lee on Tuesday, June 26, 2007

0 Comments: