A client of ours went in to test products, orders, invoices, etc. to get the feel for the overall "purchase order to shipment
'process from start to finish. After creating many test users, orders, etc. - they now desire to have everything reset back to "0" (zero).
Not only that, but they also want to separate the prefixes for the following items just for ease of dissimilarity when using the Magento ADMIN tools:
+ Orders (set prefix to begin with "1")
+ Invoices (set prefix to begin with "2")
+ Shipments (set prefix to begin with "3")
+ Credit Memo’s (set prefix to begin with "4")
Instructions:
1. Find "YOUR_DB_NAME" down below and change to match your database name (though leave the ticks ")
2. Copy and paste code into SQL manager (PhpMyAdmin or similar) and then run the query
3. Feel free to Create test order, invoice, shipment, and credit memo’s via the ADMIN (easier than going through order process on frontend)
4. Once you like the changes you see, go back and run the same SQL query
...the following will now be in effect:
+ All data pertaining to orders and search terms (Dashboard) are reset to 0 (zero)
+ Order number prefix with 0, Invoice number prefix with 2, Shipment number prefix with 3, Credit Memo number prefix with 4
Please feel free to copy and paste into your PhpMyAdmin
-- 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`; 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;
-- Reset all ID counters
TRUNCATE `eav_entity_store`; ALTER TABLE `eav_entity_store` AUTO_INCREMENT=1; SET FOREIGN_KEY_CHECKS=1;
-- set appropriate prefixes for orders, invoices, shipments, credit memos
INSERT INTO `YOUR_DB_NAME`.`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`='4' and `store_id`='1'; update `eav_entity_store` set `increment_last_id`= '000000000' where `entity_type_id`='4' and `store_id`='1'; INSERT INTO `YOUR_DB_NAME`.`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`='18' and `store_id`='1'; update `eav_entity_store` set `increment_last_id`= '000000000' where `entity_type_id`='18' and `store_id`='1'; INSERT INTO `YOUR_DB_NAME`.`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`='24' and `store_id`='1'; update `eav_entity_store` set `increment_last_id`= '000000000' where `entity_type_id`='24' and `store_id`='1'; INSERT INTO `YOUR_DB_NAME`.`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`='28' and `store_id`='1'; update `eav_entity_store` set `increment_last_id`= '000000000' where `entity_type_id`='28' and `store_id`='1';
Source : http://www.eliasinteractive.com/