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:

• You run two installs of your Magento website, the production (live) and the development one.
• The development version of your website was the exact copy of the live one and now has updates and/or is moved to another server.
• You intend to make the development website live, instead of transferring the changes over to the production website.
• The currently live Magento website has been running during your development and has acquired more sales and signups that need to be transferred as well.

If 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.

• Transfer Customers, Sales & Invoices
• Updating Last ID's

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

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.

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.