Customer Recovery

If you have had an unfortunate incident and deleted all customers the recovery is not as simple as restoring from a backup, if you have had orders or customer creation since your last backup.

Before we start

Check if you have had any orders since your last backup.

If you haven't then a full import of the backup will fix any issues.

Starting from backup

First backup your current database, we will need the order data later.

Now get your backup of the database and import this into a fresh database, for now we will call this mage_restore.

Next import the dump you took from your current database into another new database. We do this to ensure data integrity. Call this something like full_restore

Pulling customers from backup

From the database we created earlier, mage_restore, we need to extract the customer data.

This can be done by creating a new sql dump of only the relevant tables.

For our purposes we will use this query:

mysqldump -t --skip-extended-insert mage_restore customer_address_entity customer_address_entity_datetime customer_address_entity_decimal customer_address_entity_int customer_address_entity_text customer_address_entity_varchar customer_entity customer_entity_datetime customer_entity_decimal customer_entity_int customer_entity_text customer_entity_varchar wishlist wishlist_item > customers_restore.sql

Add --insert-ignore if you had customer registration after deleting the customers

This will create a new sql file customers_restore.sql which will hold all customer data.

Import customers into backup

Now we can import the customers back into our backup copy.

This can be done with:

mysql full_restore < customers_restore.sql

Our full_restore database will now hold all customer data and all orders.

However, if you will still have orphaned orders as a result of deleting the customer data, and we will fix that in the next step.

Matching Orders to Customers

Because of the customer deletion we will now have orphaned orders, we need to recreate the association between customers and their orders.

This query should correctly associate all orders back to customers:

UPDATE full_restore.sales_order AS orig 
    INNER JOIN mage_restore.sales_order AS res 
    ON orig.entity_id=res.entity_id 
SET orig.customer_id=res.customer_id;

This will attempt to set the customer_id back to the correct customer. Add ignore to the query if you have some partial data left

Now run the next query in order to add the association back to the order grid:

UPDATE sales_order_grid 
    INNER JOIN sales_order 
    ON sales_order_grid.entity_id = sales_order.entity_id 
SET sales_order_grid.customer_id = sales_order.customer_id;

Partial data fix

If when running the previous query you had to include the ignore option in your first update query you will need to run another update query.

This will have been caused by a customer with an account recreating one because theirs was deleted.

We can fix these with the following query:

UPDATE full_restore.sales_order AS orig 
    INNER JOIN mage_restore.sales_order 
        AS res ON orig.entity_id=res.entity_id 
    INNER JOIN mage_restore.customer_entity 
        AS c ON res.customer_id=c.entity_id 
    INNER JOIN magento2.customer_entity AS c2 ON c.email=c2.email 
SET orig.customer_id=c2.entity_id 
WHERE res.customer_id IS NOT NULL AND orig.customer_id IS_NULL;

This will attempt to match the new customer to their previous account.

Data restored

We now have a complete copy of all data including orders and customers from before the deletion.

You can now either switch over your magento installation to use full_restore or dump it out and reimport it back into your main database.