Magento: Move Database Sales, Customers & Invoices

11. Mai 2015 at 16:07

sql-statement-mysql

Disabling Magento during Transfer

First disable magento in the transfer time.
create a blank file named maintenance.flag and place it on the root of your Magento install.

Transferring Customers & Sales

The first step is to transfer all the tables and data starting with „customer_“ and „sales_“, which are as follows on a Magento 1.7.0.2 – 1.9.0.1 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 now export the data from the „sales_“ tables:

„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“
„salesrule“
„salesrule_coupon“
„salesrule_coupon_usage“
„salesrule_customer“
„salesrule_customer_group“
„salesrule_label“
„salesrule_product_attribute“
„salesrule_website“

Change 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.
For example: Imagine a Magento store has 9 orders (both development and production), the last orders‘ id would probably be 100000009.

During development, your production website acquires 3 additional orders (a total of 12 orders).

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

Hence, the next order will have an ID of 100000010 instead of 100000013, 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. Default the entity types in Magento 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 – 1.9.0.1 sites.

Please share this article von facebook & google plus or where you want, thank you!