I use the following 2 files to clear transactions with SQL Express, keeping all menus, products, and setups. It will not remove Entities or any accounts linked to an Entity like Customer Accounts - but will wipe all account transactions.
Please note that this is destructive and can not be reversed once run !!! The script will backup your database, but only if the SambaPOS SQL Instance is running as a Local System account.
Always backup your database first.
DELETE V4 DATA.bat
@echo off
REM Set your Server and Database names here.
set DATABASENAME=SambaPOS45
set SERVERNAME=localhost\SAMBAPOS
set DATESTAMP=%DATE:~4%
set BACKUPFILENAME=%CD%%DATABASENAME%-%DATESTAMP%.bak
pause
echo – BACKUP DATABASE –
sqlcmd -E -S %SERVERNAME% -d master -Q “BACKUP DATABASE [%DATABASENAME%] TO DISK = N’%BACKUPFILENAME%’ WITH INIT , NOUNLOAD , NAME = N’%DATABASENAME% backup’, NOSKIP , STATS = 10, NOFORMAT”
echo – CLEAR DATABASE –
SQLCMD -S %SERVERNAME% -d %DATABASENAME% -i V4_dtrans.sql
pause
V4_dtrans.sql
DELETE FROM [TicketEntities]
GO
DELETE FROM [Tickets]
GO
DELETE FROM [AccountTransactionDocuments]
GO
DELETE FROM [AccountTransactions]
GO
DELETE FROM [AccountTransactionValues]
GO
DELETE FROM [Calculations]
GO
DELETE FROM [CostItems]
GO
DELETE FROM [InventoryTransactionDocuments]
GO
DELETE FROM [InventoryTransactions]
GO
DELETE FROM [Orders]
GO
DELETE FROM [PaidItems]
GO
DELETE FROM [PeriodicConsumptionItems]
GO
DELETE FROM [PeriodicConsumptions]
GO
DELETE FROM [ProductTimerValues]
GO
DELETE FROM [Payments]
GO
DELETE FROM [WarehouseConsumptions]
GO
DELETE FROM [WorkPeriods]
GO
UPDATE [Numerators] SET Number = 0
GO
UPDATE [EntityStateValues] SET EntityStates = ‘[{“S”:“Available”,“SN”:“Status”}]’ Where EntityStates like ‘%Status%’
GO