How to remove test data from a Magento website

| Posted by absolute

When we test a new website we generate a lot of data, and some of this test data includes lots of fake customers and orders. 

But before the website goes live we want to clear all this test data out so that any subsequent reporting isn’t incorrectly skewed and to ensure order IDs are consistent.

However, Magento doesn’t allow you to remove orders from the backend and certainly doesn’t let you reset the IDs. So, in order to resolve this we run the following SQL queries to reset everything back to its default state:

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `catalogsearch_query`;
ALTER TABLE `catalogsearch_query` AUTO_INCREMENT=1;
TRUNCATE `sales_flat_order`;
TRUNCATE `sales_flat_order_address`;
TRUNCATE `sales_flat_order_grid`;
TRUNCATE `sales_flat_order_item`;
TRUNCATE `sales_flat_order_payment`;
TRUNCATE `sales_flat_order_status_history`;
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 `tag_relation`;
TRUNCATE `tag_summary`;
TRUNCATE `wishlist`;
TRUNCATE `log_quote`;
TRUNCATE `report_event`;
ALTER TABLE `sales_flat_order` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_address` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_payment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_status_history` 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;
TRUNCATE `eav_entity_store`;
ALTER TABLE `eav_entity_store` AUTO_INCREMENT=1;
INSERT INTO `eav_entity_store` (`entity_store_id` ,`entity_type_id` ,`store_id`,`increment_prefix` ,`increment_last_id`) 
VALUES(‘1’, ‘11’, ‘1’, ‘1’, ‘000000000’);
update `eav_entity_store` set `increment_prefix`= 1 where `entity_type_id`=‘5’ and `store_id`=‘1’;
update `eav_entity_store` set `increment_last_id`= ‘000000000’ where `entity_type_id`=‘4’ and `store_id`=‘1’;
INSERT INTO `eav_entity_store` (`entity_store_id` ,`entity_type_id` ,`store_id` ,`increment_prefix` ,`increment_last_id`) VALUES(‘2’, ‘16’, ‘1’, ‘2’, ‘000000000’);
update `eav_entity_store` set `increment_prefix`= 2 where `entity_type_id`=‘6’ and `store_id`=‘1’;
update `eav_entity_store` set `increment_last_id`= ‘000000000’ where `entity_type_id`=‘18’ and `store_id`=‘1’;
INSERT INTO `eav_entity_store` (`entity_store_id` ,`entity_type_id` ,`store_id` ,`increment_prefix` ,`increment_last_id`) VALUES(‘3’, ‘19’, ‘1’, ‘3’, ‘000000000’);
update `eav_entity_store` set `increment_prefix`= 3 where `entity_type_id`=‘8’ and `store_id`=‘1’;
update `eav_entity_store` set `increment_last_id`= ‘000000000’ where `entity_type_id`=‘24’ and `store_id`=‘1’;
INSERT INTO `eav_entity_store` (`entity_store_id` ,`entity_type_id` ,`store_id` ,`increment_prefix` ,`increment_last_id`) VALUES(‘4’, ‘23’, ‘1’, ‘4’, ‘000000000’);
update `eav_entity_store` set `increment_prefix`= 4 where `entity_type_id`=‘7’ and `store_id`=‘1’;
update `eav_entity_store` set `increment_last_id`= ‘000000000’ where `entity_type_id`=‘28’ and `store_id`=‘1’;
SET FOREIGN_KEY_CHECKS=1;
SET FOREIGN_KEY_CHECKS=0;
TRUNCATE customer_address_entity;
TRUNCATE customer_address_entity_datetime;
TRUNCATE customer_address_entity_decimal;
TRUNCATE customer_address_entity_int;
TRUNCATE customer_address_entity_text;
TRUNCATE customer_address_entity_varchar;
TRUNCATE customer_entity;
TRUNCATE customer_entity_datetime;
TRUNCATE customer_entity_decimal;
TRUNCATE customer_entity_int;
TRUNCATE customer_entity_text;
TRUNCATE customer_entity_varchar;
TRUNCATE log_customer;
TRUNCATE log_visitor;
TRUNCATE log_visitor_info;
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=30000;
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 log_customer AUTO_INCREMENT=1;
ALTER TABLE log_visitor AUTO_INCREMENT=1;
ALTER TABLE log_visitor_info AUTO_INCREMENT=1;
SET FOREIGN_KEY_CHECKS=1’