1

I have delete all customer from my system

Now i have old a database , i want to restore the customers from this old database

Thus

Customer id and data are not changed

Can i try this

SET FOREIGN_KEY_CHECKS=0;
 ALTER TABLE `customer_address_entity` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `tag` AUTO_INCREMENT=1;
ALTER TABLE `tag_relation` AUTO_INCREMENT=1;
ALTER TABLE `tag_summary` AUTO_INCREMENT=1;
ALTER TABLE `tag_properties` AUTO_INCREMENT=1;
ALTER TABLE `wishlist` AUTO_INCREMENT=1;


ALTER TABLE `log_customer` AUTO_INCREMENT=1;**

then import those customer tables from old database**

then set SET FOREIGN_KEY_CHECKS=1;

Is it good and proper way ? help me?.

1
  • assign olddata base to other one text magento and export customer and import on exist site. Nov 11, 2014 at 7:39

2 Answers 2

2

If you want to import those tables with the customer and it's entities having the same ID this will be a good way to go about it.

You will have to make sure the tables stated above or empty tho, before you import your old data or you will get duplicate primary keys

4
  • am i missing any table ? i have using magento CE .7
    – Amit Bera
    Nov 11, 2014 at 6:52
  • No that seems about it Nov 11, 2014 at 6:53
  • In the train now, but I'm reasonably sure that there are links with sales. Perhaps not enforced by foreign keys. In fact, I'm pretty sure they're not enforced, cause we had a client do something like this and ended up with customers seeing order history from other customers. So be careful.
    – user4351
    Nov 11, 2014 at 7:30
  • Many many thanks @SanderMangel for give me confident this issue. Accept and +upvote from me
    – Amit Bera
    Nov 11, 2014 at 7:50
1

I have done the same mistake (deleting all customers) and based on the answer from Sander Mangel, I created a script for that, assuming I have a backup file mybackup.sql

Create in the same folder as the backup restore.sql with the content :

SET FOREIGN_KEY_CHECKS=0;
DELETE FROM `customer_address_entity`;
ALTER TABLE `customer_address_entity` AUTO_INCREMENT=1;

DELETE FROM `customer_address_entity_datetime`;
ALTER TABLE `customer_address_entity_datetime` AUTO_INCREMENT=1;

DELETE FROM `customer_address_entity_decimal`;
ALTER TABLE `customer_address_entity_decimal` AUTO_INCREMENT=1;

DELETE FROM `customer_address_entity_int`;
ALTER TABLE `customer_address_entity_int` AUTO_INCREMENT=1;

DELETE FROM `customer_address_entity_text`;
ALTER TABLE `customer_address_entity_text` AUTO_INCREMENT=1;

DELETE FROM `customer_address_entity_varchar`;
ALTER TABLE `customer_address_entity_varchar` AUTO_INCREMENT=1;

DELETE FROM `customer_entity`;
ALTER TABLE `customer_entity` AUTO_INCREMENT=1;

DELETE FROM `customer_entity_datetime`;
ALTER TABLE `customer_entity_datetime` AUTO_INCREMENT=1;

DELETE FROM `customer_entity_decimal`;
ALTER TABLE `customer_entity_decimal` AUTO_INCREMENT=1;

DELETE FROM `customer_entity_int`;
ALTER TABLE `customer_entity_int` AUTO_INCREMENT=1;

DELETE FROM `customer_entity_text`;
ALTER TABLE `customer_entity_text` AUTO_INCREMENT=1;

DELETE FROM `customer_entity_varchar`;
ALTER TABLE `customer_entity_varchar` AUTO_INCREMENT=1;

DELETE FROM `tag`;
ALTER TABLE `tag` AUTO_INCREMENT=1;

DELETE FROM `tag_relation`;
ALTER TABLE `tag_relation` AUTO_INCREMENT=1;

DELETE FROM `tag_summary`;
ALTER TABLE `tag_summary` AUTO_INCREMENT=1;

DELETE FROM `tag_properties`;
ALTER TABLE `tag_properties` AUTO_INCREMENT=1;

DELETE FROM `wishlist`;
ALTER TABLE `wishlist` AUTO_INCREMENT=1;


DELETE FROM `log_customer`;
ALTER TABLE `log_customer` AUTO_INCREMENT=1;

Then create a second file restore2.sql

SET FOREIGN_KEY_CHECKS=1;
UPDATE sales_flat_order INNER JOIN customer_entity ON sales_flat_order.customer_email = customer_entity.email SET sales_flat_order.customer_id = customer_entity.entity_id;  
UPDATE sales_flat_order_grid INNER JOIN sales_flat_order ON sales_flat_order_grid.entity_id = sales_flat_order.entity_id SET sales_flat_order_grid.customer_id = sales_flat_order.customer_id;

Then from command line, navigate to the folder containing your backup and run the following

sed -n -e '/DROP TABLE.*`customer_address_entity`/,/UNLOCK TABLES/p' mybackup.sql > customer_address_entity.sql
sed -n -e '/DROP TABLE.*`customer_address_entity_datetime`/,/UNLOCK TABLES/p' mybackup.sql > customer_address_entity_datetime.sql
sed -n -e '/DROP TABLE.*`customer_address_entity_decimal`/,/UNLOCK TABLES/p' mybackup.sql > customer_address_entity_decimal.sql
sed -n -e '/DROP TABLE.*`customer_address_entity_int`/,/UNLOCK TABLES/p' mybackup.sql > customer_address_entity_int.sql
sed -n -e '/DROP TABLE.*`customer_address_entity_text`/,/UNLOCK TABLES/p' mybackup.sql > customer_address_entity_text.sql
sed -n -e '/DROP TABLE.*`customer_address_entity_varchar`/,/UNLOCK TABLES/p' mybackup.sql > customer_address_entity_varchar.sql
sed -n -e '/DROP TABLE.*`customer_entity`/,/UNLOCK TABLES/p' mybackup.sql > customer_entity.sql
sed -n -e '/DROP TABLE.*`customer_entity_datetime`/,/UNLOCK TABLES/p' mybackup.sql > customer_entity_datetime.sql
sed -n -e '/DROP TABLE.*`customer_entity_decimal`/,/UNLOCK TABLES/p' mybackup.sql > customer_entity_decimal.sql
sed -n -e '/DROP TABLE.*`customer_entity_int`/,/UNLOCK TABLES/p' mybackup.sql > customer_entity_int.sql
sed -n -e '/DROP TABLE.*`customer_entity_text`/,/UNLOCK TABLES/p' mybackup.sql > customer_entity_text.sql
sed -n -e '/DROP TABLE.*`customer_entity_varchar`/,/UNLOCK TABLES/p' mybackup.sql > customer_entity_varchar.sql
sed -n -e '/DROP TABLE.*`tag`/,/UNLOCK TABLES/p' mybackup.sql > tag.sql
sed -n -e '/DROP TABLE.*`tag_relation`/,/UNLOCK TABLES/p' mybackup.sql > tag_relation.sql
sed -n -e '/DROP TABLE.*`tag_summary`/,/UNLOCK TABLES/p' mybackup.sql > tag_summary.sql
sed -n -e '/DROP TABLE.*`tag_properties`/,/UNLOCK TABLES/p' mybackup.sql > tag_properties.sql
sed -n -e '/DROP TABLE.*`wishlist`/,/UNLOCK TABLES/p' mybackup.sql > wishlist.sql
sed -n -e '/DROP TABLE.*`log_customer`/,/UNLOCK TABLES/p' mybackup.sql > log_customer.sql


cat restore.sql customer_address_entity.sql customer_address_entity_datetime.sql customer_address_entity_decimal.sql customer_address_entity_int.sql customer_address_entity_text.sql customer_address_entity_varchar.sql customer_entity.sql customer_entity_datetime.sql customer_entity_decimal.sql customer_entity_int.sql customer_entity_text.sql customer_entity_varchar.sql tag.sql tag_relation.sql tag_summary.sql tag_properties.sql wishlist.sql log_customer.sql restore2.sql > restorefull.sql

mysql -u root -p mytable < restorefull.sql

rm restore.sql customer_address_entity.sql customer_address_entity_datetime.sql customer_address_entity_decimal.sql customer_address_entity_int.sql customer_address_entity_text.sql customer_address_entity_varchar.sql customer_entity.sql customer_entity_datetime.sql customer_entity_decimal.sql customer_entity_int.sql customer_entity_text.sql customer_entity_varchar.sql tag.sql tag_relation.sql tag_summary.sql tag_properties.sql wishlist.sql log_customer.sql restore2.sql restorefull.sql

All your customers are back!!!

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.