How to take a data snapshot
From MTHWiki
Sometimes it is necessary to temporary move the data into text files, for example when migrating from an older version of My Money into a new one, or for backup purposes.
It is relatively easy to do using Derby built-in stored procedure SYSCS_UTIL.SYSCS_EXPORT_TABLE
The full syntax of this procedure could be found at Derby's Reference Guide Online, for our purposes it is sufficient to say that we will be exporting tables into text files were values are comma separated
Please prepare a file called export_all.sql and save it into c:\export directory
The contents of this file should be similar to this:
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('MTHADMIN','MMY_ACCOUNT', 'c:/export/MMY_ACCOUNT.txt', null, null, null);
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('MTHADMIN','MMY_ADDRESS', 'c:/export/MMY_ADDRESS.txt', null, null, null);
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('MTHADMIN','MMY_CATEGORY', 'c:/export/MMY_CATEGORY.txt', null, null, null);
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('MTHADMIN','MMY_DBVERSION', 'c:/export/MMY_DBVERSION.txt', null, null, null);
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('MTHADMIN','MMY_DOUBLEENTRY', 'c:/export/MMY_DOUBLEENTRY.txt', null, null, null);
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('MTHADMIN','MMY_FI', 'c:/export/MMY_FI.txt', null, null, null);
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('MTHADMIN','MMY_MEMORIZEDTRANSACTION', 'c:/export/MMY_MEMORIZEDTRANSACTION.txt', null, null, null);
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('MTHADMIN','MMY_PAYEE', 'c:/export/MMY_PAYEE.txt', null, null, null);
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('MTHADMIN','MMY_SPLIT', 'c:/export/MMY_SPLIT.txt', null, null, null);
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('MTHADMIN','MMY_TRANSACTION', 'c:/export/MMY_TRANSACTION.txt', null, null, null);
As you can see this files contains commands to take content of My Money tables and save them into text files located in c:\export. For example, MMY_CATEGORY table will be saved into c:\export\MMY_CATEGORY.txt
Start My Moneys console and type runsqlbatch("C:/export/export_all.sql"); at the prompt. This will run the export_all.sql and you will end up having the data snapshot of all relevant tables.
To bring this data back into My Money you will need to import it, you may use a import_all.sql commands similar to the following
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'MMY_MEMORIZEDTRANSACTION', 'c:/export/MMY_MEMORIZEDTRANSACTION.txt', null, null, null,1);
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'MMY_DBVERSION', 'c:/export/MMY_DBVERSION.txt', null, null, null,1);
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'MMY_FI', 'c:/export/MMY_FI.txt', null, null, null,1);
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'MMY_ADDRESS', 'c:/export/MMY_ADDRESS.txt', null, null, null,1);
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'MMY_PAYEE', 'c:/export/MMY_PAYEE.txt', null, null, null,1);
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'MMY_CATEGORY', 'c:/export/MMY_CATEGORY.txt', null, null, null,1);
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'MMY_ACCOUNT', 'c:/export/MMY_ACCOUNT.txt', null, null, null,1);
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'MMY_TRANSACTION', 'c:/export/MMY_TRANSACTION.txt', null, null, null,1);
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'MMY_SPLIT', 'c:/export/MMY_SPLIT.txt', null, null, null,1);
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'MMY_DOUBLEENTRY', 'c:/export/MMY_DOUBLEENTRY.txt', null, null, null,1);
ALTER TABLE tauto ALTER COLUMN i RESTART WITH 6
Please note that we recommend importing this data into a brand new database, in other words create a new database in My Money first and then import snapshot data. The reason for this is because you may have data collision issues if you attempt to import saved data on top of existing database.
