Database Backup and Disaster Recovery Basics

A good database backup strategy answers two questions in plain numbers: how much data can the business afford to lose, and how long can it afford to be without the system? Those are your RPO and RTO. Once you have them, the 3-2-1 rule (three copies, two media types, one off-site) and a regularly tested restore procedure turn them into a working plan. A backup you have never restored is only a hope.

RPO and RTO in plain English

RPO, the recovery point objective, is about data loss. If the database fails at 15:00 and your latest backup is from 02:00, you lose thirteen hours of work. If the business says it can lose at most an hour, your RPO is one hour and you need backups at least that often.

RTO, the recovery time objective, is about downtime. It is the maximum time between the failure and the system working again. It includes finding the problem, restoring the data, and checking the application, not just copying files.

These are business decisions, not technical ones. Ask the people who rely on the system: what happens if it is gone for an hour, a day, a week? Tighter targets cost more, so it is worth being honest about which systems genuinely need them.

The 3-2-1 rule

• Three copies of your data: the live one and at least two backups.
• Two different storage types, such as local disk and cloud object storage, so one kind of failure cannot take out both.
• One copy off-site, away from the building or data centre, so fire, flood or theft does not destroy everything.

Many practitioners add that at least one copy should be immutable or offline, meaning it cannot be altered by someone who has gained access to your network. That is the best defence against ransomware that deliberately deletes backups.

Types of backup

Type	What it stores	Trade-off
Full	The whole database	Simplest to restore, slowest and largest
Differential	Changes since the last full backup	Smaller than full, restore needs two pieces
Incremental / log	Changes since the last backup of any kind	Smallest, enables point-in-time recovery, restore needs a chain

A common pattern is a full backup weekly or nightly, plus frequent log backups. That lets you restore to a chosen moment, such as just before someone ran a bad update.

An example

For MySQL, a logical backup of one database can be taken like this. The exact options depend on your setup, so check the documentation for your version:

mysqldump --single-transaction --routines --triggers \
  business_db | gzip > business_db_$(date +%F).sql.gz

Restoring it into an empty test database proves the file is usable:

gunzip -c business_db_2026-07-17.sql.gz | mysql restore_test

Other engines have their own tools and managed services have built-in snapshots, but the principle is identical: take it, copy it away, restore it somewhere safe, and check the data.

Disaster recovery beyond backups

Backups protect data. Disaster recovery protects the service. A written plan should cover:

• Who declares a disaster and who is contacted, with phone numbers that do not depend on the failed system.
• Where the system will run if the main site is unavailable, and how the application connects to the restored database.
• The order of recovery. Databases usually come before applications that depend on them.
• Credentials and encryption keys needed to restore, stored securely and separately from the backups themselves.
• How you will communicate with staff and customers during the outage.

A practical checklist

1. Agree RPO and RTO for each system with the business owner.
2. Automate backups; manual backups get forgotten.
3. Follow 3-2-1, with one copy that cannot be altered from your network.
4. Encrypt backups in transit and at rest, and protect the keys.
5. Monitor backup jobs and alert on failure, not just on success.
6. Test a full restore on a schedule and record how long it actually took.
7. Write the recovery runbook and keep a printed or offline copy.
8. Review everything after any major system change.

Common mistakes

• Never testing a restore. Corrupt, incomplete or unreadable backups are discovered at the worst moment.
• Storing backups on the same server. If the server dies or is encrypted, so do the backups.
• Confusing replication with backup. A deleted table is deleted everywhere almost instantly.
• Forgetting everything around the database. Application config, file uploads and encryption keys are also needed to recover.
• Keeping backups forever without a policy. Retention should be a deliberate decision, including any legal obligations under GDPR (this is not legal advice).
• Silent failures. A job that stopped running three months ago looks identical to one that works until you need it.

Setting RPO and RTO with a simple exercise

Pick your most important system and ask the business owner three questions. If it were unavailable from now until tomorrow morning, what would that cost us in lost work, lost sales or broken promises? If it were gone for a week? If we lost the last day of entries, could we re-enter them, and how? The answers translate directly into targets, which you can then match to techniques.

If the business says...	It implies...
"We can re-key a day of data if we must"	Nightly backups may be enough
"Losing more than an hour would hurt"	Frequent log or incremental backups
"We cannot be down for more than a few hours"	A standby server or managed failover, and a rehearsed restore
"We can work without it for a few days"	Simple backups and a documented rebuild may be acceptable

Do not aim for the tightest targets everywhere. Match the investment to the real cost of downtime for each system.

Backups and ransomware

Modern attacks often try to delete or encrypt backups before demanding payment. The defences are practical: keep at least one copy that is offline or immutable, use separate credentials for backup storage that are not stored on the production servers, and restrict who can delete backups. Guidance from bodies such as Ireland's National Cyber Security Centre and the NCSC in the UK covers this in more depth; consult their current publications for specifics.

Restore testing, step by step

1. Choose a backup at random, not the newest one.
2. Restore it to a separate test server, never over production.
3. Time every step and note anything you had to look up.
4. Run a few checks: record counts, the latest transaction date, a handful of known records.
5. Connect a test copy of the application and perform a real task.
6. Compare the elapsed time with your RTO. If it is too long, change the design, not the target.
7. Update the runbook with what you learned.

Who does what

A plan only works if people know their part. Name a recovery lead, a technical lead and a communications lead, plus a deputy for each, because disasters do not respect holidays. Make sure at least two people can perform a restore and have the access needed, and keep emergency access details stored securely outside the systems that might be down.

Where to go from here

Backup and disaster recovery are part of designing a database properly, not an afterthought. Soch Tech's database and application development service includes backup and disaster recovery alongside encryption and structured architecture, and the same thinking applies whether you build new or review an existing system.