Every database will eventually face a bad day: a failed disk, an accidental DELETE without a WHERE clause, a software bug, or a ransomware attack. When that day comes, the only thing that matters is whether you can get your data back, and how quickly. In this guide you will learn the core ideas behind database backup and recovery, from backup types to the 3-2-1 rule and the two numbers that shape every recovery plan: RPO and RTO.
Why backups matter (and why replication is not enough)
Many beginners assume that a replica, a second server that copies data from the first, is the same as a backup. It is not. Replication protects you from hardware failure, but it also copies mistakes. If someone drops a table on the primary server, the replica usually drops it too, within moments. A backup, by contrast, is a separate copy from a specific point in time that you can go back to.
The main types of backups
| Type | What it copies | Pros | Cons |
|---|---|---|---|
| Full | The entire database | Simplest to restore | Slow and uses the most storage |
| Differential | Everything changed since the last full backup | Faster restore than incremental; smaller than full | Grows larger each day until the next full backup |
| Incremental | Only what changed since the last backup of any kind | Smallest and fastest to create | Restore needs the full backup plus every increment in the chain |
| Transaction log / binary log | A continuous record of every change | Enables point-in-time recovery | Requires careful management and retention |
The exact names and tools vary by system. For example, SQL Server uses full, differential and transaction log backups; PostgreSQL offers logical dumps with pg_dump and physical backups combined with write-ahead log (WAL) archiving; MySQL commonly uses logical dumps with mysqldump, physical backup tools and binary logs. The concepts, however, are very similar.
Logical vs. physical backups
- Logical backups export data as SQL statements or a structured format. They are portable between versions and platforms, and easy to inspect, but can be slow to restore on large databases.
- Physical backups copy the actual data files on disk. They are usually faster for large databases, but are tied more closely to the database version and configuration.
Hot, warm and cold backups
A cold backup is taken while the database is shut down, which guarantees consistency but means downtime. A hot backup is taken while the database is running, using the engine’s own mechanisms to guarantee a consistent copy. Most production systems rely on hot backups because they cannot afford to stop.
The 3-2-1 backup rule
The 3-2-1 rule is a widely used guideline for resilient backups:
- 3 copies of your data (the original plus two backups);
- 2 different types of storage media or systems;
- 1 copy stored off-site, such as in another location or a cloud storage account.
Many teams add extra protection against ransomware by keeping at least one copy immutable (it cannot be changed or deleted for a set period) or offline.
RPO and RTO: the two numbers that define your plan
Recovery Point Objective (RPO) is the maximum amount of data you can afford to lose, measured in time. If your RPO is 15 minutes, you need backups or logs that let you restore to a state no older than 15 minutes before the incident.
Recovery Time Objective (RTO) is the maximum acceptable time to get the system working again. An RTO of one hour means that, after a failure, service must be restored within an hour.
These two values are business decisions, not just technical ones. A small internal tool may tolerate a day of lost data; an online store taking orders may tolerate only minutes. The tighter the RPO and RTO, the more frequent, automated and costly the backup strategy becomes.
Point-in-time recovery
Point-in-time recovery (PITR) lets you restore a database to a precise moment, such as just before a mistaken command ran. It works by restoring a base backup and then replaying the transaction or binary logs up to the chosen time. To use it, you must keep log backups continuously and make sure they are stored safely alongside your full backups.
A simple backup strategy to start with
- Define your RPO and RTO together with the people who depend on the data.
- Schedule a regular full backup, for example weekly.
- Add differential or incremental backups, for example daily.
- Enable and archive transaction or binary logs if you need point-in-time recovery.
- Copy backups to a second location and to an off-site or cloud destination.
- Encrypt backups and restrict who can read or delete them.
- Monitor jobs so that a failed backup triggers an alert.
The most important step: test your restores
A backup that has never been restored is only a hope. Corrupted files, missing log segments, wrong credentials or outdated documentation often appear only during a real recovery. Schedule regular restore tests into a separate environment, check that the data is complete and measure how long the process really takes. That measured time is your true RTO.
Common mistakes to avoid
- keeping backups on the same disk or server as the database;
- relying only on replication;
- never testing a restore;
- forgetting to back up configuration files, users and permissions;
- deleting old backups too early, so a slowly spreading corruption cannot be rolled back;
- leaving backup credentials and storage open to anyone with access to the production system.
Conclusion
Good backup and recovery practice comes down to a few habits: choose the right mix of backup types, follow the 3-2-1 rule, define realistic RPO and RTO targets, and test your restores regularly. With these in place, a bad day becomes an inconvenience instead of a disaster.
If you want to go deeper, explore the database courses on Cursa. They cover SQL, relational design and administration fundamentals that will help you protect and manage data with confidence.
