This section covers how to backup your OpenVPMS system - both what to backup and how to.

In the following it is assumed that you are running OpenVPMS on a Windows system.  If you are using a linux system, then you should be able to do the appropriate folder name conversions.

Note also that you really should practice disaster recovery, ie assume that your system was lost or destroyed and that you have to start from scratch with a new computer.  That is, your backups are only any good if they can be restored. Backups should be periodically tested or restored to a backup server as part of your backup process. If you don't have a 'backup server', at least get your hands on a system that you can install OpenVPMS on and check that you can restore your database.

What to backup

The MySQL database itself. OpenVPMS keeps everything in the database including the report and document templates and all patient and customer documents, attachments and images. So backing up the openvpms database itself secures most of what you will need to recover in the event of a disaster. Note that MySQL will normally have two other databases (or 'schemas'), mysql and performance_schema.  You don't need to backup these.

Other files. Apart from the database itself, you should backup:

1. the mysql configuration file, normally called 'my.ini'. In a standard windows system this is located in C:\Program Files\MySQL\MySQL Server 5.5\my.ini - remember that even if you have not done any other adjustments to the standard my.ini file, you will have made the changes required by openvpms as documented in Installing OpenVPMS|Requirements.
2. any other modified versions of files in the standard release package. These will include any modifed archetypes (.adl files) and any modified files in the <TOMCAT_HOME>/webapps/openvpms/WEB-INF/classes/org/openvpms/web/resource/localisation and the <TOMCAT_HOME>/webapps/openvpms/WEB-INF/classes/style folders.
3. any other installation specific files. These include any files you have that will be lost if you have a complete disaster and have to start from scratch.

It is also wise to occasionally make a 'system image' that you can restore the system disk from in the event of a disk failure (otherwise you are going to have to reinstall all your software). In Windows 7 this can be done via Control Program|Backup and Restore, and in Windows 8 via Control Program|File History.

When to backup

Essentially this is governed by the amount of data you can afford to lose and have to re-enter. If you do a daily backup at 6pm each business day, and the system fails at 2pm, then you will have to re-enter everything that happened after 6pm the previous day.  Hence a weekly backup is probably not a viable option.

How to backup

Essentially you need to take a copy and put it somewhere else, where 'somewhere else' means, at a minimum, an external disk or storage device. You should also strongly consider having a copy held off-site so that you can cope with your building being destroyed or all your computer equipment being stolen.

Although USB memory sticks are now available with large capacities, and are wonderful for transferring files, they are not reliable enough to use as backup storage. Use a USB hard drive or NAS (Network Attached Storage) device instead.

Backing up to 'the cloud' is a possibility, but most sites do not have sufficient upload bandwidth to make this a viable solution.

So how to backup MySQL.  There are at least three ways to do this: mysqldump, raw file copy, and database replication.

mysqldump
The mysqldump command line utility has lots of options, but for our purposes it is invoked as follows:

mysqldump -u <username> --password=<password> --single-transaction --quick --extended-insert --add-locks --result-file=<dumpfile> openvpms

where both <username> and <password> will be 'openvpms' unless you have set these differently in your system, and <dumpfile> is where you want the data to go, eg E:\backups\opvdump.sql

The single-transaction option allows for the fact that someone may be using the system entering data - and as this implies you do not have to shut down Tomcat to prevent people using the system while mysql dump runs.  You certainly do not want to shutdown mysql itself - it has to be running for mysqldump to work.

The quick option functionally decreases the impact of running mysqldump so that it minimises the impact on normal users (otherwise they will notice a slow down in the system response).

The extended-insert and add-locks options make the restore process more efficient.

In the windows environment, especially with a large database, it is more efficient to use the results-file argument as above rather than piping the output, eg as follows but in the unix environment piping is just as efficient.

mysqldump -u <username> --password=<password> ... openvpms > E:\backups\opvdump.sql

You will see examples on the web of compressing the .sql file to save space. This is normally not worthwhile with OpenVPMS databases because typically such a large part of the data is uncompressible jpeg images.

To restore the database, you use the mysql command line utility as follows:

mysql -u <username> -p openvpms < <dumpfile>

Note that MySQL dumps can be large - see also 'File Sizes' below. Compression can achieve some space saving at the cost of the extra time required to do the compression. Linux users will be familiar with the process or piping the output of one program into another, and thus on a linux system one can do:

• To backup: mysqldump -u openvpms -p openvpms | gzip > dump.sql.gz
• To restore: gzip -cd dump.sql.gz | mysql -u openvpms -p openvpms

On Windows systems the same can be done using the 7-Zip compression utility (see http://www.7-zip.org) as follows:

• To backup: mysqldump -u openvpms -p openvpms | 7z a -si dump.sql.7z
• To restore: 7z x dump.sql.7z > mysql -u openvpms -p openvpms

However, because a typical OpenVPMS database contains a large number of uncompressable images (from x-rays and ultra-sounds), the saving is space is normally only about 25%.

raw file copy
If you want to minimise the database restore time, then you can simply copy the MySQL database files to another device.  In MySQL terminology, this is called a 'cold backup' - see http://dev.mysql.com/doc/refman/5.5/en/innodb-backup.html. To do this you need to shut down MySQL before you copy the files, and you have to copy nearly everything in the MySQL data folder. Specifically you need to copy the ibdata files and each of the sub-folders, ie mysql, performance_schema, and openvpms.  You do not need to copy ib_logfile1 and ib_logfile2.

In the windows environment, the best copy program to use is robocopy.

Note that when do the recovery (by copying the files back) you MUST still be using the same version of MySQL, and you may need to be wary about the log files.  If you have not been backing these up, then when you copy the other files back you must ensure that any old log files are deleted

database replication
If you have a large busy practice, you should consider using MySQL's ability to replicate the database on another machine. There is a slave database on the other machine, and updates to the master database are continuously copied to the slave. When a disaster occurs, it is quite easy and very quick to switch operations to the backup machine which was running the slave database which now becomes the master.

See http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html

other information
For more information on backing up and restoring MySQL databases, see:

• http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
• http://dev.mysql.com/doc/refman/5.1/en/innodb-backup.html
• http://dev.mysql.com/doc/refman/5.1/en/mysql.html

File Sizes

The size of an OpenVPMS database depends mainly on two things, the number of transactions recorded, and the number (and size) of the stored documents. The following data is taken from a reasonably sized business. ('participations' counts the number of links between data.)

As you can see, the documents and participations files make up some 80% of the total database size. The Time column shows the time required for the raw file copy, and the uncompressed and compressed mysql dumps. In all cases the data is moved to a NAS unit via a 1gigabit network connection.  Note that although compression gives a space saving of 24%, it increases the time required by 175%. The relatively small space saving is due to the fact that many of the documents are jpeg images and thus uncompressible.

Hardware redundancy

Apart from backing up, it is possible to decrease the probability of failure by providing some redundancy in the system.  There are two things to consider, RAID and server class systems.

RAID (Redundant Array of Inexpensive Disks) - see http://en.wikipedia.org/wiki/RAID - provides a storage system that is resilient to failure. These can be configured as a simple 2 disk mirror RAID-1 setup where data is written to both drives, and the system will continue to operate when one fails. At the other end of the scale there are multi-disk RAID-5 and -6 systems with hot stand-by drives.

Server class hardware  There is a very real difference between a standard desktop machine and a server class system (apart from the price and weight). Server machines typically offer a remote access console (so you can power on the system from afar), large fast storage systems, and dual power supplies.

Even if you don't use a server class machine, you should consider the use of enterprise rather than consumer class disk drives.

Second system  The simplest approach to hardware redundancy is simply to have a second system that is a exact duplicate. You can use this system for other non-critical things (perhaps as a spare worstation).  You can then make an image backup of the primary system.  When it fails, you disconnect the primary system from the network (in case it is still partially alive), restore the image backup to the backup system, and then restore your last database backup. You may also need to unplug any printers from the failed system and plug them into the backup system. Your users can then resume work - but they will have to re-key transactions since the last backup.

Note we need the backup machine to be an exact duplicate so that the image backup from the primary system can be restored and run without any changes.