How to optimize data recovery results in SQL Server using ApexSQL Recover (4214842)

Summary This article describes actions that improve chances for successful recovery of lost SQL Server data using ApexSQL Recover.

Post-incident activities To maximize the chance of recovery success, it is highly recommended to do the following immediately after the incident:

1. Detach the database
2. Create copies of database MDF and LDF files
3. Attach the copies of the MDF and LDF files on another server
4. Start ApexSQL Recover and select an appropriate data recovery option

The described steps ensure that post-incident activities do not overwrite data information you need to recover.

SQL Server data recovery options in ApexSQL Recover ApexSQL Recover provides four data recovery options:

1. The From DELETE operation option
2. The From TRUNCATE operation option
3. The From DROP TABLE operation option
4. The Deleted BLOBs option

The 2^nd, and 4^th option rely on reading a database MDF file and recovering data from it.

These options do not use database LDF (transaction log) files because required recovery information cannot be found in them (e.g. the TRUNCATE operation is not logged in the database transaction log by design).

The DELETE and DROP TABLE operations Unlike the previously described recovery options, the From DELETE operation and From DROP TABLE operation recovery options use the database transaction log (online, detached, or backup) to reconstruct the lost data. Both options provide a wizard-like recovery which enables you to select transaction logs as additional data sources and successfully recover lost data.

In case of DELETE and DROP TABLE operations, ApexSQL Recover requires a non-truncated transaction log (online, detached, or backup) containing the operation that caused data loss. In case the transaction log is not available, ApexSQL Recover will read the database MDF file and try to recover data from it.

Note that recovering lost data from an MDF file, without appropriate additional sources can be successful, but the results are not guaranteed, as the actual data may be overwritten by another post-incident activity.

To improve and maximize the chance of recovery success, a non-truncated transaction log (online, detached, or backup) containing the incident operation must be included in the recovery process. To do that, after the appropriate recovery option (From DELETE operation or From DROP TABLE operation recovery) is selected:

1. Select the Add transaction logs option

2. In the Specify additional data sources for the recovery dialog use the Add option to browse for backup files (.bak and .trn) or detached transaction log files (.ldf)

   

3. After the additional files have been added to the dialog, select the data sources to be analyzed in order to recover the lost data. For optimal results, you should provide the whole chain of transaction log backups since a full database backup exists. Or a full database backup, then differential database backups and then the transaction log backup chain from there up to the point in time when the incident occurred

   

4. Click Next to continue with the recovery process wizard

Last Updated: 7/27/2018