SQL Migration and backing up

Mark HHP

Hey, could anyone point me to a tutorial on moving the database to another BSV? Basically, I just want to test my newest version with the existing data without doing it on live version (just paranoid)

I've exported the SQL from the old one, (just exported everything that looked relevant) and created a new business space and imported my bsv into that one. Now what? I know that SQL stuff is quite large and involved but if anyone has a good tutorial for me to browse, I'd appreciate it.

RentProperty

Hi Mark

I also have need for this time to time so I spent some time this morning to figure this out. Here's what worked for me:

I use MySQL workbench
Lets assume I have a business space with my actual data called (MyLiveBSV)
Now Create a new Buinsess space (MyTestBSV) and allocate seperate Database with name (MyTestBSV).
Now Export the BSV from you Live Application and import and publish that BSV in your new Test Business Space.
Now go to Workbench (I Have version 6.1) and Select Data Export.
Select your live bsv schema and make sure all the objects are selected
at the bottom choose "Export to Self contained File" and make sure no other flags are ticked.
Name the file anything you like
After the export Look up the file and open it with a text editor.
The first two lines should read something like:
CREATE DATABASE IF NOT EXISTS mylivebsv /*!40100 DEFAULT CHARACTER SET latin1 */;
USE mylivebsv;
(ASSUMING YOU CALLED YOUR DB 'MyLiveBSV') 🙂
Now just replace ',mylivebsv' on both lines with 'mytestbsv'
save and close.
Now go back to Workbench and select import/restore
Select 'Import From Self-contained file' and and select the .sql file that you edited.
Then under "Default Schema to be imported to" select 'mytestbsv'
click start import.
Once the import is done you should now be able to log into the MyTestBSV business space and all the data from the MyLiveBSV business space should be there.
Now you can publish the New BSV that you want to test on the MyTestBSV business space for testing purposes.

I tested this with small amounts of data but theoretically it should work as it worked perfectly in my small test scenario.

NOTE: I am not a mysql or workbench expert at all so I won't give any guarantees... But test it locally first and see what happens. I'm pretty happy with it.

Hope it helps.

kind Regards

Hein

Mark HHP

Thanks, Hein! I'm not sure how many South African Aware devs there are so it's nice to meet another!

I will give this a bash and see what happens.

RentProperty

No Probs, Hope it works for you!

Yeah it is nice! Where in SA are you? We're located in cape town. Would be nice to meet up sometime. Always good to know some people who also know the framework. We got some BIG projects coming up this year so one never knows where you need to contract in some extra resources... 😉

Kind Regards

Hein

Mark HHP

Serious? We are too...we're up in Gardens, close to Wembley.

RentProperty

Awesome!

We're in Bellville. 🙂 So yeah should devs meet up sometime.

Regards

Hein

Mark HHP

RentProperty wrote
should devs meet up sometime.

I see what you did there.

Yeah, we want to expand the system we're working on currently, and when I say we, I mean me. I am the only one in the company building a custom CRM. There's a lot of additional functionality that I am stuck on and we would probably be keen to outsource if I can't come right. It's tricky for me not having anyone to bounce ideas/problems off of.

Mark HHP

Hi Hein,

I'm just about to try tackle this but I'm a little worried as I rebuilt the CRM using mostly the same attribute names and relationships but some are different/new. How would this affect the import process? Is there a way to specify when moving across?

RentProperty

Hey Mark, it's been a long time since I've tried/used this 🙂 So it's difficult to say, but I would think that if the DB Structure is not 100% the same then you will most likely be getting errors.

I would try the following. Backup your data, then Publish a version locally on the test DB that matches the Live one. Then do the data transfer, and then you can re-publish the latest version you want to test with. This way the structures will match when you are Transferring the data.

Kind Regards

Hein