Database Back-ups

There are only two types of people: those who have lost data, and those who are about to. Data loss can be devastating for our clients and the relationship, so guarding against it is critical.

Managing Risk

Given that so much of the data we deal with in our personal lives is now outsourced to third parties (the "cloud") we rarely feel the impacts of data loss and it's easy to assume it's just automatically taken care of by someone else. It's not.

It's relatively easy for serious damage to be done to a production database. As an app progresses, it accumulates a number of people who have database and repository access and although it's not probable it's still possible that someone could run a bad migration or an injection attack could take place, nullifying the production database.

No system is 100% secure and no tests can catch 100% of potential issues.

The question to ask is: would you want to be the one to say "sorry, no we don't have a back-up" when the client calls you in a panic having lost months of accumulated customer and financial data?

Remember: there are only two types of people.

Principles

To help separate the technical wheat from the chaff, it's important to understand the why behind our approach to backing up. This is summarised in the following principles:

Back-ups as hygiene

Just like superannuation and flossing, nobody would "naturally" do back-ups if it relied of feeling like doing it. The cost and the benefit are psychologically distanced from each other, and its human nature to ignore or avoid this critical process. Clients are unlikely to ask about them until they're needed, and by that time it's far too late.

Never together

Back-ups should never be stored in the same place as the thing they are backing up. That would be akin to writing your password down on the cover of your laptop. If something happens to the original data source, it's likely to happen to everything near it too. Keep back-ups on a different server, and preferably with an entirely different service.

If it's not tested, it's not backed up

It only takes a typo in a connection string to find that your lifesaving back-up has a hefty size of 0 Kb, sending you from the frying pan into the fire. If back-ups aren't regularly tested, they may as well not exist, and the two processes should go hand-in-hand.

Surface errors

If a back-up or testing process fails, someone should know about it and be accountable to sort it out. This is the little beep that your fire alarm makes when the battery is low. It's annoying, but it's trying to save your life.

Database Types

We have a number of different database types and servers that we use with our clients and they all require slightly different approaches for back-ups and testing.

By far the easiest to back-up and test is service, however we also have to support more boring options like good old for .

The fundamentals are the same even if the tools differ slightly, and the main goal is to put a system in place that reliably and regularly backs up, with regular checks to ensure that we can retrieve and use the data in an already backed-up database.

Combining regular backing with regular testing gives us and clients certainty that there is a validated safeguard in place, not just an assumption of one.

Schemes & Scheduling

As we have a number of maintenance plans (including legacy ones) out there with different levels of commitment, a client or app's individual maintenance agreement will determine how and how often back-ups are taken and how often they're verified.

There's also a cost/benefit balance to be struck as some datasets are large and consume a lot of space, and some data is more critical to retain than others.

Either way, at a minimum, back-ups should be verified regularly within the scope of a maintenance agreement, which is typically once a month, and actual back-ups should be taken at a minimum daily with a month's worth of history retained.

There are some very elegant schemes available to provide a good level of coverage without consuming a huge amount of space, ranging from continuous back-up processes (where "diffs" of changes are recorded, much like versioning) or snapshot-based ones that are taken according to a time schedule.