Test Your Database Backups Before You Need Them

A backup job that reports success only means a file was written. It does not prove that the file can be restored, that the restored database is consistent, or that the restore can be completed in the time the business can tolerate. Many failed recoveries involve backups that existed and were never proven to work.

The test that matters is a full restore onto a separate server or instance, not onto production. It should prove four things: that the backup can be read and restored from start to finish, that the restored database passes the database engine's own consistency checks, that the data is usable, for example by running a few queries or letting the application connect, and that the process took an acceptable amount of time.

Most databases are recovered from a full backup plus differentials or transaction logs. A test that only restores the full backup does not show whether the later parts of the chain are intact, or whether a point-in-time restore to just before a mistake is possible. At least some tests should restore the full chain up to a chosen time.

If backups are encrypted, the certificates, keys or passwords needed to open them must be stored somewhere separate from the backups themselves, and the restore test should use them. A backup that can only be opened with a key that lives on the failed server is not really a backup. Accounts, permissions and configuration that the application depends on should also be recorded, because a restored database without them may not be usable.

Two numbers guide the whole plan: how much data the business can afford to lose, and how long it can afford to be without the system. A restore test shows the real restore time, which is often much longer than assumed once the size of the data and the speed of the storage are taken into account. If the measured time is too long, the plan needs to change before an incident, not during one.

A practical pattern is a scheduled restore test at regular intervals, and another after any significant change to the systems, backup software or storage. Keep more than one copy, keep at least one copy away from the main system, and keep at least one that cannot be changed or deleted from the production network, so that an attacker or a mistake cannot remove every copy at once.

A restore that depends on one person's memory is fragile. The steps, the locations of backups, the credentials and the order of restoration should be written down, and tested by someone other than the person who wrote them. Recording the results of each test also gives an honest picture of how much the backups can be trusted.