Backing Up Operational Data

RLJB · Post by **RLJB** » Mon Oct 11, 2010 6:45 am

Before publishing it is recommended to backup your operational data.

We have multiple business spaces. Therefore before publishing a business space I assume we should back up all the MySQL tables in the BASDB database that have the prefix of the business space name - is that correct?

Are there any other tables we would need (if we wanted to do a restore of operational data) to also backup i.e. ones without the prefix?

Also - can anyone suggest any good tools that they use to quickly undertake such back-ups in MySQL? We currently just use phpmyadmin and use Export and select the tables manually? Does anyone have a better (more fool proof) suggestion?

technopak · Post by **technopak** » Mon Oct 11, 2010 8:04 am

Hi,

I backup the entire MySQL database any time I make a BSV change to an operational app.

I use the old MySQL Administrator tool for both manual and scheduled backups - I find it very good. There is a new package called MySQL Workbench which has replaced MySQL Administrator, but I haven't used it.

MySQL Administrator is simple and quick, and backups are easy.

You can download it here - http://sourceforge.net/projects/mysql-admin/

Hope this helps,

Peter

rocketman · Post by **rocketman** » Fri Oct 15, 2010 9:46 pm

Hi Guys,

Mysql Workbench has one major drawback, it doesn't yet have any facility to schedule backups.

Like Technopak, I also use MySQL Admin, both for one off's and regular scheduled full backups.

Never used the PHP admin package you mentioned but I can tell you that MysqlAdmin can run on a local PC or network and backup across the internet. In my case, My production server is on an internet based VPS and I run MysqlAdmin locally on my development PC thus providing me with an "Offsite" backup solution

aware_support · Post by **aware_support** » Fri Oct 15, 2010 10:58 pm

One of the popular MySQL tools is Navicat. I believe it has the feature of doing scheduled backups

kklosson · Post by **kklosson** » Sat Oct 16, 2010 1:52 am

I use Navicat which does have the ability to schedule backups. I then use Mozy Pro to backup my backups. The keen thing here is that Mozy keeps 30 days of your backups by default. So you get a backup window out of the deal by default.

technopak · Post by **technopak** » Sat Oct 16, 2010 8:11 am

Hi,

Just to say that I use i-Drive for my online backups. If you refer 5 contacts to them, they give you 12Gb of online storage space free.

So, I schedule backups using MySQL Administrator to a local folder and then backup that folder each night to i-Drive. The backups only are incremental, so only new or changed data is uploaded and it's reasonably quick.

Then I decide how many backups to maintain online - usually between 30 and 40, and move the old copies of my backups to a different folder on the local server. This ensures that the online storage doesn't exceed my space allocation and I can keep older backups for a few months too.

It's working well so far.

Peter

rocketman · Post by **rocketman** » Sat Oct 16, 2010 8:25 am

If as you say, you have multiple BSV's running on your server, then I would think it is important to only backup the tables relating to the BS you have made changes to because whilst the backing up and publishing is taking place, I assume other users could still be working in their BS's and making changes to their data

If anything goes wrong and you need to restore the BS you are working on, at least others will not lose data through no fault of their own whereas if you restore the entire BasDB schema, others will suffer data loss unnecessarily.

In the above scenario, it is vitally important that you DO NOT restore the Bas_idgen table. As you will see, there is only one for the entire schema. It is where the last unique ID number is held. If you back it up and then restore it after other's have continued to work, someone somewhere is going to start getting duplicate key errors when they try to add new records

technopak · Post by **technopak** » Sat Oct 16, 2010 9:58 am

Good points Rocketman,

I have a concern about only backing up the one BS: what happens if the entire database is somehow corrupted during the publish operation ?

I guess it's a matter of where the more likely failure point is. Is the entire database vulnerable when publishing a new BSV or is just the part of the schema which relates to the newly published BS ?

In light of Rocketman's post, I think it's best to backup the entire database before you publish a new BSV, but only restore as much as you need to should a corruption occur. This will avoid overwriting the latest data in other BSs while ensuring you have a backup which is as comprehensive and current as possible.

Perhaps support can give some advice ?

Peter

rocketman · Post by **rocketman** » Sat Oct 16, 2010 11:50 am

That sounds like a "Best Practice" to me Peter

AwareIm support - "Tips and Tricks" or even a new "Best Practices" sub group?

RLJB · Post by **RLJB** » Tue Oct 19, 2010 9:52 am

Thanks for your answers everyone. Now a quick clarification required here please...

Assuming we take a FULL database backup every time before publishing and we need to restore the operational data for just ONE Business Space (not all business spaces) - what exactly should we restore?

Is it only the tables prefixed with with the business space name? Or is there another table/s to consider too? What about this Bas_idgen table?

rocketman · Post by **rocketman** » Tue Oct 19, 2010 1:53 pm

If it's just your data that's screwed - just restore the tables with the correct BSV prefix.

Tables:
Bas_IDgen holds the last used unique ID used by the awareIM system. It is incremented every time a new record is created irrespective of which BS or table it's in. Personally I would have thought it better if each BS had its own counter but I suppose there must be an internal reason why they've done it this way.

EXECUTION_CONTEXTS - This table holds a list of processes in the waiting state. If you are in the habit of publishing new versions whilst people are still logged in, this action could leave some processes in this waiting state. They don't seem to cause any problems but it doesn't do any harm to occasionally clear out this table as long as there is no-one logged in. Might be best done by taking AwareIM offline temporarily

BAS_SYSTEM - No idea - I think it holds the license key amongst other things - over to AwareIM

DOMAINS - No Idea - it's a blob - Possibly the latest current compiled BS versions. That's just a guess but I can't see any other place they could be.

DOMAINVERSIONS - looks like it contains all the BSV's that you haven't actually deleted both current and obsolete

DVHISTORIES - It's like a changelog. When you create a new version and the window pops up that allows you to change the version description? - well this is where you'll find all the entries for all the versions of all the BS's

... and I would say, if things are screwed to such an extent that you have to consider restoring these - buy a support ticket and let the experts sort it

rocketman · Post by **rocketman** » Tue Oct 19, 2010 2:03 pm

Forgot to say, If an actual BSV gets corrupted whilst in test mode, the easiest way around it is to delete the corrupted test version and create a new one from the current version (means re-doing some work).

What isn't clear (Perhaps Aware support can help us all here) is what we would need to restore if just one current - in use - bsv gets corrupted. Is it possible to restore that BSV without impacting on the other published BSV's