Cleanly Delete all Orders, Sales & Customer Data in Magento

Prior to launching a new eCommerce website, at development phase, you’ll always have to carry out testing and this will require creating test orders and customers to ensure that all processes function correctly and as you would expect.

This can often end up leaving a load of test data in the system which you won’t be wanting once the site is launched. Magento is notorious for inserting data in multiple database tables so we’ve provided the SQL query below that will cleanly delete all this data from your Magento database (and reset the increment counters for sales, invoices, customers and shipping).

This has been tested and confirmed working on Magento versions from 1.4.0.1 to 1.9.1.1.

There is actually an extension that has surfaced now which allows you to erase orders from the Magento back office. Presumably this cleans up as necessary in the database when you delete an order but please be warned, we have not used or tested this extension yet so use at your own risk (backup your database before installing/using).

If you’ve used this extension, please leave a comment with your experience below.

This post is also available in: German, Spanish

Geoff Jackson

Geoff Jackson (more commonly known by his online pseudonym zigojacko) is the founder of Design Haven and The Clubnet Group which hosts numerous agencies including the most widely recognised of them, Clubnet Digital, a full service digital marketing and creative design/development agency. He also has a personal blog but it doesn't get updated as much as he'd like.

Sponsored Links...

Subscribe to Design Haven UK

Enter your email address to subscribe and receive our new posts by email.

25 Responses

  1. Nick says:

    Great list, thank you! I also wanted the search terms cleared out from the dashboard, so I ran the following in addition:

    TRUNCATE catalogsearch_query;
    TRUNCATE catalogsearch_fulltext;
    TRUNCATE catalogsearch_result;
    ALTER TABLE catalogsearch_query AUTO_INCREMENT=1;
    ALTER TABLE catalogsearch_fulltext AUTO_INCREMENT=1;
    ALTER TABLE catalogsearch_result AUTO_INCREMENT=1;

    TRUNCATE TABLE sales_bestsellers_aggregated_daily;
    TRUNCATE TABLE sales_bestsellers_aggregated_monthly;
    TRUNCATE TABLE sales_bestsellers_aggregated_yearly;
    ALTER TABLE sales_bestsellers_aggregated_daily AUTO_INCREMENT=1;
    ALTER TABLE sales_bestsellers_aggregated_monthly AUTO_INCREMENT=1;
    ALTER TABLE sales_bestsellers_aggregated_yearly AUTO_INCREMENT=1;

    • Thanks for the comment Nick, yes, you’re suggestion code would be useful to clear out any of the data in the search terms and bestsellers, thanks for providing the code for this.

  2. Tony Jackson says:

    Many thanks for this – works a treat, this has solved a real headache for us.

  3. n says:

    Thank you for the great work.
    One question, I run this SQL script plus the comment from Nick in Magento 1.9.1.0 and it worked fine but then in few minutes later slowly started to update the “Search Terms” and few “Products in carts” that was from before.When I first installed the theme to Magento I had to import some files including test orders and etc from PhpMyAdmin.

    • Hey,

      Sounds like you may have some SQL script that re-ran from when you imported the dummy data initially which re-imported the search terms data back in to your database again?

      Hard to say to be honest without knowing your exact set up, configuration etc.

      Perhaps post the question specifying all details to Magento SE and there’ll be someone that will be able to discuss further and shed some light on this for you most likely.

      Thanks for stopping by and taking the time to comment with your results post actioning this query.

  4. Diego Castro says:

    Thank you, worked like a charm!

  5. Kátia says:

    Can I use this to Magento 1.9.1.0 or I need to run another command?

  6. Ink Factory says:

    Thanks for this – we’ve used this in our work flow to quickly deploy a staging server which exactly mirrors our live site, minus all the customer data. Works a treat on Magento 1.7.0.2.

    Cheers, Tim.

    • Good stuff Tim, glad you found it useful.

      • Ink Factory says:

        Also, you might want to include this, which clears the newsletter subscribers too:

        SET FOREIGN_KEY_CHECKS=0;

        TRUNCATE newsletter_problem;
        TRUNCATE newsletter_queue;
        TRUNCATE newsletter_queue_link;
        TRUNCATE newsletter_queue_store_link;
        TRUNCATE newsletter_subscriber;
        TRUNCATE newsletter_template;

        ALTER TABLE newsletter_problem AUTO_INCREMENT=1;
        ALTER TABLE newsletter_queue AUTO_INCREMENT=1;
        ALTER TABLE newsletter_queue_link AUTO_INCREMENT=1;
        ALTER TABLE newsletter_queue_store_link AUTO_INCREMENT=1;
        ALTER TABLE newsletter_subscriber AUTO_INCREMENT=1;
        ALTER TABLE newsletter_template AUTO_INCREMENT=1;

        SET FOREIGN_KEY_CHECKS=1;

        • Thanks for the contribution Tim, indeed, this could also prove useful for clearing our test/dummy data. Will see if we can get the post updated with this too. Thanks.

  7. Maria says:

    Thank you sharing your experience on how to tackle problem in database. Those who do not have any idea about running that queries, can remove orders free downloading Store Manager for Magento version

    • Thank you for stopping by and leaving a comment Maria but I cannot recommend Store Manager for Magento in the slightest. It is buggy, not very user friendly, causes countless issues and is incredibly overpriced for the hassle that the software causes.

      We’ve numerous clients that can vouch for that feedback.

      It’s a shame that in the 3+ years I know of that Store Manager has been available on the market, it has not improved and rectified these bugs/glitches.

      • Maria says:

        Hello Geoff. Even Magento itself has bugs. The question is how quickly they are solved and what the features are included. We are always trying to improve Store Manager for Magento and eliminate the issues occurring. We are interested in improving the app on the basis your feedback, so if you remember what issues you or your clients encountered, we can provide you free copy for the pointing us in the right direction. If you are interested, e-mail us to contact@mag-manager.com

  8. K Khan says:

    it’s not working on 1.9.2.2

    please help! 🙂

  9. hykont says:

    Jackson you are the best, and every other person that contributed so far to this… You really safe me with the script..much love

  10. James says:

    Many thanks !

    But I also want to share an available Magento extension to help delete orders quickly and easily

    http://bsscommerce.com/magento-delete-order-extension.html

    Hope it is also useful!

  11. Jonathan says:

    Thank for great infor!
    For amateur user who not familiar with coding process, I suggest a great extension that work well on both Magento 1 and 2. Managing orders in backend becomes easier than ever with this helpful tool!

    For Magento 1: http://bsscommerce.com/magento-delete-order-extension.html
    For Magento 2: http://bsscommerce.com/delete-order-for-magento-2.html

    • Thanks for suggesting these extensions Jonathan. I have no experience of using these extensions so cannot vouch for them or comment.

      For the benefit of other visitors though, the recommended extensions by Jonathan only deletes orders whereas the above SQL does a lot more. As always, approach any third party extensions with caution and take full backups as always before first use.

  1. May 22, 2016

    […] may need to clear out all customer and order information. Kudos to Geoff Jackson for this solution https://www.designhaven.co.uk/2014/08/cleanly-delete-orders-sales-customer-data-magento/ . I’ve used this a few times now and want to include it here for easy […]

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: