Remove test data from your Magento website

One of the easiest and quickest ways to remove Magento data is to perform queries directly on the database. If you are remove media rows such as images that are associated with products, you also need to remove the image files as well. This is easy to do and the below should be used if you wish to wipe all data.

The below code can be useful if you wish to:

  • Remove products and categories from the database
  • Remove test data from your Magento website
  • Remove customers and their addresses
  • Remove data ready for a website to go live

Please note the below queries will remove all data so be careful.

# remove customers DELETE FROM `magento_customer_entity`; DELETE FROM `magento_customer_entity_datetime`; DELETE FROM `magento_customer_entity_decimal`; DELETE FROM `magento_customer_entity_int`; DELETE FROM `magento_customer_entity_text`; DELETE FROM `magento_customer_entity_varchar`; DELETE FROM `magento_newsletter_subscriber`; ALTER TABLE `magento_customer_entity` AUTO_INCREMENT=1; ALTER TABLE `magento_customer_entity_datetime` AUTO_INCREMENT=1; ALTER TABLE `magento_customer_entity_decimal` AUTO_INCREMENT=1; ALTER TABLE `magento_customer_entity_int` AUTO_INCREMENT=1; ALTER TABLE `magento_customer_entity_text` AUTO_INCREMENT=1; ALTER TABLE `magento_customer_entity_varchar` AUTO_INCREMENT=1; ALTER TABLE `magento_newsletter_subscriber` AUTO_INCREMENT=1; # or DELETE FROM `customer_entity`; DELETE FROM `customer_entity_datetime`; DELETE FROM `customer_entity_decimal`; DELETE FROM `customer_entity_int`; DELETE FROM `customer_entity_text`; DELETE FROM `customer_entity_varchar`; DELETE FROM `newsletter_subscriber`; ALTER TABLE `customer_entity` AUTO_INCREMENT=1; 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 `newsletter_subscriber` AUTO_INCREMENT=1; # remove customer addresses DELETE FROM `magento_customer_address_entity`; DELETE FROM `magento_customer_address_entity_datetime`; DELETE FROM `magento_customer_address_entity_decimal`; DELETE FROM `magento_customer_address_entity_int`; DELETE FROM `magento_customer_address_entity_text`; DELETE FROM `magento_customer_address_entity_varchar`; # or DELETE FROM `customer_address_entity`; DELETE FROM `customer_address_entity_datetime`; DELETE FROM `customer_address_entity_decimal`; DELETE FROM `customer_address_entity_int`; DELETE FROM `customer_address_entity_text`; DELETE FROM `customer_address_entity_varchar`; # remove categories DELETE FROM `magento_catalog_category_entity` WHERE `entity_id` > 2; DELETE FROM `magento_catalog_category_entity_datetime` WHERE `entity_id` > 2; DELETE FROM `magento_catalog_category_entity_decimal` WHERE `entity_id` > 2; DELETE FROM `magento_catalog_category_entity_int` WHERE `entity_id` > 2; DELETE FROM `magento_catalog_category_entity_text` WHERE `entity_id` > 2; DELETE FROM `magento_catalog_category_product` WHERE `category_id` > 2; DELETE FROM `magento_catalog_category_flat_store_1` WHERE `entity_id` > 2; ALTER TABLE `magento_catalog_category_entity` AUTO_INCREMENT=3; ALTER TABLE `magento_catalog_category_entity_datetime` AUTO_INCREMENT=3; ALTER TABLE `magento_catalog_category_entity_decimal` AUTO_INCREMENT=3; ALTER TABLE `magento_catalog_category_entity_int` AUTO_INCREMENT=3; ALTER TABLE `magento_catalog_category_entity_text` AUTO_INCREMENT=3; ALTER TABLE `magento_catalog_category_product` AUTO_INCREMENT=3; ALTER TABLE `magento_catalog_category_flat_store_1` AUTO_INCREMENT=3; # or DELETE FROM `catalog_category_entity` WHERE `entity_id` > 2; DELETE FROM `catalog_category_entity_datetime` WHERE `entity_id` > 2; DELETE FROM `catalog_category_entity_decimal` WHERE `entity_id` > 2; DELETE FROM `catalog_category_entity_int` WHERE `entity_id` > 2; DELETE FROM `catalog_category_entity_text` WHERE `entity_id` > 2; DELETE FROM `catalog_category_product` WHERE `category_id` > 2; DELETE FROM `catalog_category_flat_store_1` WHERE `entity_id` > 2; ALTER TABLE `catalog_category_entity` AUTO_INCREMENT=3; ALTER TABLE `catalog_category_entity_datetime` AUTO_INCREMENT=3; ALTER TABLE `catalog_category_entity_decimal` AUTO_INCREMENT=3; ALTER TABLE `catalog_category_entity_int` AUTO_INCREMENT=3; ALTER TABLE `catalog_category_entity_text` AUTO_INCREMENT=3; ALTER TABLE `catalog_category_product` AUTO_INCREMENT=3; ALTER TABLE `catalog_category_flat_store_1` AUTO_INCREMENT=3; # remove cms pages DELETE FROM `magento_cms_page` WHERE `page_id` >= 7 DELETE FROM `magento_cms_page_store` WHERE `page_id` >= 7 # or DELETE FROM `cms_page` WHERE `page_id` >= 7 DELETE FROM `cms_page_store` WHERE `page_id` >= 7 # remove products DELETE FROM `magento_catalog_category_product`; DELETE FROM `magento_catalog_category_product_index`; DELETE FROM `magento_catalog_category_product_index_enbl_idx`; DELETE FROM `magento_catalog_category_product_index_enbl_tmp`; DELETE FROM `magento_catalog_category_product_index_enbl_tmp`; DELETE FROM `magento_catalog_category_product_index_tmp`; DELETE FROM `magento_catalog_product_bundle_option`; DELETE FROM `magento_catalog_product_bundle_option_value`; DELETE FROM `magento_catalog_product_bundle_price_index`; DELETE FROM `magento_catalog_product_bundle_selection`; DELETE FROM `magento_catalog_product_bundle_selection_price`; DELETE FROM `magento_catalog_product_bundle_stock_index`; DELETE FROM `magento_catalog_product_enabled_index`; DELETE FROM `magento_catalog_product_entity`; DELETE FROM `magento_catalog_product_entity_datetime`; DELETE FROM `magento_catalog_product_entity_decimal`; DELETE FROM `magento_catalog_product_entity_gallery`; DELETE FROM `magento_catalog_product_entity_int`; DELETE FROM `magento_catalog_product_entity_media_gallery`; DELETE FROM `magento_catalog_product_entity_media_gallery_value`; DELETE FROM `magento_catalog_product_entity_text`; DELETE FROM `magento_catalog_product_entity_tier_price`; DELETE FROM `magento_catalog_product_entity_varchar`; DELETE FROM `magento_catalog_product_index_eav`; DELETE FROM `magento_catalog_product_index_eav_decimal`; DELETE FROM `magento_catalog_product_index_eav_decimal_idx`; DELETE FROM `magento_catalog_product_index_eav_decimal_tmp`; DELETE FROM `magento_catalog_product_index_eav_idx`; DELETE FROM `magento_catalog_product_index_eav_tmp`; DELETE FROM `magento_catalog_product_index_price`; DELETE FROM `magento_catalog_product_index_price_bundle_idx`; DELETE FROM `magento_catalog_product_index_price_bundle_opt_idx`; DELETE FROM `magento_catalog_product_index_price_bundle_opt_tmp`; DELETE FROM `magento_catalog_product_index_price_bundle_sel_idx`; DELETE FROM `magento_catalog_product_index_price_bundle_sel_tmp`; DELETE FROM `magento_catalog_product_index_price_bundle_tmp`; DELETE FROM `magento_catalog_product_index_price_cfg_opt_agr_idx`; DELETE FROM `magento_catalog_product_index_price_cfg_opt_agr_tmp`; DELETE FROM `magento_catalog_product_index_price_cfg_opt_idx`; DELETE FROM `magento_catalog_product_index_price_cfg_opt_tmp`; DELETE FROM `magento_catalog_product_index_price_downlod_idx`; DELETE FROM `magento_catalog_product_index_price_downlod_tmp`; DELETE FROM `magento_catalog_product_index_price_final_idx`; DELETE FROM `magento_catalog_product_index_price_final_tmp`; DELETE FROM `magento_catalog_product_index_price_idx`; DELETE FROM `magento_catalog_product_index_price_opt_agr_idx`; DELETE FROM `magento_catalog_product_index_price_opt_agr_tmp`; DELETE FROM `magento_catalog_product_index_price_opt_idx`; DELETE FROM `magento_catalog_product_index_price_opt_tmp`; DELETE FROM `magento_catalog_product_index_price_tmp`; DELETE FROM `magento_catalog_product_index_tier_price`; DELETE FROM `magento_catalog_product_link`; DELETE FROM `magento_catalog_product_option`; DELETE FROM `magento_catalog_product_option_price`; DELETE FROM `magento_catalog_product_option_title`; DELETE FROM `magento_catalog_product_option_type_price`; DELETE FROM `magento_catalog_product_option_type_title`; DELETE FROM `magento_catalog_product_option_type_value`; DELETE FROM `magento_catalog_product_relation`; DELETE FROM `magento_catalog_product_super_attribute`; DELETE FROM `magento_catalog_product_super_attribute_label`; DELETE FROM `magento_catalog_product_super_attribute_pricing`; DELETE FROM `magento_catalog_product_super_link`; DELETE FROM `magento_catalog_product_website`; ALTER TABLE `magento_catalog_product_bundle_option` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_bundle_option_value` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_bundle_selection` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_entity` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_entity_datetime` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_entity_decimal` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_entity_gallery` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_entity_int` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_entity_media_gallery` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_entity_text` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_entity_tier_price` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_entity_varchar` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_link` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_option` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_option_price` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_option_title` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_option_type_price` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_option_type_title` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_option_type_value` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_super_attribute` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_super_attribute_label` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_super_attribute_pricing` AUTO_INCREMENT=1; ALTER TABLE `magento_catalog_product_super_link` AUTO_INCREMENT=1; # or DELETE FROM `catalog_category_product`; DELETE FROM `catalog_category_product_index`; DELETE FROM `catalog_category_product_index_enbl_idx`; DELETE FROM `catalog_category_product_index_enbl_tmp`; DELETE FROM `catalog_category_product_index_enbl_tmp`; DELETE FROM `catalog_category_product_index_tmp`; DELETE FROM `catalog_product_bundle_option`; DELETE FROM `catalog_product_bundle_option_value`; DELETE FROM `catalog_product_bundle_price_index`; DELETE FROM `catalog_product_bundle_selection`; DELETE FROM `catalog_product_bundle_selection_price`; DELETE FROM `catalog_product_bundle_stock_index`; DELETE FROM `catalog_product_enabled_index`; DELETE FROM `catalog_product_entity`; DELETE FROM `catalog_product_entity_datetime`; DELETE FROM `catalog_product_entity_decimal`; DELETE FROM `catalog_product_entity_gallery`; DELETE FROM `catalog_product_entity_int`; DELETE FROM `catalog_product_entity_media_gallery`; DELETE FROM `catalog_product_entity_media_gallery_value`; DELETE FROM `catalog_product_entity_text`; DELETE FROM `catalog_product_entity_tier_price`; DELETE FROM `catalog_product_entity_varchar`; DELETE FROM `catalog_product_index_eav`; DELETE FROM `catalog_product_index_eav_decimal`; DELETE FROM `catalog_product_index_eav_decimal_idx`; DELETE FROM `catalog_product_index_eav_decimal_tmp`; DELETE FROM `catalog_product_index_eav_idx`; DELETE FROM `catalog_product_index_eav_tmp`; DELETE FROM `catalog_product_index_price`; DELETE FROM `catalog_product_index_price_bundle_idx`; DELETE FROM `catalog_product_index_price_bundle_opt_idx`; DELETE FROM `catalog_product_index_price_bundle_opt_tmp`; DELETE FROM `catalog_product_index_price_bundle_sel_idx`; DELETE FROM `catalog_product_index_price_bundle_sel_tmp`; DELETE FROM `catalog_product_index_price_bundle_tmp`; DELETE FROM `catalog_product_index_price_cfg_opt_agr_idx`; DELETE FROM `catalog_product_index_price_cfg_opt_agr_tmp`; DELETE FROM `catalog_product_index_price_cfg_opt_idx`; DELETE FROM `catalog_product_index_price_cfg_opt_tmp`; DELETE FROM `catalog_product_index_price_downlod_idx`; DELETE FROM `catalog_product_index_price_downlod_tmp`; DELETE FROM `catalog_product_index_price_final_idx`; DELETE FROM `catalog_product_index_price_final_tmp`; DELETE FROM `catalog_product_index_price_idx`; DELETE FROM `catalog_product_index_price_opt_agr_idx`; DELETE FROM `catalog_product_index_price_opt_agr_tmp`; DELETE FROM `catalog_product_index_price_opt_idx`; DELETE FROM `catalog_product_index_price_opt_tmp`; DELETE FROM `catalog_product_index_price_tmp`; DELETE FROM `catalog_product_index_tier_price`; DELETE FROM `catalog_product_link`; DELETE FROM `catalog_product_option`; DELETE FROM `catalog_product_option_price`; DELETE FROM `catalog_product_option_title`; DELETE FROM `catalog_product_option_type_price`; DELETE FROM `catalog_product_option_type_title`; DELETE FROM `catalog_product_option_type_value`; DELETE FROM `catalog_product_relation`; DELETE FROM `catalog_product_super_attribute`; DELETE FROM `catalog_product_super_attribute_label`; DELETE FROM `catalog_product_super_attribute_pricing`; DELETE FROM `catalog_product_super_link`; DELETE FROM `catalog_product_website`; # remove orders, invoices, quotes and shipments DELETE FROM `magento_sales_flat_creditmemo`; DELETE FROM `magento_sales_flat_creditmemo_comment`; DELETE FROM `magento_sales_flat_creditmemo_grid`; DELETE FROM `magento_sales_flat_creditmemo_item`; DELETE FROM `magento_sales_flat_invoice`; DELETE FROM `magento_sales_flat_invoice_comment`; DELETE FROM `magento_sales_flat_invoice_grid`; DELETE FROM `magento_sales_flat_invoice_item`; DELETE FROM `magento_sales_flat_order`; DELETE FROM `magento_sales_flat_order_address`; DELETE FROM `magento_sales_flat_order_grid`; DELETE FROM `magento_sales_flat_order_item`; DELETE FROM `magento_sales_flat_order_payment`; DELETE FROM `magento_sales_flat_order_status_history`; DELETE FROM `magento_sales_flat_quote`; DELETE FROM `magento_sales_flat_quote_address`; DELETE FROM `magento_sales_flat_quote_address_item`; DELETE FROM `magento_sales_flat_quote_item`; DELETE FROM `magento_sales_flat_quote_item_option`; DELETE FROM `magento_sales_flat_quote_payment`; DELETE FROM `magento_sales_flat_quote_shipping_rate`; DELETE FROM `magento_sales_flat_shipment`; DELETE FROM `magento_sales_flat_shipment_comment`; DELETE FROM `magento_sales_flat_shipment_grid`; DELETE FROM `magento_sales_flat_shipment_item`; DELETE FROM `magento_sales_flat_shipment_track`; DELETE FROM `magento_sales_invoiced_aggregated`; DELETE FROM `magento_sales_invoiced_aggregated_order`; DELETE FROM `magento_sales_order_aggregated_created`; #DELETE FROM `magento_sales_order_aggregated_updated`; DELETE FROM `magento_sales_order_tax`; #DELETE FROM `magento_sales_order_tax_item`; DELETE FROM `magento_sales_payment_transaction`; DELETE FROM `magento_sales_refunded_aggregated`; DELETE FROM `magento_sales_refunded_aggregated_order`; DELETE FROM `magento_sales_shipping_aggregated`; DELETE FROM `magento_sales_shipping_aggregated_order`; # or DELETE FROM `sales_flat_creditmemo`; DELETE FROM `sales_flat_creditmemo_comment`; DELETE FROM `sales_flat_creditmemo_grid`; DELETE FROM `sales_flat_creditmemo_item`; DELETE FROM `sales_flat_invoice`; DELETE FROM `sales_flat_invoice_comment`; DELETE FROM `sales_flat_invoice_grid`; DELETE FROM `sales_flat_invoice_item`; DELETE FROM `sales_flat_order`; DELETE FROM `sales_flat_order_address`; DELETE FROM `sales_flat_order_grid`; DELETE FROM `sales_flat_order_item`; DELETE FROM `sales_flat_order_payment`; DELETE FROM `sales_flat_order_status_history`; DELETE FROM `sales_flat_quote`; DELETE FROM `sales_flat_quote_address`; DELETE FROM `sales_flat_quote_address_item`; DELETE FROM `sales_flat_quote_item`; DELETE FROM `sales_flat_quote_item_option`; DELETE FROM `sales_flat_quote_payment`; DELETE FROM `sales_flat_quote_shipping_rate`; DELETE FROM `sales_flat_shipment`; DELETE FROM `sales_flat_shipment_comment`; DELETE FROM `sales_flat_shipment_grid`; DELETE FROM `sales_flat_shipment_item`; DELETE FROM `sales_flat_shipment_track`; DELETE FROM `sales_invoiced_aggregated`; DELETE FROM `sales_invoiced_aggregated_order`; DELETE FROM `sales_order_aggregated_created`; #DELETE FROM `sales_order_aggregated_updated`; DELETE FROM `sales_order_tax`; #DELETE FROM `sales_order_tax_item`; DELETE FROM `sales_payment_transaction`; DELETE FROM `sales_refunded_aggregated`; DELETE FROM `sales_refunded_aggregated_order`; DELETE FROM `sales_shipping_aggregated`; DELETE FROM `sales_shipping_aggregated_order`; # remove products with an id less than 12000 DELETE FROM `magento_catalog_category_product` WHERE `product_id` < 12000; DELETE FROM `magento_catalog_product_bundle_option` WHERE `parent_id` < 12000; DELETE FROM `magento_catalog_product_entity` WHERE `entity_id` < 12000; DELETE FROM `magento_catalog_product_entity_datetime` WHERE `entity_id` < 12000; DELETE FROM `magento_catalog_product_entity_decimal` WHERE `entity_id` < 12000; DELETE FROM `magento_catalog_product_entity_gallery` WHERE `entity_id` < 12000; DELETE FROM `magento_catalog_product_entity_int` WHERE `entity_id` < 12000; DELETE FROM `magento_catalog_product_entity_media_gallery` WHERE `entity_id` < 12000; DELETE FROM `magento_catalog_product_entity_text` WHERE `entity_id` < 12000; DELETE FROM `magento_catalog_product_entity_tier_price` WHERE `entity_id` < 12000; DELETE FROM `magento_catalog_product_entity_varchar` WHERE `entity_id` < 12000; DELETE FROM `magento_catalog_product_link` WHERE `product_id` < 12000 OR `linked_product_id` < 12000; DELETE FROM `magento_catalog_product_option` WHERE `product_id` < 12000; DELETE FROM `magento_catalog_product_relation` WHERE `parent_id` < 12000 OR `child_id` < 12000; DELETE FROM `magento_catalog_product_super_attribute` WHERE `product_id` < 12000; DELETE FROM `magento_catalog_product_super_link` WHERE `product_id` < 12000 OR `parent_id` < 12000; DELETE FROM `magento_catalog_product_website` WHERE `product_id` < 12000; DELETE FROM `magento_sales_flat_quote_item` WHERE `product_id` < 12000; DELETE FROM `magento_sales_flat_quote_item_option` WHERE `product_id` < 12000; DELETE FROM `magento_sales_flat_quote_address_item` WHERE `product_id` < 12000; # remove all quotes and cart items (this completely wipes everyones shopping carts) DELETE FROM `magento_sales_flat_quote`; DELETE FROM `magento_sales_flat_quote_address`; DELETE FROM `magento_sales_flat_quote_item`; DELETE FROM `magento_sales_flat_quote_item_option`; DELETE FROM `magento_sales_flat_quote_payment`; DELETE FROM `magento_sales_flat_quote_shipping_rate`; DELETE FROM `magento_sales_flat_quote_address_item`;