Move Magento Database Sales, Customers & Invoices

To move a Magento website with its database is one thing, but what if you only want to update a Magento websites' sales and recent customers? These steps may be required when your project is under the following conditions:

Orders, Sales, Magento-AdminIf the updates done to the development site are database related and not template related and the websites signup or purchase processes have not been turned off during a transfer process + development changes, then the option to simply re-transferring a database would be less preferred option.

The moving of only Magento sales and customers, as well as the sales + customer sync, can be done in 3 simple steps:

Note: This tutorial has been tested on Magento 1.7.0.2 installed on both websites. Database tables may vary based on different Magento versions.

Disabling Magento during Transfer

To avoid new signups and/or sales to the live website during the transfer process, consider disabling the checkout and signup from the configuration or create a blank file named maintenance.flag and place it on the root of your Magento install.

Move Magento with PHPmyAdmin Export/Import

A more convenient way of transferring the below database tables is by doing it in PHPmyAdmin, where you can simply export the tables to a SQL file by choosing them in the Export page of the production sites database and importing them into the target Magento installs database. You can also enable the statement Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement in the Export options to avoid manually dropping those tables in the target database.

Transferring Customers & Sales

The first step is to transfer all the tables starting with customers_ and sales_, which are as follows on a Magento 1.7.0.2 install:

'customer_address_entity'
'customer_address_entity_datetime'
'customer_address_entity_decimal'
'customer_address_entity_int'
'customer_address_entity_text'
'customer_address_entity_varchar'
'customer_eav_attribute'
'customer_eav_attribute_website'
'customer_entity'
'customer_entity_datetime'
'customer_entity_decimal'
'customer_entity_int'
'customer_entity_text'
'customer_entity_varchar'
'customer_form_attribute'
'customer_group'

And the sales_ tables are:

'sales_bestsellers_aggregated_daily';
'sales_bestsellers_aggregated_monthly';
'sales_bestsellers_aggregated_yearly';
'sales_billing_agreement';
'sales_billing_agreement_order';
'sales_flat_creditmemo';
'sales_flat_creditmemo_comment';
'sales_flat_creditmemo_grid';
'sales_flat_creditmemo_item';
'sales_flat_invoice';
'sales_flat_invoice_comment';
'sales_flat_invoice_grid';
'sales_flat_invoice_item';
'sales_flat_order';
'sales_flat_order_address';
'sales_flat_order_grid';
'sales_flat_order_item';
'sales_flat_order_payment';
'sales_flat_order_status_history';
'sales_flat_quote';
'sales_flat_quote_address';
'sales_flat_quote_address_item';
'sales_flat_quote_item';
'sales_flat_quote_item_option';
'sales_flat_quote_payment';
'sales_flat_quote_shipping_rate';
'sales_flat_shipment';
'sales_flat_shipment_comment';
'sales_flat_shipment_grid';
'sales_flat_shipment_item';
'sales_flat_shipment_track';
'sales_invoiced_aggregated';
'sales_invoiced_aggregated_order';
'sales_order_aggregated_created';
'sales_order_aggregated_updated';
'sales_order_status';
'sales_order_status_label';
'sales_order_status_state';
'sales_order_tax';
'sales_order_tax_item';
'sales_payment_transaction';
'sales_recurring_profile';
'sales_recurring_profile_order';
'sales_refunded_aggregated';
'sales_refunded_aggregated_order';
'sales_shipping_aggregated';
'sales_shipping_aggregated_order';

Last Order ID for Sales

magento-eav-entity-store

Next, a step that is fairly easy, but that is often missed is the changing of the last entity ID for orders, invoices, credit memos and shipments. Otherwise, Magento will ignore the sales data that did not exist prior to the transfer.

For example: Imagine a Magento store has 7 orders (both development and production), the last orders' id would probably be 100000007.

During development, your production website acquires 2 additional orders (a total of 9 orders).

After moving your live sites' sales_ tables, your development site will reflect the 9 orders correctly, but will still count from 7, instead of 9.

Hence, the next order will have an ID of 100000008 instead of 100000010, unless the last order ID is updated. The same applies for invoices, shipping and credit memos.

The fix is simple! Simply edit the table eav_entity_store and update the increment_last_id column with the last id used for each row. Each rows' entity type can be found in the table eav_entity_type. In this example (see above image), the entity types are:

  • 5 = order
  • 6 = invoice
  • 7 = creditmemo
  • 8 = shipment

That should be all that's needed for a simple customer and sales update on two, otherwise identical Magento 1.7.0.2 sites. Hope his tutorial was useful and please feel free to comment and suggestions, questions or fixes.

I'm a developer at Maximus Business.
  • Elvira
    Awesome! I'd just like to add that if you're using extensions don't forget to check their tables.

    Said on Apr 1, 2014 by Elvira - Reply
  • LakewoodWeb
    Is there a better / easier way to do this ? :-)

    Said on Apr 7, 2014 by LakewoodWeb - Reply
  • Adrian
    Hi, There is one more thing to do... When you transfer your db using phpMyAdmin you should insert some SQL statement from this site: http://www.magentocommerce.com/wiki/1<_installation_and_configuration/restoring_a_backup_of_a_magento_database Great article, by the way :)

    Said on May 15, 2014 by Adrian - Reply
  • Dan
    There's this script: https://github.com/iateadonut/magento_copy_customer It grabs a customer and all his orders through the single command: $mg->copy_customer(1234); if 1234 is the customer_entity.entity_id - You can take a look in the source code to see how the table restraints were queried to make sure all rows were grabbed.

    Said on May 29, 2014 by Dan - Reply
  • Aurélien
    Hello, I do not understand which with which value I need to update the increment_last_id ? For example the entity_id 1 is the customer, and the his increment_last_id is 000000188. But it's the same number in my dev database and in my live database, why ? Should I upgrade with my last customer id find in the admin ? Why I cannt copy all the eav_ table from my live server to my dev ? Thank you

    Said on Jun 5, 2014 by Aurélien - Reply
  • Rob
    Thanks man, works great.

    Said on Sep 15, 2014 by Rob - Reply
  • Jacco
    Can this be used to move sales from 1.7 to a 1.9 install? I want to complete my install in a test enviroment and then move the latest sales and customers over to the new database. This would greatly help my moving day

    Said on Sep 23, 2014 by Jacco - Reply
  • Danny Longhurst
    Hi Just a couple of questions re this if you can help 1 What is the likely hood of this working where the new site is now a 1.9 version of magento? 2 Re last entity ID. When and where do you change this? Prior to importing the livesite data and to the existing data tables in the devsite? Or to the exported tables in an editor prior to importing? 3 If this method is not going to work with an upgraded magento site how am I going to update my devsite with these details? Many many thanks

    Said on Oct 2, 2014 by Danny Longhurst - Reply
  • Alize
    Thanks a lot, this was exactly what i was looking for to transfer from Magento 1.8 to 1.9!

    Said on Nov 7, 2014 by Alize - Reply
  • Tim
    This worked on a CE1.7->1.9 data set. I had a bunch of trouble with Foreign Keys bringing in customers but dropped the relevant tables, disabled FK checks, added em in, and bang. Be awesome seeing something like this for products. Thanks for putting the time into writing this post. Tim

    Said on Dec 9, 2014 by Tim - Reply
  • Mario
    Nice info. Additionally you should find all related tables checking FK relationships for customer_entity and sales_flat_order tables using these queries: USE information_schema; SELECT ti.`TABLE_NAME`, t.`TABLE_ROWS` FROM KEY_COLUMN_USAGE as ti LEFT JOIN TABLES as t ON t.TABLE_SCHEMA = ti.`CONSTRAINT_SCHEMA` AND t.table_name = ti.`TABLE_NAME` WHERE ti.CONSTRAINT_SCHEMA = 'magento_prod' AND ti.REFERENCED_TABLE_NAME = 'customer_entity' AND ti.REFERENCED_COLUMN_NAME = 'entity_id'; SELECT ti.`TABLE_NAME`, t.`TABLE_ROWS` FROM KEY_COLUMN_USAGE as ti LEFT JOIN TABLES as t ON t.TABLE_SCHEMA = ti.`CONSTRAINT_SCHEMA` AND t.table_name = ti.`TABLE_NAME` WHERE CONSTRAINT_SCHEMA = 'magento_prod' AND REFERENCED_TABLE_NAME = 'sales_flat_order' AND REFERENCED_COLUMN_NAME = 'entity_id'; These queries are not bulletproof because there are tables that are not defining an explicit FK to customer_entity (wishlist was fixed in a recent version).

    Said on Feb 2, 2015 by Mario - Reply
  • Jonathan
    Hi, tried as you described but get an error while importing: Can't create table 'customer_address_entity' (errno: 150). Did what Adrian suggested: "http://www.magentocommerce.com/wiki/1<_installation_and_configuration/restoring_a_backup_of_a_magento_database" Tried to move customer and orders from CE1.6 to CE1.9. Have you any suggestion what this could be caused by? Thanks in advance

    Said on Feb 23, 2015 by Jonathan - Reply
    • Jonathan
      Thanks to the help of Maximilian I could fix my problem. In my case I couldn't create a table if I dropped it while importing. So for export via PHPmyAdmin I choose "Truncate table before insert" and disabled all "create"-statements except: "Create table options" Thanks Max, nice tutorial

      Said on Feb 24, 2015 by Jonathan - Reply
  • Bob Davey
    Hi, I'm trying to follow your instructions but when I try to import customers I get the following warning: -- Dumping data for table `customer_address_entity` -- INSERT INTO `customer_address_entity` (`entity_id`, `entity_type_id`, `attribute_set_id`, `increment_id`, `parent_id`, `created_at`, `updated_at`, `is_active`) VALUES (1, 2, 0, NULL, 1, '2011-08-13 10:25:52', '2014-10-18 12:09:57', 1), (2, 2, 0, '', 2, '2011-08-10 11:58:03', '2012-08-18 12:21:27', 1), (3, 2, 0, '', 3, '2011-08-10 19:23:16', '2011-08-10 19:23:16', 1), (4, 2, 0, '', 4, '2011-08-11 11:39:19', '2011-08-11 11:41:20', 1), (5, 2, 0, '', 5, '2011-08-11 14:07:33', '2011-11-28 09:35:00', 1), (6, 2, 0, '', 6, '2011-08-12 15:38:16', '2011-08-12 15:38:16', 1), (7, 2, 0, '', 7, '2011-08-15 05:12:05', '2011-08-17 07:44:58', 1), (8, 2, 0, '', 8, '2011-08-15 16:32:52', '2011-12-31 02:16:00', 1), (9, 2, 0, '', 9, '2011-08-15 19:07:23', '2011-08-15 19:07:23', 1), (10, 2, 0, '', 10, '2011-08-16 15:47:44', '2011-08-16 15:47:44', 1), (11, 2, 0, '', 11, '2011-08-18 09:42:58', '2011-08-18 09:42:58', 1), (12, 2, 0, '', 12, '2011-08-2[...] MySQL said: #1062 - Duplicate entry '1' for key 'PRIMARY' I tried exporting the data using Jonathan's option "Truncate table before insert" and disabled all "create"-statements except: "Create table options" but then i get an error: INSERT INTO `customer_address_entity` (`entity_id`, `entity_type_id`, `attribute_set_id`, `increment_id`, `parent_id`, `created_at`, `updated_at`, `is_active`) VALUES (717, 2, 0, NULL, 629, '2014-11-09 01:45:37', '2014-11-08 17:45:38', 1), (718, 2, 0, NULL, 629, '2014-11-09 01:45:37', '2014-11-08 17:45:38', 1), (719, 2, 0, NULL, 630, '2014-11-15 07:38:50', '2014-11-14 23:38:50', 1), (720, 2, 0, NULL, 631, '2014-11-15 22:51:20', '2014-11-15 14:51:20', 1), (721, 2, 0, NULL, 631, '2014-11-15 22:51:20', '2014-11-15 14:51:20', 1), (722, 2, 0, NULL, 632, '2014-11-16 21:55:03', '2014-11-16 13:55:04', 1), (723, 2, 0, NULL, 632, '2014-11-16 21:55:03', '2014-11-16 13:55:04', 1), (724, 2, 0, NULL, 633, '2014-11-16 22:54:16', '2014-11-16 14:54:17', 1), (725, 2, 0, NULL, 633, '2014-11-16 22:54:17', '2014-11-16 14:54:17', 1), (726, 2, 0, NULL, 634, '2014-11-18 04:07:17', '2014-11-17 20:07:18', 1), (727, 2, 0, NULL, 634, '2014-11-18 04:07:18', '2014-11-17 20:07:18', 1), (728, 2, 0, NULL, 635, '2014-[...] MySQL said: #1452 - Cannot add or update a child row: a foreign key constraint fails (`admin_ma9poster`.`customer_address_entity`, CONSTRAINT `FK_CUSTOMER_ADDRESS_ENTITY_PARENT_ID_CUSTOMER_ENTITY_ENTITY_ID` FOREIGN KEY (`parent_id`) REFERENCES `customer_entity` (`entity_id`) ON) Magento CE1.8.1 to CE1.9.1 Any help would be much appreciated, Thanks.

    Said on Apr 9, 2015 by Bob Davey - Reply
    • Nick
      I'm getting the same, I also disabled foreign keys - but it just will not import .

      Said on Apr 22, 2015 by Nick - Reply
    • Sean Casey
      Hey Bob, In this instance it looks like it is throwing an error because your customer_address_entity has a foreign key and is trying to reference the entity_id in customer_entity. If you have not set up that table yet, that will cause the problem. Be sure to look at the relationships and foreign keys between each of the tables as you import them. I had to jump around the tables when I needed. If you need to view the relations,, and you are using myphpadmin you can click on structure then relation view. Let me know if this helps!

      Said on May 5, 2015 by Sean Casey - Reply
  • Sean Grünböck
    Thanks for your great article! I created a Shell Script based on your information, since I have to dump and restore quite often... if anybody is interested, you can download it here: https://gist.github.com/seangreen/c293bd59815ed73f264f

    Said on Jun 10, 2015 by Sean Grünböck - Reply
  • Ryan G
    Does this shell script work with a 1.9 Magento DB? I tried running it but I keep getting the following error; 12: Syntax Error: "(" Unexpected

    Said on Aug 18, 2015 by Ryan G - Reply
  • Roger Wiese
    Great article. Exactly what I was looking for. Taking 1.7.0.2 > 1.9.2.1 worked 100% Sean ... I tried your script... putting your script into a file call dumpsales.sh but .. running your script & got this error -bash: ./dumpsales.sh: /bin/bash^M: bad interpreter: No such file or directory I wonder what that is about?

    Said on Aug 30, 2015 by Roger Wiese - Reply
  • Josh
    Didn't have much luck importing the customers_ and sales_ tables using PHPMyAdmin so I used Sequel Pro to run export/import and worked fine that way. Thanks for sharing.

    Said on Aug 31, 2015 by Josh - Reply
  • Larry S-M
    Sean, your script looks good, except I don't think it is pre-pending the table_prefix to the table names.

    Said on Sep 23, 2015 by Larry S-M - Reply
  • sankaran
    Awesome. I did this and working good.

    Said on Jan 6, 2016 by sankaran - Reply
  • glauco
    This didn't work for me. I'm only trying to import sales data as I already exported customers using Dataflow Profiles (much easier!). I don't know if it's because I'm trying to export from 1.6 to 1.9 and the table structure is different, but I get errors on import, like: Error SQL query: -- Table structure for table `mg_sales_flat_order` DROP TABLE IF EXISTS `mg_sales_flat_order` ; MySQL said: Documentation #1217 - Cannot delete or update a parent row: a foreign key constraint fails I tried a few tables at a time as well as in bulk, either way I get these errors and the tables are not updated in full, causing errors in the admin when trying to view the order data. Any ideas?

    Said on Feb 1, 2016 by glauco - Reply
  • Tahir
    I am getting error `mage.sh: 12: mage.sh: Syntax error: "(" unexpected`

    Said on Feb 4, 2016 by Tahir - Reply
  • Marcel
    Hi, I used the Sean's script to transfer orders and customer from 2 different magento website one 1.8.0 and one 1.9.2.3. Orders imported well but customers not. I see customers lists, but without names and adress and I receive error wrong website code when I try to open the customer detail. Any ideas? Thanks.

    Said on Feb 24, 2016 by Marcel - Reply
  • Maria
    If you do not want to spend additional budget on developer and have no special tech skills yourself, you can use had a reliable ready-made Magento order export / import extension, check how it works free.

    Said on Mar 29, 2016 by Maria - Reply

Leave a Reply

Please feel free to give us your feedback and comment below. Please keep in mind that comments are moderated. Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>