Fundamental Snippet: Database Backup and Recovery

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.

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

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)