Database backups are crucial for safeguarding data, ensuring business continuity, and meeting regulatory requirements. They provide a safety net to recover from various types of data loss scenarios, helping organizations maintain data integrity and operational resilience.
Common backup and recovery scenarios
Unplanned shutdown
Accidental deletion
Data corruption
Migration
Share data with business partners
Physical vs. Logical Backup
The choice between physical and logical backups depends on the use case requirement. For disaster recovery, system migrations, or full system backups, physical backups are often preferred due to their efficiency and completeness. However, for tasks like data extraction, transferring data between different database systems, or selective data retrieval, logical backups are more suitable.
| Logical Backup | Physical Backup |
| Contains DDL and DML commands to recreate a database | Creates a copy of physical files, including logs and configuration |
| Reclaim wasted space | Can only restore to similar RDBMS |
| Slow and may affect performance | Smaller and quicker |
| Granular | Less granular |
| Use import/export, dump/load utilities to perform logical backup | Common for specialized storaged systems in clou |
What to backup and restore
When performing a database backup, it's important to consider backing up all relevant database objects to ensure a complete and consistent snapshot of the data and schema.
Database
Schema
Tables
Views, stored procedures, triggers, functions and constraints
User accounts and permissions
Configuration settings
Key considerations when backing up databases and their objects
A lot of factors need to be considered to ensure that the database backup and recovery process is reliable, secure and capable of safeguarding the data.
Check if the backup is valid (e.g. data integrity is intact and latest).
Check if the recovery plan works
Ensure that backup files, as well as the transfer process, are secured.
Backup options
Compression - reduce size for storage and transfer, but this would increase time for backup and recovery
Encryption - reduces the risk of data being compromised, but increases time for backup and recovery
Types of Backup
Full backup
Simple approach
Requires a lot of time, bandwidth and storage
Point-in-time backup
Captures a specific moment or snapshot of a system's data and files. It records the data as it existed at that exact point in time, preserving the state of the system at that moment.
Uses logged transactions for the backup process
Differential backup
A copy of any data that has changed since the last full backup was taken
Example use case: Run a full backup once a week (e.g. Sunday) then run a differential backup every day of the week.
Recovery approach in the use case: Perform recovery of the latest full backup and then apply only the latest differential backup
Recovery process time is faster than incremental backup
Incremental backup
A copy of any data that has changed since the last full backup OF ANY TYPE was taken
Example use case: Run a full backup once a week (e.g. Sunday) then run an incremental backup every day of the week.
Recovery approach in the use case: Perform recovery of the latest full backup and then apply all incremental backups
Backup process time is faster than differential backup
Hot vs. Cold Backups
| Hot Backup | Cold Backup |
| Known as online backup; allows data to be backed up while the database is active | Known as offline backup; require the database to be offline |
| Performed on data when it is in use | |
| No impact on availability, hence users can still continue with their activity in the application | Puts the application to be unavailable to users during the backup run |
| Result in performance degradation for users during the backup run | |
| Can affect data integrity if data changes during the backup process | Eliminates data integrity risk associated with hot backups |
| Stored on an available server and receives regular updates from the production database | Stored on external drives or on servers that are shut down between backup operation |
| Recovery process is longer |
Backup Policies Consideration
Physical vs logical
Full, differential, or incremental
Hot or cold
Compression and encryption
Backup frequency
Is data regularly changing or being added
Is the existing table large?
Schedule of backup
Automation feature on backup run and schedule
Managed Cloud Backups
Most managed cloud databases provide automated backup functionality. Generally, options dependent on the RDBMS and cloud service provider include:
Preconfigured automated backup
Configurable automated backup
Manual backups
Third-party tools
Database Transaction Log
Transaction logs keep track of changes to the database (i.e. insert, update or delete transactions).
These logs assist with recovery and can be applied to recover or roll a database forward to a point in time.
Best practice dictates that the log is isolated in a location separate from where the database is running (i.e. use log mirroring or log shipping to replicate/ write the log to another remote system)