Skip to content
My Docs

Backup and Recovery Strategies

Difficulty: Advanced
Generated on: 2025-07-10 02:37:52
Category: SQL Cheatsheet for Database Development

SQL Backup and Recovery Strategies: Advanced Cheatsheet

Section titled “SQL Backup and Recovery Strategies: Advanced Cheatsheet”

1. Quick Overview

Backup and recovery are critical processes for protecting data against loss or corruption. A robust strategy ensures business continuity and minimizes downtime. This cheatsheet covers various backup types, recovery techniques, and best practices for different SQL database systems.

When to Use:

  • Regularly: Schedule automated backups according to data change frequency and recovery time objectives (RTO) and recovery point objectives (RPO).
  • Before Major Changes: Back up before applying database schema changes, upgrades, or large data migrations.
  • After Critical Events: Consider a backup after significant data loading or processing.
  • Disaster Recovery: Backups are essential for restoring data in case of hardware failure, natural disasters, or security breaches.

2. Syntax

Here’s a breakdown of common backup and recovery syntax for different database systems. Specific options and features may vary depending on the database version.

MySQL:

  • Logical Backup (mysqldump): Creates a text file containing SQL statements to recreate the database.

    -- Full database backup
    mysqldump -u [username] -p[password] [database_name] > backup.sql
    

    -- Backup specific tables
    mysqldump -u [username] -p[password] [database_name] [table1] [table2] > tables_backup.sql
    

    -- Backup with compression
    mysqldump -u [username] -p[password] [database_name] | gzip > backup.sql.gz
    

    -- Backup with specific options (e.g., single transaction for consistency)
    mysqldump -u [username] -p[password] --single-transaction --quick --lock-tables=false [database_name] > backup_consistent.sql

  • Physical Backup (using mysqlbackup utility - MySQL Enterprise Backup): Backs up the actual database files. More efficient for large databases.

    Terminal window
    # Requires MySQL Enterprise Backup
    mysqlbackup --backup-dir=/path/to/backup/directory backup-to-image

  • Restore (mysqldump):

    -- Restore from a logical backup
    mysql -u [username] -p[password] [database_name] < backup.sql

  • Point-in-Time Recovery (using Binary Logs):

    -- Enable binary logging in my.cnf (or my.ini)
    -- log_bin = mysql-bin
    -- binlog_format = ROW  (Recommended for reliable recovery)
    

    -- Find the last backup timestamp
    -- Restore from the last full backup
    mysql -u [username] -p[password] [database_name] < full_backup.sql
    

    -- Apply binary logs to recover to a specific point in time
    mysqlbinlog --start-datetime="2023-10-27 10:00:00" mysql-bin.000001 | mysql -u [username] -p[password] [database_name]

PostgreSQL:

  • Logical Backup (pg_dump):

    -- Full database backup
    pg_dump -U [username] -F p -f backup.sql [database_name]
    

    -- Backup specific tables
    pg_dump -U [username] -t [schema].[table1] -t [schema].[table2] -F p -f tables_backup.sql [database_name]
    

    -- Backup with compression
    pg_dump -U [username] -F c -f backup.tar.gz [database_name]
    

    -- Backup only the schema
    pg_dump -U [username] -s -F p -f schema_backup.sql [database_name]

  • Physical Backup (File System Level): Requires stopping the database server or using online backup tools.

  • Restore (pg_restore):

    -- Restore from a logical backup
    psql -U [username] -d [database_name] -f backup.sql
    

    -- Restore from a compressed backup
    pg_restore -U [username] -d [database_name] backup.tar.gz

  • Point-in-Time Recovery (using Write-Ahead Logging (WAL)):

    -- Enable WAL archiving in postgresql.conf
    -- wal_level = replica
    -- archive_mode = on
    -- archive_command = 'cp %p /path/to/archive/%f'
    

    -- Base backup
    pg_basebackup -D /path/to/basebackup -U [username] -P -v
    

    -- Restore from base backup and apply WAL logs
    # Stop the database server
    # Copy the base backup to the data directory
    # Recover using recovery.conf (PostgreSQL versions < 12) or recovery.signal (PostgreSQL versions >= 12)
    

    # Example recovery.signal (PostgreSQL >= 12):
    # touch /path/to/data/recovery.signal
    

    # Example recovery.conf (PostgreSQL < 12):
    # restore_command = 'cp /path/to/archive/%f %p'
    # recovery_target_time = '2023-10-27 10:00:00'

SQL Server:

  • Full Backup:

    -- Using T-SQL
    BACKUP DATABASE [database_name]
    TO DISK = 'C:\Backup\database_name.bak'
    WITH FORMAT,
         INIT,  -- Overwrites existing backup
         NAME = 'Full Database Backup';
    GO

  • Differential Backup: Backs up changes since the last full backup.

    BACKUP DATABASE [database_name]
    TO DISK = 'C:\Backup\database_name_diff.bak'
    WITH DIFFERENTIAL,
         INIT,
         NAME = 'Differential Database Backup';
    GO

  • Transaction Log Backup: Backs up the transaction log, allowing for point-in-time recovery.

    BACKUP LOG [database_name]
    TO DISK = 'C:\Backup\database_name_log.trn'
    WITH INIT,
         NAME = 'Transaction Log Backup';
    GO

  • Restore:

    -- Restore from a full backup
    RESTORE DATABASE [database_name]
    FROM DISK = 'C:\Backup\database_name.bak'
    WITH REPLACE,  -- Overwrites existing database
         RECOVERY;   -- Brings the database online
    

    -- Restore from a full backup, differential backup, and transaction log backups (Point-in-Time Recovery)
    RESTORE DATABASE [database_name]
    FROM DISK = 'C:\Backup\database_name.bak'
    WITH NORECOVERY; -- Leaves the database in a restoring state
    

    RESTORE DATABASE [database_name]
    FROM DISK = 'C:\Backup\database_name_diff.bak'
    WITH NORECOVERY;
    

    RESTORE LOG [database_name]
    FROM DISK = 'C:\Backup\database_name_log.trn'
    WITH STOPAT = 'Oct 27, 2023 10:00:00 AM',  -- Point-in-time recovery
         RECOVERY;
    GO

Oracle:

  • Recovery Manager (RMAN): The primary tool for backup and recovery in Oracle.

    -- Connect to RMAN
    rman target /
    

    -- Full database backup
    backup database plus archivelog;
    

    -- Incremental backup (level 0 is like a full backup, level 1 backs up changes since the last level 0 or 1)
    backup incremental level 1 database plus archivelog;
    

    -- Restore and recover
    restore database;
    recover database;
    

    -- Point-in-time recovery
    run {
        set until time "TO_DATE('2023-10-27 10:00:00', 'YYYY-MM-DD HH24:MI:SS')";
        restore database;
        recover database;
    }

3. Common Use Cases

  • Disaster Recovery: Recovering from a complete system failure by restoring backups to a separate location.
  • Database Migration: Backing up a database and restoring it to a new server.
  • Development/Testing: Creating copies of production databases for development and testing purposes.
  • Data Corruption: Restoring from a backup to recover from data corruption caused by hardware or software errors.
  • Human Error: Reverting accidental data modifications or deletions.
  • Auditing: Restoring backups for historical data analysis or compliance audits.
  • Data Warehousing: Creating point-in-time snapshots of transactional databases to load into a data warehouse.

4. Best Practices

  • Automate Backups: Use scheduling tools (e.g., cron, SQL Server Agent, DBMS_SCHEDULER in Oracle) to automate backups regularly.
  • Verify Backups: Regularly test backups by restoring them to a test environment.
  • Offsite Storage: Store backups in a separate physical location to protect against site-specific disasters. Consider cloud storage.
  • Encryption: Encrypt backups to protect sensitive data.
  • Compression: Compress backups to reduce storage space and transfer time.
  • Backup Retention Policy: Establish a retention policy for backups based on business requirements and regulatory compliance. Rotate and purge old backups.
  • Monitor Backup Jobs: Monitor backup jobs to ensure they are completing successfully. Set up alerts for failures.
  • Document Procedures: Document the backup and recovery procedures clearly and keep them up-to-date.
  • Minimize Downtime: Use techniques like online backups, mirroring, and replication to minimize downtime during backups and recovery.
  • Regularly Review and Update: Review and update the backup and recovery strategy regularly to adapt to changing business needs and technology.
  • Consider RTO and RPO: Define Recovery Time Objective (RTO) and Recovery Point Objective (RPO) to guide your backup and recovery strategy.

5. Examples

MySQL Example: Restoring to a Point-in-Time

Suppose you have a table orders with the following data:

-- Sample data
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATETIME,
    customer_id INT,
    amount DECIMAL(10, 2)
);


INSERT INTO orders (order_id, order_date, customer_id, amount) VALUES
(1, '2023-10-26 09:00:00', 101, 100.00),
(2, '2023-10-26 10:00:00', 102, 200.00);


-- Assume a full backup was taken on '2023-10-26 08:00:00'
-- A rogue script updated all the orders to amount=0 at '2023-10-26 11:00:00'
UPDATE orders SET amount = 0;

To restore the data to the state before the update:

  1. Restore the Full Backup:

    mysql -u [username] -p[password] [database_name] < full_backup.sql

  2. Apply Binary Logs up to the desired point-in-time:

    mysqlbinlog --start-datetime="2023-10-26 08:00:00" --stop-datetime="2023-10-26 11:00:00" mysql-bin.000001 | mysql -u [username] -p[password] [database_name]

PostgreSQL Example: Setting up WAL Archiving

  1. Edit postgresql.conf:

    wal_level = replica
    archive_mode = on
    archive_command = 'cp %p /path/to/archive/%f'

  2. Restart PostgreSQL:

    Terminal window
    sudo systemctl restart postgresql

  3. Take a base backup:

    Terminal window
    pg_basebackup -D /path/to/basebackup -U [username] -P -v

SQL Server Example: Creating a Backup Schedule

-- Create a SQL Server Agent job to perform a full database backup
USE msdb;
GO


EXEC dbo.sp_add_job
    @job_name = N'Full Database Backup - [database_name]';
GO


EXEC sp_add_jobstep
    @job_name = N'Full Database Backup - [database_name]',
    @step_name = N'Backup Database',
    @subsystem = N'TSQL',
    @command = N'BACKUP DATABASE [database_name] TO DISK = ''C:\Backup\[database_name]_$(date).bak'' WITH INIT, FORMAT',
    @retry_attempts = 5,
    @retry_interval = 5;
GO


EXEC dbo.sp_add_schedule
    @schedule_name = N'Daily Backup',
    @freq_type = 4, -- Daily
    @freq_interval = 1, -- Every day
    @active_start_time = 010000; -- 1:00 AM
GO


EXEC sp_attach_schedule
    @job_name = N'Full Database Backup - [database_name]',
    @schedule_name = N'Daily Backup';
GO


EXEC dbo.sp_add_jobserver
    @job_name = N'Full Database Backup - [database_name]',
    @server_name = N'(local)';
GO

Oracle Example: Using RMAN to Restore and Recover

Suppose you need to restore the database to a point in time.

-- Connect to RMAN
rman target /


-- Run the restore and recovery
run {
  set until time "TO_DATE('2023-10-27 10:00:00', 'YYYY-MM-DD HH24:MI:SS')";
  restore database;
  recover database;
  alter database open resetlogs;
}

6. Common Pitfalls

  • Insufficient Testing: Failing to regularly test backups. A backup is useless if it cannot be restored.
  • Lack of Documentation: Missing or outdated backup and recovery procedures.
  • Inadequate Monitoring: Not monitoring backup jobs for failures.
  • Storing Backups on the Same Server: Storing backups on the same server as the database. This defeats the purpose of disaster recovery.
  • Incorrect Permissions: Incorrect file system permissions preventing backup or restore operations.
  • Not Backing Up System Databases: Forgetting to back up system databases (e.g., master in SQL Server, postgres in PostgreSQL, mysql in MySQL). These databases contain critical system information.
  • Not Backing Up Configuration Files: Forgetting to back up database configuration files (e.g., my.cnf, postgresql.conf, sqlnet.ora).
  • Using Incorrect Backup Type: Using only full backups for large databases, which can be slow and resource-intensive. Consider a combination of full, differential, and transaction log backups.
  • Ignoring Binary Logs/WAL: Not enabling and managing binary logs (MySQL) or Write-Ahead Logging (PostgreSQL) for point-in-time recovery.

Troubleshooting Tips:

  • Check Error Logs: Examine the database server error logs for clues about backup or restore failures.
  • Verify File Permissions: Ensure that the user account running the backup or restore has the necessary file system permissions.
  • Check Disk Space: Make sure there is sufficient disk space available for backups and restore operations.
  • Test Connectivity: Verify network connectivity between the database server and the backup storage location.
  • Consult Documentation: Refer to the database server documentation for detailed information on backup and recovery procedures.

7. Database Variations

FeatureMySQLPostgreSQLSQL ServerOracle
Backup Toolsmysqldump, mysqlbackup (Enterprise Backup)pg_dump, pg_dumpall, pg_basebackupSQL Server Management Studio (SSMS), T-SQL BACKUP DATABASE commandRecovery Manager (RMAN)
Backup TypesLogical, Physical, Incremental (using Enterprise Backup)Logical, Physical, Base Backup, Incremental (using third-party tools or LVM snapshots)Full, Differential, Transaction Log, FilegroupFull, Incremental, Cumulative, Archivelog
PITRBinary LogsWrite-Ahead Logging (WAL)Transaction Log BackupsArchivelogs, Flashback
Configurationmy.cnf (or my.ini)postgresql.conf, pg_hba.confSQL Server Configuration Manager, SQL Server Management Studiosqlnet.ora, tnsnames.ora, database initialization parameters
Restore Toolsmysql command-line clientpsql, pg_restoreSQL Server Management Studio (SSMS), T-SQL RESTORE DATABASE commandRMAN
ReplicationReplication (Master-Slave, Group Replication), MySQL ClusterReplication (Streaming Replication, Logical Replication)Always On Availability Groups, Replication (Transactional, Merge, Snapshot)Data Guard, GoldenGate
Compression`mysqldumpgzip, mysqlbackup —compress`pg_dump -F c, external compression toolsBackup compression option in T-SQL
Encryptionmysqldump --ssl, Transparent Data Encryption (TDE)pg_dump with external encryption tools, Transparent Data Encryption (TDE) (PostgreSQL 15+)Transparent Data Encryption (TDE), Backup encryption option in T-SQLTransparent Data Encryption (TDE), RMAN encryption options
Backup Verificationmysqlcheck, restore to test environmentpg_verifybackup, restore to test environmentRESTORE VERIFYONLY, restore to test environmentRMAN VALIDATE command, restore to test environment
Enterprise FeaturesMySQL Enterprise Backup, MySQL HeatWaveEnterpriseDB Postgres Enterprise Manager (PEM)SQL Server Enterprise Edition features (e.g., online indexing, advanced auditing)Oracle Enterprise Edition features (e.g., partitioning, RAC, Data Guard)
This cheatsheet provides a comprehensive overview of backup and recovery strategies in SQL databases. Remember to adapt these guidelines to your specific environment and requirements. Always test your backup and recovery procedures to ensure they are effective.