Jim2 Custom Database Clone | Scheduled Production to Test Database

Do this at your own risk. This is not supported and does come with risks. If you do not do this correctly you could corrupt your database or accidentally send emails with invoices to people that are not correct. If you implement this YOU ARE RESPONSIBLE FOR WHAT HAPPENS!! So make sure you understand what you are doing and do not blame me if you break something!

So I wanted to always have a copy of the ERP that we use (Jim2 by Happen Buinses) so then if I wanted to test out something I can just open the yesterday database & then I can use it.

THIS IS NOT SUPPORTED. YOU ARE RESPONSIBLE FOR WHAT HAPPENS!! So make sure you understand what you are doing

Now there is an inbuilt feature of Jim2 where you have a training database but the issue with this is everytime you want to do some testing, you need to log into the server and restore the backup from last night to the Training Database, this is quite cumbersom when you are doing a lot of testing.

So what I did first was created a new database which is quite easy, I just followed the instructions in the help article. Then I restored a backup to it, now this is when I had the following issues:

1. Emails are still active – So the database that was just restored starts trying to automatically download any connected email accounts and also if you do any test invoices it would send them because emails are still enabled. This could be extreamly bad if someone was testing things and they actually went to the clients.
   When you restore the training database, the restore proceedure disables all of the emails so I needed to figure out how.
2. The colour of the screen was the same so you cannot easily tell which database you are in
3. No Jes services come across so you cannot do any automatic meter read testing

I knew I can just add another Jes service, so thats no worries but I did have to figure out how Jim2 handles the emailing and the colour.

I did some digging through the database & couldn’t find anything that stood out. Then I thought about running the SQL Server Profiler and changing the settings and seeing if I can see any SQL commands that will point me in the right direction.

And bingo – I found them.

Turns out there is a heap of options in the table JimOptions.

So I ran a select command on the JimOptions table and orderd it by the Opt field.

SELECT * FROM [JimOptions] ORDER BY Opt

And I found the EmailSMTP & EmailProcessing in that table, when I compared it would change when I change the settings. Then I had a thought. What if I just copy this whole table out of the training, as the training Database has this changed properly (and who knows what else it does when you do it).

I first tried using the copy database wizard, but it was annoying and ended up using a backup & restore instead.

So I just ran the following commands.

TRUNCATE TABLE [Jim_Yesterday].[dbo].[JimOptions];
INSERT INTO [Jim_Yesterday].[dbo].[JimOptions]
SELECT *
FROM [Jim_Training].[dbo].[JimOptions];

Then I tested it – It had disabled the emails but now the colour of the database was the same colour as the training. I want it to stand out & be a different colour then the live one.

Anyway so I just ran through the same process. Turns out the colour is changed by the values in General_SkinName there is some changes in both Columns for Value1 and Value2

UPDATE [Jim_Yesterday].[dbo].[JimOptions] set Value1 = '1' where Opt = 'General_SkinName';
UPDATE [Jim_Yesterday].[dbo].[JimOptions] set Value2 = '2' where Opt = 'General_SkinName';

These two values make it an orange colour.

So then I had all of my testing done and I was ready to script it out.

The Actual Scripts

So this is my SQL script, which I have saved as CopyJim2Yesterday.sql to make it work, I had to change the DB to Single User mode and back out which is in the script.

BACKUP DATABASE [Jim_Production]
TO DISK = 'c:\temp\Jim_Production.bak';

BACKUP DATABASE [Jim_Production#Doc]
TO DISK = 'c:\temp\Jim_ProductionDoc.bak';

ALTER DATABASE [Jim_Yesterday] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
RESTORE DATABASE [Jim_Yesterday] FROM DISK = 'C:\Temp\Jim_Production.bak'
go
ALTER DATABASE [Jim_Yesterday#Doc] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
RESTORE DATABASE [Jim_Yesterday#Doc] FROM DISK = 'C:\Temp\Jim_ProductionDoc.bak'
go

ALTER DATABASE [Jim_Yesterday#Doc] SET MULTI_USER;
ALTER DATABASE [Jim_Production#Doc] SET MULTI_USER;
go

/* This changes the table with the options to make it the same as the training DB. To disable emailing */
TRUNCATE TABLE [Jim_Yesterday].[dbo].[JimOptions];
INSERT INTO [Jim_Yesterday].[dbo].[JimOptions]
SELECT *
FROM [Jim_Training].[dbo].[JimOptions];

UPDATE [Jim_Yesterday].[dbo].[JimOptions] set Value1 = '1' where Opt = 'General_SkinName';
UPDATE [Jim_Yesterday].[dbo].[JimOptions] set Value2 = '2' where Opt = 'General_SkinName';

Then I have a daily scheduled task that runs on the server just in a batch file. This needs to run as a user that has access to the database which I have running on a dedicated service account.

net stop Jes$Jim_Yesterday

sqlcmd.exe -S SVRNAME -i c:\Scripts\CopyJim2Yesterday.sql -o c:\Scripts\CopyJim2YesterdayLog.txt

net Start Jes$Jim_Yesterday

del C:\Temp\Jim_Production.bak
del C:\Temp\Jim_ProductionDoc.bak

Now, that should give you enough to go on. I’m sure this same strategy will work for any other ERP that uses an SQL backend as well. Just to note this is on Jim2 4.3 – This may not work on other versions & also it is not supported by the software developers, DO THIS AT YOUR OWN RISK!!