StrataBlog

Backup your databases

In recognition of World Backup Day (no?, me neither), a quick run through the hows and whys of backing up your StrataBugs database.

Backing up your system seems a obvious no-brainer, but take a few minutes to consider what you’re backing up, why you’re doing it, and how you  might recover the database, should it be necessary.

There are two main reasons to take a backup: the most obvious being a hard drive or system failure, a fire, or theft, that results in an unrecoverable data loss. The other reason, sometimes overlooked, is the need to go back to an earlier database state in the event of accidental data loss by deleting, mistaken editing, or even a programming error (I know, I know, highly unlikley, but let’s move on..).

For the first, you need a copy – any copy – on some kind of media that isn’t the computer that StrataBugs is installed on. Most sensibly this means an external drive or memory stick that isn’t stored (and fire damaged/stolen with) the computer itself, OR, files copied ino the cloud using Dropbox or similar services. The only real pitfall of this approach is that you need to ideally make multiple copies on different media and make sure you know which ones are most up to date. In the event of a system failure you want to be able to go straight back to the most recent copy without having to try to work out which of your myriad sources of backup contained the latest database.

For users of stand-alone databases, you only really need concern yourself with the database file(s) in the /Data folder ending in .db or .mdb, since this is the only file that we can’t restore for you. All the rest of the StrataBugs files can be downloaded afresh, or supplied by us.

For users of server-based databases like SQL Server and Oracle, this backup issue is out of your hands, and one would assume that server custodians would have a backup schedule fully in place. It’s worth asking however, how often the backups are performed.

Going back to an earlier state of data is more problematic if your backup schedule always involves overwriting the previous backup copy. You might be able to recover a database, but what if you discovered a previous problem that has only just come to light, or a loss of data that pre-dates your last backup? For this reason, you should keep snapshot copies stretching back say, every day for a week, then every month for a year, then each year.

Another way to create an effective backup, particularly for server based databases, it to export the data through Organiser, or through the process that creates project databases in the Wells & Outcrops module. In this way you can selectively restore data through the usual import mechanism.

To make this process as painless as possible, a recent addition to the Connection properties dialog for H2 databases allows you to Prompt for backup:

If you have this feature switched on, you will get the following dialog pop up whenever you exit from the system:

You can use this to quickly create a dated compressed backup file onto your chosen media. If you need to recover your database from the file, you need to unzip the database snapshot into the StrataBugs data folder, and provided your connection and database name are the same as the backup, you can be back up and running immediately.

Don’t let your backup be the one thing that you always meant to get around to doing … but never quite made it. If you haven’t done it recently – do it today!