Recover deleted records in Dynamics CRM 2016

Even though Dynamics CRM 2016 is very on clear on the fact that that record deletion is permanent and even provides a warning, it’s not unusual one or more CRM records to be deleted by accident. The bad news is that once the records have been deleted, there is no way to undo the deletion as removing a record from CRM actually translates to deleting it from the underlying SQL database.

That is one of the biggest differences between Dynamics CRM 4 and Dynamics CRM 2016. Unlike Dynamics CRM 2016, Dynamics CRM 4 uses the “soft delete” approach. This means that a deleted record in Dynamics CRM 4 could be recovered relatively effortlessly – to recover a deleted record from Dynamics CRM 4 all you have to do is to connect to the underlying database, locate the deleted record and simply update the value in that record’s IsDeleted column to false. So, how can one go about recovering deleted records from a Dynamics CRM 2016 database?

One of the things you can do at this point is to use the Dynamic CRM 2016 SDK to see if there is any auditing data remaining on the record that was deleted and try to recover the record that way. Unfortunately, this will only work if CRM auditing was enabled for deleted record and all records related to the deleted record. If you’re able to find exact audit record for the delete operation you want to recover (let’s assume the ID of the deletion audit record is AACFF208-277B-E011-BB2B-001F29E1FC88), you can use the following code:

auditId = new Guid("AACFF208-277B-E011-BB2B-001F29E1FC88");  
              
RetrieveAuditDetailsRequest request = new RetrieveAuditDetailsRequest();
request.AuditId = auditId;
                           
RetrieveAuditDetailsResponse response = 
    (RetrieveAuditDetailsResponse) _service.Execute(request);
_service.Create(response.AuditDetail.OldValue);

However, if not all of the released records have been audited, or even if you haven’t updated your Dynamics CRM 2016 with the latest rollup this approach may fail.

Another path you can take is to restore a database backup and overwrite the underlying SQL database with it. Although this might work if no changes have occurred on the Dynamics database after the record has been deleted. However, if the database has been changed (which is likely to be the case) you will lose all of the changes made after the deletion – not to mention that the entire system will be offline for the duration of the backup restore. So, is there any way to ensure that the record will be restored?

Since Dynamics CRM 2016 leverages SQL Server to store and manage the data, each change made to the Dynamics CRM system actually leaves a trail in the transaction log of the database. This effectively means that if we could manage to read the transaction log, we could detect the delete operation on a transaction level and attempt to restore the data from there. The bad news is that the transaction log isn’t intended to be humanly readable, and therefore attempting to retrieve such a record manually form the transaction log can be a very complex if not a border-line impossible operation. The good news is – ApexSQL Log is designed to do just that.

ApexSQL Log is an auditing and recovery tool for SQL Server databases which reads transaction logs, transaction log backups, detached transaction logs and database backups, and audits, reverts or replays data and object changes that have affected the database, including the ones that have occurred before the product was installed.

For instance, let’s assume that an account that had 4 contacts has been accidentally deleted. In order to recover it, you would have to recover the account first and then recover all of the related records – in this case the 4 contacts. To perform the recovery using ApexSQL Log:

1. Start ApexSQL Log
2. Connect to the Dynamics CRM 2016 database

3. In next step Add a database backup

   

4. Click the “Filter” tab and navigate to the “Tables” tab
5. Make sure that only the following tables are selected:
   • AccountBase
   • AccountExtensionBase
   • ContactBase

   • ContactExtensionBase

     

6. Click “Open results in grid”
7. In the “Main grid” navigate to the DELETE operations you want to undo and select them

8. Select “Create undo script” from the Main ribbon

   

9. Execute the generated SQL script against the Dynamics CRM database

Unlike Dynamics CRM 4, recovering a deleted record from a Dynamics CRM 2016 database may seem like a tall order – luckily, ApexSQL Log might just be the tool to get the job done.