Takeaway Orders

Version 4 V4 Question
8

Instructions to backup and wipe transactional data in a Compact SQL 4.0 Database.

1… Find where your Compact SQL database file is. It could be in any of the following

C:\Users\{user}\Documents\SambaPOS4
C:\ProgramData\SambaPOS\SambaPOS4
C:\Users\{user}\AppData\Roaming\SambaPOS\SambaPOS4

or do a search for *.sdf files.
The directory where the sdf file is located is where all the other files will need to be placed.

My Compact SQL Database for this example is test.sdf and the file is in C:\Users\John\Documents\SambaPOS4.

The following files will be placed in C:\Users\John\Documents\SambaPOS4.

2… Download SqlCeCmd40.exe from http://sqlcecmd.codeplex.com/downloads/get/635167

3… Create a file called Clear_CE_Data.bat and place the following in it

@echo off
REM Set your Database name here.
set DATABASENAME=test.sdf

set DATESTAMP=%DATE:/=-%_%TIME::=-%
set DATESTAMP=%DATESTAMP: =_%
set BACKUPFILENAME=%CD%\%DATABASENAME%-%DATESTAMP%.bak
pause
echo -- BACKUP DATABASE --
copy %DATABASENAME% %BACKUPFILENAME%
echo -- CLEAR DATABASE --
SqlCeCmd40 -d "Data Source=%DATABASENAME%" -i V4_dtrans.sql
pause

4… Create a file called V4_dtrans.sql and place the following in it

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

So in C:\Users\John\Documents\SambaPOS4 I have 3 files

  • SqlCeCmd40.exe
  • Clear_CE_Data.bat
  • V4_dtrans.sql
    Plus my database file
  • test.sdf

5… To backup and delete transactional data from your database, you only need to run Clear_CE_Data.bat

** UPDATE ** - CLear_CE_Data.bat file has been updated to correct date & time stamp.

Zero all accounts