Why doesn’t ApexSQL Recover recover indexes and primary keys during lost data recovery

Applies to
ApexSQL Recover

Summary
When recovering lost data into a new database, ApexSQL Recover does not generate the primary key and indexes on the created tables. The same applies to the tables created when recovering data from a drop table operation.

Description
When ApexSQL Recover creates a new database where recovered records are stored, the generated tables have no primary keys or indexes.

If the original table structure was:

CREATE TABLE Employees

(ID int primary key,
 FirstName varchar (20),
 MiddleName char(1),
 LastName varchar(30),
 SecNum int)

CREATE NONCLUSTERED INDEX [IndexSecNum] ON [dbo].[Employees](	[SecNum] ASC)

The recovered table structure is

CREATE TABLE [dbo].[Employees] /* ID = 629577281 */
([ID] int NOT NULL,
 [FirstName] varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [MiddleName] char COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [SecNum] int NULL,
 [LastName] varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

Let’s say the lost table records are:

Now, imagine there was a record in the same table with ID = 1 earlier. You deleted it, and inserted a new record with ID = 1 instead. Then, you deleted the record again. In case ApexSQL Recover finds both of these records, it will not know whether you want to recover the first, or the second one. It will create a recovery script for all lost records found.

In this case, there will be two INSERT statements with the same ID:

INSERT INTO [dbo].[Employees] ([ID], [FirstName], [MiddleName], [SecNum], [LastName]) VALUES (1, N'Anne' COLLATE SQL_Latin1_General_CP1_CI_AS, N'M' COLLATE SQL_Latin1_General_CP1_CI_AS, 311297, N'Baker' COLLATE SQL_Latin1_General_CP1_CI_AS)

INSERT INTO [dbo].[Employees] ([ID], [FirstName], [MiddleName], [SecNum], [LastName]) VALUES (1, N'John' COLLATE SQL_Latin1_General_CP1_CI_AS, N'A' COLLATE SQL_Latin1_General_CP1_CI_AS, 2805972, N'Taylor' COLLATE SQL_Latin1_General_CP1_CI_AS)

If you created a table with a primary key, the other insert will fail with the “Violation of PRIMARY KEY constraint … Cannot insert duplicate key in object ‘dbo.Employees’” error and only one record will be recovered.

To ensure that all lost records are recovered and enable maximal data recovery, ApexSQL Recover creates no primary keys and indexes when it re-creates tables. The advantage of this approach is that all records found in the MDF file are scripted and inserted into an adequate table. The disadvantage is that you have to remove the duplicates manually and manually update to the original table structure afterwards.

Last update
July 30, 2014