Recovery features comparison: ApexSQL Log vs. ApexSQL Recover

Applies to
ApexSQL Log, ApexSQL Recover

ApexSQL Log and ApexSQL Recover are two tools by ApexSQL that can be used to recover deleted records and dropped tables.

Although ApexSQL Log does recovery of data from delete or drop table operations into SQL script format, it is also an extremely useful tool in cases where post incident auditing is required.

ApexSQL Recover on the other hand, can extract blobs into files and perform recovery either by creating a T-SQL script to re-create the objects and insert the records, or by creating a new database (containing both data and objects recovered).

Recovering data lost due to a DELETE operation

This recovery option is available both in ApexSQL Log and ApexSQL Recover.

Although the output is the same, mechanisms used to recover the records are different, so if by chance ApexSQL Log does not recover the deleted records, we recommend trying the ApexSQL Recover and vice versa.

ApexSQL Recover
ApexSQL Recover reads the deleted transactions from the database MDF file. When a record is deleted, it’s not immediately removed from the MDF file. Rather, it’s marked to indicate that the space where the record was stored is now available, and can store new records. If new records haven’t been written into this space yet, ApexSQL Recover can read the original record and recover it.
The output T-SQL recovery script in ApexSQL Recover inserts the records into the table.

INSERT INTO dbo.Appointments(AppID, 
                             PatientID, 
                             AppDate, 
                             AppTime, 
                             Diagnosis, 
                             Treatment, 
                             Comments, 
                             AppStatus
                            )
VALUES(1, 
       1, 
       '20110101', 
       '12:12:00', 
       N'2' COLLATE SQL_Latin1_General_CP1_CI_AS, 
       N'2' COLLATE SQL_Latin1_General_CP1_CI_AS, 
       N'2' COLLATE SQL_Latin1_General_CP1_CI_AS, 
       NULL
      );

If you select to create a new table instead of a T-SQL script, ApexSQL Recover will create a new database, table(s) and insert deleted records into them.

ApexSQL Log
ApexSQL Log reads the transaction logs (online, detached and transaction log backups), searches for DELETE transactions and creates UNDO scripts that insert deleted records into the table. If the database is in the Simple recovery model, the chances for a successful recovery are decreased, as the transactions you aim to recover might already have been truncated.

To recover deleted records using ApexSQL Log, select the Deleted row option in the Filter |Operations | Data operations (DML) list, and create a T-SQL script via the Create undo script option. The script generated uses the same syntax as that generated by ApexSQL Recover.

Recovering data due to a TRUNCATE TABLE operation

The TRUNCATE TABLE operation removes all rows from a table. The effect is the same as if the DELETE statement with no WHERE clause was executed.

TRUNCATE TABLE <table_name>

ApexSQL Recover
If a table has been truncated, use the Recover lost data due to a TRUNCATE operation option in ApexSQL Recover. If you opt for a recovery script as the output, ApexSQL Recover will create an INSERT INTO script, as in the example shown below:

INSERT INTO dbo.Appointments(AppID, 
                             PatientID, 
                             AppDate, 
                             AppTime, 
                             Diagnosis, 
                             Treatment, 
                             Comments, 
                             AppStatus
                            )
VALUES(1, 
       1, 
       '20120101', 
       '12:00:00', 
       N'1' COLLATE SQL_Latin1_General_CP1_CI_AS, 
       N'4' COLLATE SQL_Latin1_General_CP1_CI_AS, 
       N'5' COLLATE SQL_Latin1_General_CP1_CI_AS, 
       NULL
      );

If you select the option to create a new database, ApexSQL Recover will create a database and truncated table(s) with truncated records inserted.

ApexSQL Log doesn’t recover from TRUNCATE operations at all.

Recovering data due to a DROP TABLE operation
The DROP TABLE operation removes table definition, data, indexes, triggers, constraints, and related permission specifications.

DROP TABLE <table_name>

ApexSQL Recover
The ‘Recover data due to a DROP TABLE’ operation ApexSQL Recover option in re-creates the table and inserts all lost records.

If you opt for a recovery script as the output, ApexSQL Recover will create a T-SQL script which creates the dropped table first, and inserts the records next.

CREATE TABLE dbo.Visits 

/* ID = 754101727 */

(VisitID int NOT NULL, 
 PatientID int NULL, 
 Visitdate date NULL, 
 VisitTime time(7)NULL, 
 Diagnosis nvarchar(200)COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
 treatment nvarchar(500)COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
 Comments nvarchar(500)COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
 VisitStatus bit NULL, 
 Recommendation nvarchar(max)COLLATE SQL_Latin1_General_CP1_CI_AS NULL
);
--	RECOVERED ROWS FROM [dbo].[Visits], ID = 754101727
INSERT INTO dbo.Visits(VisitID, 
                       PatientID, 
                       Visitdate, 
                       VisitTime, 
                       Diagnosis, 
                       treatment, 
                       Comments, 
                       VisitStatus, 
                       Recommendation
                      )
VALUES(1, 
       1, 
       '20130105', 
       '19:00:00', 
       N'Diabetes acc' COLLATE SQL_Latin1_General_CP1_CI_AS, 
       N'insuline' COLLATE SQL_Latin1_General_CP1_CI_AS, 
       N'general state well' COLLATE SQL_Latin1_General_CP1_CI_AS, 
       0, 
       N'A healthy diet and lifestyle'
      );

If you select a new database instead of a T-SQL script output, ApexSQL Recover will create a new database, table(s), and insert lost records into them.

ApexSQL Log doesn’t recover data lost due to DROP TABLE operation.

Recovering a table structure due to a DROP table operation

Both ApexSQL Log and ApexSQL Recover can recover dropped table structures.

ApexSQL Recover
Unlike the Recover data due to a DROP TABLE operation option (recovers both the table structure and data), the Recover table due to a DROP table operation option re-creates only the lost table structure (without any records). ApexSQL Recover reads the drop table transactions from the database data file (MDF), transaction logs (online and detached), and their transaction log backups.

The output T-SQL recovery script in ApexSQL Recover re-creates the table structure.

CREATE TABLE dbo.Visits 

/* ID = 754101727 */

(VisitID int NOT NULL, 
 PatientID int NULL, 
 Visitdate date NULL, 
 VisitTime time(7)NULL, 
 Diagnosis nvarchar(200)COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
 treatment nvarchar(500)COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
 Comments nvarchar(500)COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
 VisitStatus bit NULL, 
 Recommendation nvarchar(max)COLLATE SQL_Latin1_General_CP1_CI_AS NULL
);

When a new database is created as the output by ApexSQL Recover, only tables are created (records are not inserted).

ApexSQL Log
ApexSQL Log reads only the transaction log files (online, detached and transaction log backups). Unlike ApexSQL Recover, ApexSQL Log doesn’t read the MDF file, so if the recovery is not successful (dropped table info missing from the log files), use ApexSQL Recover, which may contain the table structure in the MDF file.

To recover dropped table structures using ApexSQL Log, select the Drop table option in the Filter |Operations | Schema operations (DDL) list, and then create a T-SQL script via the Create undo script option. The generated script will be the same as the one generated by ApexSQL Recover.

Recovering deleted BLOB data

ApexSQL Recover
When recovering varchar(max), ntext, nvarchar(max), and other BLOB data, use the Recover lost BLOBs option. ApexSQL Recover reads the MDF file, detects BLOBs marked for deletion, and re-creates them as individual files for each lost BLOB record.

ApexSQL Log
ApexSQL Log can also recover lost BLOB data, but only if the loss is due to a DELETE operation, and if the original INSERT operation exists in the online, detached or transaction log backups. This is rarely the case for databases in the Simple recovery model, and may be cumbersome for databases in the Full recovery model. If ApexSQL Log doesn’t recover BLOBs, we recommend ApexSQL Recover, as it reads more data sources and as such is more likely to recover your BLOBs.

As described, when it comes to lost data recovery, both ApexSQL Log and ApexSQL Recover can be used. In some scenarios, when the mechanisms used to recover the records are different, if one of the tools cannot bring the data back, it’s recommended to try the other one.

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.