On many occasions we’ve needed to clear out the test data from a Magento site in preparation for going live. It’s not as trivial as you’d think, but I found a nice sql script thanks to Elias Interactive that was just what I needed. I’ve removed a bit as I didn’t need to reset all the order numbers, etc.
updated 6/30/2010 for Magento 1.4
[sourcecode language='sql'] -- Reset Magento TEST Data SET FOREIGN_KEY_CHECKS=0; -- reset dashboard search queries TRUNCATE `catalogsearch_query`; ALTER TABLE `catalogsearch_query` AUTO_INCREMENT=1; -- reset sales order info TRUNCATE `sales_order`; TRUNCATE `sales_order_datetime`; TRUNCATE `sales_order_decimal`; TRUNCATE `sales_order_entity`; TRUNCATE `sales_order_entity_datetime`; TRUNCATE `sales_order_entity_decimal`; TRUNCATE `sales_order_entity_int`; TRUNCATE `sales_order_entity_text`; TRUNCATE `sales_order_entity_varchar`; TRUNCATE `sales_order_int`; TRUNCATE `sales_order_text`; TRUNCATE `sales_order_varchar`; TRUNCATE `sales_flat_quote`; TRUNCATE `sales_flat_quote_address`; TRUNCATE `sales_flat_quote_address_item`; TRUNCATE `sales_flat_quote_item`; TRUNCATE `sales_flat_quote_item_option`; TRUNCATE `sales_flat_order_item`; TRUNCATE `sendfriend_log`; TRUNCATE `tag`; TRUNCATE `tag_relation`; TRUNCATE `tag_summary`; TRUNCATE `wishlist`; TRUNCATE `log_quote`; TRUNCATE `report_event`; TRUNCATE `sales_flat_quote_payment`; TRUNCATE `sales_flat_quote_shipping_rate`; TRUNCATE `log_url`; TRUNCATE `log_url_info`; TRUNCATE `log_visitor`; TRUNCATE `log_visitor_info`; ALTER TABLE `sales_order` AUTO_INCREMENT=1; ALTER TABLE `sales_order_datetime` AUTO_INCREMENT=1; ALTER TABLE `sales_order_decimal` AUTO_INCREMENT=1; ALTER TABLE `sales_order_entity` AUTO_INCREMENT=1; ALTER TABLE `sales_order_entity_datetime` AUTO_INCREMENT=1; ALTER TABLE `sales_order_entity_decimal` AUTO_INCREMENT=1; ALTER TABLE `sales_order_entity_int` AUTO_INCREMENT=1; ALTER TABLE `sales_order_entity_text` AUTO_INCREMENT=1; ALTER TABLE `sales_order_entity_varchar` AUTO_INCREMENT=1; ALTER TABLE `sales_order_int` AUTO_INCREMENT=1; ALTER TABLE `sales_order_text` AUTO_INCREMENT=1; ALTER TABLE `sales_order_varchar` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote_address` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote_address_item` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote_item` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote_item_option` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_order_item` AUTO_INCREMENT=1; ALTER TABLE `sendfriend_log` 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 `wishlist` AUTO_INCREMENT=1; ALTER TABLE `log_quote` AUTO_INCREMENT=1; ALTER TABLE `report_event` AUTO_INCREMENT=1; [/sourcecode]
Just run this script against the Magento database (make a backup first!).
=============================
“e-commerce done right”
http://www.ifuelinteractive.com
I have a Magento 1.3.2.3 working and need to clear all test data. This code will work for me?
Yes, Marcos. Running these queries should work for you. As always we suggest that you make an entire backup of your database before running any queries like these. Hope this has helped you!