Backup Basics and Better Backups

This is a follow-on from Friday’s post about Backups

Database Backups – The Basics

Serio, like many systems, stores most (but not all) of it’s data in a relational (ie, table based) database which can be either Oracle or SQL Server. I’m going to talk about SQL Server, but most of this applies to Oracle as well. So when you log an Incident, it’s stored in a the database, when you compose an email, it's stored in the database, and on - pretty much all of the content you create is stored there.

Serio doesn’t actually write to the files. Instead, Serio says ‘here’s an Incident, store it somewhere, I don’t want to know where’ and this SQL Server duly does. Then it sits waiting for the next instruction.

All the time SQL Server is waiting, the files that it uses (of which there are quite a few) are held open, and the contents might be in what you could call an inconsistent state – in the middle of being written to, read from, reorganised or generally tidied as the database processes requests.

From a backup point of view, this means you cannot just leave the database running and then copy the files onto backup medium, because you never know what state they will be in (even if you can read them, because they are usually opened with exclusive access).

If you think this is so obvious it’s not worth pointing out, I’d bet there are people reading this who ‘backup’ by doing exactly that.

Shutdown & Startup Backups

Of course, you could shut the database down (say overnight) and then copy those files. This may work just fine (but it's something I regard as ill-advised and risky), but it’s inconvenient and can lead to large data loss. It’s inconvenient because you first of all have to shut down the applications using the database, then shut the database down, then backup, and then re-start everything.

The data loss comes about because this type of backup is typically done once every day (at night). So, if you have a system failure towards the end of the next business day, you’ve lost a day’s worth of data. Maybe you think that's OK for your environment, but to me that seems like a lot of work and information to discard.

Usually, this type of strategy is adopted by those that are not comfortable with their database tools.

Better Backups

Backing-up your database is best done by saying to the database ‘back yourself up’. The output from this command is usually a file (in SQLServer’s case), that you simply place on different (safe) media. If the worst happens and your own server starts to go ‘boing…’ (see my last post for what I mean) when it is powered on, you take this file and recover using it – buy telling the database to restore from backup file.

This kind of approach is superior to the shutdown and startup backups I’ve mentioned above for another reason – during this process of backing itself up, the database will check it’s own integrity – so if it has any internal issues you may hear about these sooner rather than later (which is a good thing).

I'll post about Full backups and the Transaction Log shortly.