**WARNING: These queries will PERMANENTLY delete the affected data. There is no way to recover this unless you have a separate backup (and that won't be easy). This should only be carried out by a skilled implementer and SQL practitioner.**
This document describes the SQLs used to:
- PURGE all Orders, Estimates, POs, RDs, Bills
- PURGE all Customers & Contacts
- PURGE all Customized Report Settings
- PURGE all Addresses and Phone Numbers for Companies, Contact, Orders
- PURGE all Activities (Journals) including Payments, Contact Activities, etc.
- PURGE all GL Entries
- PURGE all Time Cards
- PURGE all Inventory Entries
- RESET all products to NOT track inventory
- RESET the next order, estimate, and estimate numbers
- RESET all parts to not use calendars
**WARNING: These queries will PERMANENTLY delete the affected data. There is no way to recover this unless you have a separate backup (and that won't be easy). This should only be carried out by a skilled implementer and SQL practitioner.**
SQL
**WARNING: These queries will PERMANENTLY delete the affected data. There is no way to recover this unless you have a separate backup (and that won't be easy). This should only be carried out by a skilled implementer and SQL practitioner.**
-- Replace this name with the database you want to purge USE [StoreData-ToEmpty] -- DELETE ALL Report Settings DELETE FROM REPORTMENUITEM WHERE ID >= 1000 ; -- DELETE ALL CUSTOMERS DELETE FROM ACCOUNT WHERE ID > 1001 -- Leave 10 for Walk-In and 1001 for Test Company ; -- DELETE ALL CONTACTS DELETE FROM ACCOUNTCONTACT WHERE ID >= 1 AND AccountID NOT IN (SELECT ID FROM Account) ; -- DELETE ALL PHONE NUMBERS FOR ALL COMPANIES DELETE FROM PHONENUMBER WHERE PARENTCLASSTYPEID = 2000 AND ParentID NOT IN (SELECT ID FROM Account) ; -- DELETE ALL PHONE NUMBERS FOR ALL CONTACTS DELETE FROM PHONENUMBER WHERE PARENTCLASSTYPEID = 3000 AND ParentID NOT IN (SELECT ID FROM AccountContact) ; -- DELETE ALL COMPANY ADDRESS LINKS DELETE FROM ADDRESSLINK WHERE PARENTCLASSTYPEID = 2000 AND ParentID NOT IN (SELECT ID FROM Account) ; -- DELETE ALL CONTACT ADDRESS LINKS DELETE FROM ADDRESSLINK WHERE PARENTCLASSTYPEID = 3000 AND ParentID NOT IN (SELECT ID FROM AccountContact) ; -- DELETE ALL COMPANY UDFS DELETE FROM AccountUserField WHERE ID NOT IN (SELECT ID FROM Account) ; -- DELETE ALL CONTACT UDFS DELETE FROM AccountContactUserField WHERE ID NOT IN (SELECT ID FROM AccountContact) ; -- Removes login/lo;ut entries. DELETE FROM Journal WHERE JournalActivityType IN (30,31) ; -- Removes part usage entries DELETE FROM Journal WHERE JournalActivityType = 17 ; -- Removes closeouts DELETE FROM Journal WHERE JournalActivityType = 13 ; -- Removes CloseOuts DELETE FROM CLOSEOUT WHERE ID > 0 ; -- Removes company creation entries DELETE FROM Journal WHERE JournalActivityType = 6 ; -- Removes report activities DELETE FROM Journal WHERE JournalActivityType = 27 ; -- Removes timecards DELETE FROM Journal WHERE JournalActivityType = 1 ; DELETE FROM TimeCard WHERE ID > 0 ; -- Removes Activities DELETE FROM ContactActivity WHERE ID > 0 ; -- Removes Activities from Journal DELETE FROM Journal WHERE ID > 0 AND JournalActivityType = 10 ; -- Removes CalendarLinks DELETE FROM CalendarLink WHERE ID > 0 ; -- Removes Payments DELETE FROM Payment WHERE ID > 0 ; -- Removes CloseOuts DELETE FROM CLOSEOUT WHERE ID >= 0 ; -- Removes timecards DELETE FROM Journal WHERE JournalActivityType = 1 ; DELETE FROM TimeCard WHERE ID >= 0 ; -- Removes CalendarLinks DELETE FROM CalendarLink WHERE ID >= 0 ; -- Removes Payments DELETE FROM Payment WHERE ID >= 0 ; -- Removes PartUsage Cards DELETE FROM PartUsageCard WHERE ID >= 1000 ; -- Removes Activities for Payments from Journal DELETE FROM Journal WHERE ID > 0 AND JournalActivityType = 2 ; -- Removes Activities for Backups from Journal DELETE FROM Journal WHERE ID > 0 AND JournalActivityType IN (14,24) ; -- Removes Activities for Imports from Journal DELETE FROM Journal WHERE ID > 0 AND JournalActivityType IN (11,21) ; -- Removes PartUsage Cards DELETE FROM PartUsageCard WHERE ID > 0 ; -- Removes Activities for Macros from Journal DELETE FROM Journal WHERE ID > 0 AND JournalActivityType = 16 ; -- Removes Activities for Macro Templates from Journal DELETE FROM Journal WHERE ID > 0 AND JournalActivityType = 42 ; -- Removes Activities for Emails from Journal DELETE FROM Journal WHERE ID > 0 AND JournalActivityType = 42 ; -- Removes Activities for Notes from Journal DELETE FROM Journal WHERE ID > 0 AND JournalActivityType = 15 ; -- Removes any inventory log entries DELETE FROM InventoryLog WHERE ID >= 0 ; -- Removes any inventory entries DELETE FROM Inventory WHERE ID >= 0 ; -- Sets all parts to non-inventory items UPDATE Part SET TrackInventory = 0 WHERE ID >= 0 ; DELETE FROM Payroll WHERE ID >= 1000 ; DELETE FROM PayrollPaycheck WHERE ID >= 1000 ; DELETE FROM PayrollPaycheckPayItem WHERE ID >= 1000 ; DELETE FROM PayrollPTO WHERE ID >= 1000 ; DELETE FROM Payroll WHERE ID >= 1000 ; -- Delete the transheader record first then run the sql statements below DELETE FROM TransHeader WHERE ID >= 0 ; DELETE FROM Transdetail WHERE ID >= 0 ; DELETE FROM Transvariation WHERE ID >= 0 ; DELETE FROM TransHeaderUserField WHERE ID >= 0 ; DELETE FROM TransDetailGraphic WHERE ID >= 0 ; DELETE FROM TransDetailParam WHERE ID >= 0 ; DELETE FROM TransMod WHERE ID >= 0 ; DELETE FROM TransPart WHERE ID >= 0 ; DELETE FROM TransTax WHERE ID >= 0 ; DELETE FROM VendorTransDetail WHERE ID >= 0 ; DELETE FROM Ledger WHERE ID >= 0 ; DELETE FROM Journal WHERE TransactionID NOT IN (SELECT id FROM transheader) AND ID >= 0 ; DELETE FROM Payment WHERE ID >= 0 ; -- Delete Part Calendars DELETE FROM PricingElement WHERE classtypeid = 12075 ; -- Reset Parts to not use Calendars UPDATE Part SET ShowOnProductionSchedule = 0, InstancesAllowed = 0 ; -- Delete all Addresses that are not associated with anything. DELETE FROM ADDRESS WHERE ID > 0 AND ID NOT IN ( SELECT BillingAddressID FROM ( SELECT BillingAddressID, CAST('Account BillingAddressID' AS NVarChar(50)) AS SOURCE FROM Account UNION SELECT ShippingAddressID, CAST('Account ShippingAddressID' AS NVarChar(50)) AS SOURCE FROM Account UNION SELECT AddressID, CAST('GLAccount AddressID' AS NVarChar(50)) AS SOURCE FROM GLAccount UNION SELECT BillingAddressID, CAST('AccountContact BillingAddressID' AS NVarChar(50)) AS SOURCE FROM AccountContact UNION SELECT ShippingAddressID, CAST('AccountContact ShippingAddressID' AS NVarChar(50)) AS SOURCE FROM AccountContact UNION SELECT PaymentAddressID, CAST('Contact PaymentAddressID' AS NVarChar(50)) AS SOURCE FROM AccountContact UNION SELECT AddressID, CAST('AddressLink AddressID' AS NVarChar(50)) AS SOURCE FROM AddressLink UNION SELECT AddressID, CAST('Employee AddressID' AS NVarChar(50)) AS SOURCE FROM Employee UNION SELECT MailAddressID, CAST('Employee AddressID' AS NVarChar(50)) AS SOURCE FROM Employee UNION SELECT AddressID, CAST('EmployeeGroup AddressID' AS NVarChar(50)) AS SOURCE FROM EmployeeGroup UNION SELECT AddressID, CAST('Store AddressID' AS NVarChar(50)) AS SOURCE FROM Store UNION SELECT BillingAddressID, CAST('Store BillingAddressID' AS NVarChar(50)) AS SOURCE FROM Store UNION SELECT PickUpAddressID, CAST('Store PickUpAddressID' AS NVarChar(50)) AS SOURCE FROM Store UNION SELECT ShippingAddressID, CAST('Store ShippingAddressID' AS NVarChar(50)) AS SOURCE FROM Store UNION SELECT InvoiceAddressID, CAST('TransHeader InvoiceAddressID' AS NVarChar(50)) AS SOURCE FROM TransHeader UNION SELECT ShippingAddressID, CAST('TransHeader ShippingAddressID' AS NVarChar(50)) AS SOURCE FROM TransHeader UNION SELECT ShippingAddressID, CAST('TransDetail ShippingAddressID' AS NVarChar(50)) AS SOURCE FROM TransDetail ) WorkingTable WHERE BillingAddressID IS NOT NULL AND BillingAddressID > 0 ) ; -- Delete all Addresslink records that are not associated with an Address DELETE FROM AddressLink WHERE ID > 0 AND AddressID NOT IN (SELECT ID FROM Address) ; -- Set Next Company Record UPDATE SEQUENCE SET NextNumber = 200 WHERE ClassTypeID = 2000 ; -- Set Next Order Record UPDATE SEQUENCE SET NextNumber = 1000 WHERE ClassTypeID = 10001 ; -- Set Next Estimate Record UPDATE SEQUENCE SET NextNumber = 500 WHERE ClassTypeID = 10002 ;
**WARNING: These queries will PERMANENTLY delete the affected data. There is no way to recover this unless you have a separate backup (and that won't be easy). This should only be carried out by a skilled implementer and SQL practitioner.**
Version
- Updated 2016-07.
You could leave a comment if you were logged in.