When you are preparing a Magento 2 store for launch, you often need a clean slate. After weeks of testing, your database is likely filled with test orders, dummy customers, and sample products that shouldn't exist in your live environment.

While you could manually delete these entities through the Admin Panel, doing so for thousands of records is inefficient and often leaves behind orphaned data in secondary tables. The most effective way to reset your store is by truncating specific database tables via SQL.

In this guide, we will walk through the exact SQL queries needed to clear orders, customers, reviews, and products while ensuring your database integrity remains intact.

Important: Backup Before You Begin

Truncating tables is a destructive action. Once executed, the data cannot be recovered. Always take a full database backup before running these queries.

Additionally, be aware of your table prefixes. If your Magento installation uses a prefix (like mg_), you must prepend it to the table names in the scripts below.

1. How to Clear All Orders and Quotes

Clearing orders involves more than just the sales_order table. You must also clear quotes (carts), invoices, shipments, and credit memos to prevent data mismatches.

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE `gift_message`;
TRUNCATE TABLE `quote`;
TRUNCATE TABLE `quote_address`;
TRUNCATE TABLE `quote_address_item`;
TRUNCATE TABLE `quote_id_mask`;
TRUNCATE TABLE `quote_item`;
TRUNCATE TABLE `quote_item_option`;
TRUNCATE TABLE `quote_payment`;
TRUNCATE TABLE `quote_shipping_rate`;
TRUNCATE TABLE `reporting_orders`;
TRUNCATE TABLE `sales_bestsellers_aggregated_daily`;
TRUNCATE TABLE `sales_bestsellers_aggregated_monthly`;
TRUNCATE TABLE `sales_bestsellers_aggregated_yearly`;
TRUNCATE TABLE `sales_creditmemo`;
TRUNCATE TABLE `sales_creditmemo_comment`;
TRUNCATE TABLE `sales_creditmemo_grid`;
TRUNCATE TABLE `sales_creditmemo_item`;
TRUNCATE TABLE `sales_invoice`;
TRUNCATE TABLE `sales_invoiced_aggregated`;
TRUNCATE TABLE `sales_invoiced_aggregated_order`;
TRUNCATE TABLE `sales_invoice_comment`;
TRUNCATE TABLE `sales_invoice_grid`;
TRUNCATE TABLE `sales_invoice_item`;
TRUNCATE TABLE `sales_order`;
TRUNCATE TABLE `sales_order_address`;
TRUNCATE TABLE `sales_order_aggregated_created`;
TRUNCATE TABLE `sales_order_aggregated_updated`;
TRUNCATE TABLE `sales_order_grid`;
TRUNCATE TABLE `sales_order_item`;
TRUNCATE TABLE `sales_order_payment`;
TRUNCATE TABLE `sales_order_status_history`;
TRUNCATE TABLE `sales_order_tax`;
TRUNCATE TABLE `sales_order_tax_item`;
TRUNCATE TABLE `sales_payment_transaction`;
TRUNCATE TABLE `sales_refunded_aggregated`;
TRUNCATE TABLE `sales_refunded_aggregated_order`;
TRUNCATE TABLE `sales_shipment`;
TRUNCATE TABLE `sales_shipment_comment`;
TRUNCATE TABLE `sales_shipment_grid`;
TRUNCATE TABLE `sales_shipment_item`;
TRUNCATE TABLE `sales_shipment_track`;
TRUNCATE TABLE `sales_shipping_aggregated`;
TRUNCATE TABLE `sales_shipping_aggregated_order`;
TRUNCATE TABLE `tax_order_aggregated_created`;
TRUNCATE TABLE `tax_order_aggregated_updated`;
TRUNCATE TABLE `downloadable_link_purchased`;
TRUNCATE TABLE `downloadable_link_purchased_item`;
TRUNCATE TABLE `sendfriend_log`;

-- Reset Auto Increments
ALTER TABLE `sales_order` AUTO_INCREMENT=1;
ALTER TABLE `sendfriend_log` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS = 1;

2. How to Clear All Customer Data

To remove all registered customers, addresses, and wishlists, use the following queries. This will effectively reset your customer base to zero.

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE `customer_address_entity`;
TRUNCATE TABLE `customer_address_entity_datetime`;
TRUNCATE TABLE `customer_address_entity_decimal`;
TRUNCATE TABLE `customer_address_entity_int`;
TRUNCATE TABLE `customer_address_entity_text`;
TRUNCATE TABLE `customer_address_entity_varchar`;
TRUNCATE TABLE `customer_entity`;
TRUNCATE TABLE `customer_entity_datetime`;
TRUNCATE TABLE `customer_entity_decimal`;
TRUNCATE TABLE `customer_entity_int`;
TRUNCATE TABLE `customer_entity_text`;
TRUNCATE TABLE `customer_entity_varchar`;
TRUNCATE TABLE `customer_grid_flat`;
TRUNCATE TABLE `customer_log`;
TRUNCATE TABLE `customer_visitor`;
TRUNCATE TABLE `persistent_session`;
TRUNCATE TABLE `wishlist`;
TRUNCATE TABLE `wishlist_item`;
TRUNCATE TABLE `wishlist_item_option`;

-- Reset Auto Increments
ALTER TABLE `customer_entity` AUTO_INCREMENT=1;
ALTER TABLE `wishlist` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS = 1;

3. How to Clear Products and Inventory

Clearing the catalog is more complex because it involves EAV (Entity-Attribute-Value) tables and product link attributes. A critical note: do not leave catalog_product_link_attribute empty, or you will encounter issues when creating grouped products later.

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE `catalog_product_entity`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value_to_entity`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `catalog_category_product_index`;

-- Re-insert essential link types
INSERT INTO `catalog_product_link_type`(`link_type_id`,`code`) VALUES (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');
INSERT INTO `catalog_product_link_attribute`(`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) VALUES (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');

-- Reset Auto Increment
ALTER TABLE `catalog_product_entity` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS = 1;

4. Resetting Categories and Root Catalog

If you truncate the category tables, you must re-insert the "Root Catalog" and "Default Category." Without these two records, the Magento Admin Panel will fail to load the category tree.

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE `catalog_category_entity`;
TRUNCATE TABLE `catalog_category_entity_datetime`;
TRUNCATE TABLE `catalog_category_entity_decimal`;
TRUNCATE TABLE `catalog_category_entity_int`;
TRUNCATE TABLE `catalog_category_entity_text`;
TRUNCATE TABLE `catalog_category_entity_varchar`;

-- Restore Root and Default Categories
INSERT INTO `catalog_category_entity` (`entity_id`, `attribute_set_id`, `parent_id`, `path`, `position`, `level`, `children_count`) VALUES 
(1, 0, 0, '1', 0, 0, 1), 
(2, 3, 1, '1/2', 1, 1, 0);

INSERT INTO `catalog_category_entity_varchar` (`attribute_id`, `store_id`, `entity_id`, `value`) VALUES 
(45, 0, 1, 'Root Catalog'), 
(45, 0, 2, 'Default Category');

-- Clear category URL rewrites to avoid duplicate entry errors
DELETE FROM `url_rewrite` WHERE `entity_type` = 'category';
DELETE FROM `url_rewrite` WHERE `entity_type` = 'product';

SET FOREIGN_KEY_CHECKS = 1;

5. Clearing Reviews and Ratings

To remove all product reviews and their associated ratings, run the following:

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE `review`;
TRUNCATE TABLE `review_detail`;
TRUNCATE TABLE `review_entity_summary`;
TRUNCATE TABLE `review_store`;
TRUNCATE TABLE `rating_option_vote`;
TRUNCATE TABLE `rating_option_vote_aggregated`;

SET FOREIGN_KEY_CHECKS = 1;

Frequently Asked Questions

Why do I need to set FOREIGN_KEY_CHECKS to 0?

Magento uses foreign key constraints to maintain data relationships. For example, an order cannot exist without a quote. When you truncate tables, these constraints prevent the deletion. Setting the check to 0 allows you to clear the tables; setting it back to 1 restores the database protection.

Why is resetting AUTO_INCREMENT important?

If you truncate your order tables but don't reset the auto-increment, your next order might start at a high number (e.g., #10000052) instead of #00000001. Resetting ensures your IDs start from the beginning, which is standard for a fresh production launch.

Will this remove my CMS pages or configuration?

No. These queries specifically target transactional data (orders), customer data, and catalog data. Your store configuration, CMS pages, blocks, and admin users will remain untouched.

Wrapping Up

Truncating tables is the fastest way to wipe test data from a Magento 2 store. By following the categorized approach above, you can selectively clear orders, customers, or products while maintaining the structural integrity of your database.

After running these queries, always remember to: 1. Re-index your store: php bin/magento indexer:reindex 2. Flush the cache: php bin/magento cache:flush 3. Verify the Admin Panel to ensure your Root Category is visible.