Applies to
ApexSQL Recover
Summary
This article explains how to use the “From TRUNCATE” operation option in ApexSQL Recover, in order to recover data lost due to a malicious or accidental TRUNCATE statement.
Description
In order to maximize chances for recovery, the time period between the incident and the recovery attempt needs to be minimized. A TRUNCATE TABLE operation deallocates (marks as available for use/overwrite) the data pages with the existing data, but leaves the actual row data intact. ApexSQL Recover scrapes these deallocated pages for lost data and recovers it from them. However, deallocated pages can be reused by SQL Server at any time, and as the time passes, the chances that SQL Server will overwrite these data pages with new data increase, decreasing the chances for successful recovery thereby. Performing a full database backup after the incident doesn’t ensure a successful data recovery.
In case of lost data due to a TRUNCATE TABLE operation:
- It is highly advisable to copy the database data and transaction log files to a safe location. Note, however, that the database must be offline. You can bring the database back online afterwards
- Attach copies of the database and transaction log files as a new database
-
Proceed with the recovery process on the new database
-
Provide basic database connection parameters (the SQL Server instance name, authentication type, and the name of the database containing the table that was truncated). ApexSQL Recover provides a list of available tables in the selected database
-
Select tables you want restored. To narrow down the list and find the required tables quickly, use the filter row at the top of the list. A word of caution: in case that selected tables depend on deselected ones (e.g. the table being recovered is dependent via a foreign key on a deselected table), the recovery process will not be successful:
-
Specify a recovery action; you can choose to save a recovery SQL script or to recover the truncated table and its data directly into a new SQL Server database
Last updated:
8/9/2018
