Database Management SystemsBackup Implementation

Backup Implementation Strategies

LevelIntermediate

Duration60 mins

TopicBackup Implementation

4 / 5

Backup Tools

The Landscape of Database Backup Tools

The database backup ecosystem spans a wide spectrum—from lightweight command-line utilities bundled with databases to sophisticated enterprise platforms managing petabytes across global infrastructure. Selecting the right tools is crucial: underpowered tools create operational burden and reliability risks, while over-engineered solutions add unnecessary complexity and cost.

Effective tool selection requires understanding your requirements across multiple dimensions: database platforms, consistency needs, performance expectations, retention policies, regulatory compliance, and operational capabilities. This page provides a comprehensive survey of backup tools, from native database utilities to enterprise platforms, enabling you to make informed selections for your environment.

What You Will Learn

By the end of this page, you will understand the categories of backup tools available, master the capabilities of native database backup utilities, evaluate enterprise backup platforms, recognize emerging cloud-native solutions, and be able to select appropriate tools for different scenarios.

Categories of Backup Tools

Backup tools can be categorized along several dimensions. Understanding these categories helps frame tool evaluation and selection.

By Scope

Native Database Tools: Utilities provided by the database vendor, optimized for that specific database
Multi-Platform Tools: Enterprise solutions supporting multiple database types
General Purpose Tools: File system backup tools adapted for database use

By Backup Type

Physical Backup Tools: Copy raw database files (data files, logs)
Logical Backup Tools: Export data as SQL or structured formats
Hybrid Tools: Support both physical and logical backup

By Deployment Model

On-Premises Tools: Run in your data center
Cloud-Native Tools: Designed for cloud database services
SaaS Backup Services: Managed backup as a service

Converting Mermaid diagram...

Tool Category Selection Criteria
Category	Best For	Considerations
Native Database Tools	Single-platform shops, maximum performance, direct control	Requires scripting for automation, limited cross-platform
Enterprise Platforms	Multi-database, compliance requirements, centralized management	Cost, complexity, vendor dependency
Cloud-Native	Cloud databases, managed services, minimal operations	Vendor lock-in, limited customization
General Purpose (rsync, tar)	Cold backup, simple requirements, Linux expertise	Requires careful coordination with database

PostgreSQL Native Tools

PostgreSQL provides a robust set of native backup tools, each suited for different use cases.

pg_dump - Logical Backup

The standard tool for logical backups, producing SQL or custom archive formats:

Capabilities:

Exports database objects as SQL statements
Selective backup (specific schemas, tables)
Multiple output formats (plain SQL, custom, directory, tar)
Parallel dump for large databases
Cross-version compatibility

pg_dump_examples.sh
Bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
#!/bin/bash
# pg_dump - PostgreSQL Logical Backup Tool
 
# Basic SQL dump
pg_dump mydb > /backup/mydb.sql
 
# Custom format (compressed, parallel restore)
pg_dump -Fc mydb > /backup/mydb.dump
 
# Directory format with parallel dump (4 jobs)
pg_dump -Fd -j 4 mydb -f /backup/mydb_dir/
 
# Tar format
pg_dump -Ft mydb > /backup/mydb.tar
 
# Selective backup - specific schema
pg_dump -n sales_schema mydb > /backup/sales_schema.sql
 
# Selective backup - specific tables
pg_dump -t 'orders*' -t customers mydb > /backup/order_tables.sql
 
# Exclude large tables
pg_dump --exclude-table=audit_log --exclude-table=session_data mydb > /backup/mydb_small.sql
 
# Schema only (structure, no data)
pg_dump -s mydb > /backup/mydb_schema.sql
 
# Data only (use with existing schema)
pg_dump -a mydb > /backup/mydb_data.sql
 
# With compression (gzip via pipe)
pg_dump mydb | gzip > /backup/mydb.sql.gz
 
# Consistent dump with serializable isolation
pg_dump --serializable-deferrable mydb > /backup/mydb_consistent.sql
 
# Include BLOB data
pg_dump -b mydb > /backup/mydb_with_blobs.sql
 
# Generate INSERT statements instead of COPY
pg_dump --inserts mydb > /backup/mydb_inserts.sql
 
# All databases (pg_dumpall)
pg_dumpall > /backup/all_databases.sql
 
# Roles and tablespaces only
pg_dumpall --globals-only > /backup/globals.sql

pg_basebackup - Physical Backup

The standard tool for physical (file-level) backup:

Capabilities:

Streaming replication protocol for consistent backup
Includes WAL needed for consistency
Supports compression and parallel streaming
Can create standby server directly

pg_basebackup_examples.sh
Bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
#!/bin/bash
# pg_basebackup - PostgreSQL Physical Backup Tool
 
# Basic backup with streamed WAL
pg_basebackup -D /backup/base -X stream -P
 
# Tar format with compression
pg_basebackup -D /backup -Ft -z -X stream
 
# With specific host/port/user
pg_basebackup -h db.example.com -p 5432 -U replication_user -D /backup
 
# Fast checkpoint (minimize wait time)
pg_basebackup -D /backup --checkpoint=fast -X stream
 
# With progress reporting
pg_basebackup -D /backup -X stream -P -v
 
# Maximum rate limiting (100MB/s) to limit I/O impact
pg_basebackup -D /backup -X stream -r 100M
 
# Create replication slot for backup (prevents WAL removal)
pg_basebackup -D /backup -X stream -S backup_slot -C
 
# With manifest for verification (PostgreSQL 13+)
pg_basebackup -D /backup -X stream --manifest-checksums=SHA256
 
# Incremental backup preparation (PostgreSQL 17+)
# Create manifest-only for incremental base
pg_basebackup -D /backup/full --manifest-only -X stream
 
# Verify backup integrity (PostgreSQL 13+)
pg_verifybackup /backup/base
 
# Tar to stdout (for piping to storage)
pg_basebackup -D - -Ft -X stream | aws s3 cp - s3://bucket/backup.tar
 
# Create standby directly
pg_basebackup -D /var/lib/postgresql/standby -X stream -R

pgBackRest - Enterprise-Grade PostgreSQL Backup

pgBackRest is a feature-rich backup tool specifically for PostgreSQL, offering capabilities beyond native tools:

Key Features:

Parallel backup and restore
Full, differential, and incremental backup
Local and remote (via SSH or TLS) operation
Compression with multiple algorithms (gzip, lz4, zstd)
Encryption (AES-256)
Backup verification and consistency checks
Repository management with multiple storage backends (local, S3, Azure, GCS)

pgbackrest_examples.sh
Bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
#!/bin/bash
# pgBackRest - Enterprise PostgreSQL Backup
 
# Configuration (/etc/pgbackrest/pgbackrest.conf)
cat > /etc/pgbackrest/pgbackrest.conf <<EOF
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=4
compress-type=zstd
compress-level=3
 
[main]
pg1-path=/var/lib/postgresql/14/main
pg1-port=5432
 
# Optional: S3 storage
# repo1-type=s3
# repo1-s3-bucket=my-backup-bucket
# repo1-s3-endpoint=s3.amazonaws.com
# repo1-s3-region=us-east-1
# repo1-s3-key=ACCESS_KEY
# repo1-s3-key-secret=SECRET_KEY
EOF
 
# Create stanza (initialize backup configuration)
pgbackrest --stanza=main stanza-create
 
# Full backup
pgbackrest --stanza=main --type=full backup
 
# Differential backup (changes since last full)
pgbackrest --stanza=main --type=diff backup
 
# Incremental backup (changes since last backup of any type)
pgbackrest --stanza=main --type=incr backup
 
# Check backup integrity
pgbackrest --stanza=main check
 
# Verify backup (read and validate all files)
pgbackrest --stanza=main --set=20240115-120000F verify
 
# List backups
pgbackrest --stanza=main info
 
# Restore to original location
sudo systemctl stop postgresql
pgbackrest --stanza=main --delta restore
sudo systemctl start postgresql
 
# Restore to specific time (PITR)
pgbackrest --stanza=main --delta \
    --type=time "--target=2024-01-15 10:30:00" \
    --target-action=promote restore
 
# Expire old backups (based on retention policy)
pgbackrest --stanza=main expire

MySQL/MariaDB Backup Tools

MySQL ecosystem offers several backup tools, from simple logical dumps to sophisticated physical backup solutions.

mysqldump - Logical Backup

The classic MySQL backup tool for logical exports:

mysqldump_examples.sh
Bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
#!/bin/bash
# mysqldump - MySQL Logical Backup Tool
 
# Basic database dump
mysqldump -u root -p mydb > /backup/mydb.sql
 
# All databases
mysqldump -u root -p --all-databases > /backup/all_dbs.sql
 
# Consistent backup with single transaction (InnoDB)
mysqldump -u root -p --single-transaction mydb > /backup/mydb_consistent.sql
 
# With routines and triggers
mysqldump -u root -p --routines --triggers mydb > /backup/mydb_full.sql
 
# Include events
mysqldump -u root -p --events mydb > /backup/mydb_with_events.sql
 
# Specific tables
mysqldump -u root -p mydb orders customers > /backup/mydb_tables.sql
 
# Where clause filter
mysqldump -u root -p mydb orders --where="order_date > '2024-01-01'" > /backup/recent_orders.sql
 
# Schema only
mysqldump -u root -p --no-data mydb > /backup/mydb_schema.sql
 
# Data only
mysqldump -u root -p --no-create-info mydb > /backup/mydb_data.sql
 
# With compression
mysqldump -u root -p mydb | gzip > /backup/mydb.sql.gz
 
# Quick dump (skip sorting, less memory)
mysqldump -u root -p --quick mydb > /backup/mydb_quick.sql
 
# Extended insert for faster restore
mysqldump -u root -p --extended-insert mydb > /backup/mydb_ext.sql
 
# Lock all tables for MyISAM consistency
mysqldump -u root -p --lock-all-tables mydb > /backup/mydb_locked.sql
 
# Master data for replication setup
mysqldump -u root -p --single-transaction --master-data=2 mydb > /backup/mydb_repl.sql
 
# GTID compatible
mysqldump -u root -p --single-transaction --set-gtid-purged=AUTO mydb > /backup/mydb_gtid.sql

Percona XtraBackup - Physical Backup

The industry-standard open-source tool for MySQL physical backup:

Key Features:

Non-blocking backup for InnoDB
Point-in-time recovery capability
Incremental backup support
Compression and encryption
Streaming to remote servers

xtrabackup_examples.sh
Bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
#!/bin/bash
# Percona XtraBackup - MySQL Physical Backup Tool
 
# Full backup
xtrabackup --backup --user=root --password=secret \
    --target-dir=/backup/full
 
# With compression (qpress)
xtrabackup --backup --compress --compress-threads=4 \
    --user=root --password=secret --target-dir=/backup/compressed
 
# Parallel backup threads
xtrabackup --backup --parallel=4 --user=root --password=secret \
    --target-dir=/backup/parallel
 
# Prepare backup (required before restore!)
xtrabackup --prepare --target-dir=/backup/full
 
# Incremental backup (based on full)
xtrabackup --backup --user=root --password=secret \
    --incremental-basedir=/backup/full \
    --target-dir=/backup/incr1
 
# Second incremental (based on first incremental)
xtrabackup --backup --user=root --password=secret \
    --incremental-basedir=/backup/incr1 \
    --target-dir=/backup/incr2
 
# Prepare incremental chain
xtrabackup --prepare --apply-log-only --target-dir=/backup/full
xtrabackup --prepare --apply-log-only --target-dir=/backup/full \
    --incremental-dir=/backup/incr1
xtrabackup --prepare --target-dir=/backup/full \
    --incremental-dir=/backup/incr2
 
# Streaming backup to another server
xtrabackup --backup --stream=xbstream --user=root --password=secret | \
    ssh remote-server "xbstream -x -C /backup/remote"
 
# Streaming with compression
xtrabackup --backup --stream=xbstream --compress --user=root --password=secret | \
    ssh remote-server "xbstream -x -C /backup/remote"
 
# Restore procedure
sudo systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql
 
# Validate backup
xtrabackup --validate-backup --target-dir=/backup/full

MySQL Enterprise Backup

Oracle's commercial backup solution for MySQL (requires MySQL Enterprise subscription):

Key Features:

Hot backup of InnoDB, MyISAM, and other storage engines
Incremental and differential backup
Point-in-time recovery
Encryption and compression
Integration with MySQL Enterprise Monitor

MariaDB Backup

MariaDB includes Mariabackup, a fork of XtraBackup optimized for MariaDB. Usage is nearly identical to XtraBackup. For MariaDB 10.2+, use Mariabackup instead of XtraBackup for best compatibility with MariaDB-specific features like encrypted tables and compression.

Oracle and SQL Server Native Tools

Enterprise databases include sophisticated native backup tools designed for mission-critical deployments.

Oracle RMAN (Recovery Manager)

Oracle's comprehensive backup and recovery tool:

Key Features:

Block-level corruption detection
Incremental forever (block change tracking)
Backup optimization (skip unchanged blocks)
Integration with Data Guard
Recovery catalog for centralized management
Encryption and compression
Cross-platform backup and restore

rman_examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- Oracle RMAN - Recovery Manager Examples
 
-- Connect to RMAN
-- $ rman target /
-- $ rman target sys/password@orcl catalog rcat_user/password@rcat
 
-- Configure RMAN settings
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT';
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES256';
 
-- Full database backup with compression
BACKUP AS COMPRESSED BACKUPSET DATABASE 
    PLUS ARCHIVELOG 
    TAG 'DAILY_FULL';
 
-- Incremental backup strategy (Level 0 = base, Level 1 = changes)
-- Level 0 - full baseline
BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'WEEKLY_LEVEL0';
 
-- Level 1 - differential (changes since last level 0 or 1)
BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'DAILY_LEVEL1';
 
-- Level 1 - cumulative (changes since last level 0 only)
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'DAILY_CUMUL';
 
-- Backup to specific location
BACKUP AS COMPRESSED BACKUPSET 
    FORMAT '/backup/oracle/%d_%T_%s_%p.bkp'
    DATABASE PLUS ARCHIVELOG;
 
-- Backup tablespace
BACKUP TABLESPACE users, data TAG 'TS_BACKUP';
 
-- Backup datafile
BACKUP DATAFILE 4 TAG 'DATAFILE_BACKUP';
 
-- Backup archived logs
BACKUP ARCHIVELOG ALL DELETE INPUT;
 
-- Validate backup (check for corruption)
VALIDATE DATABASE;
VALIDATE BACKUPSET TAG 'DAILY_FULL';
 
-- List backups
LIST BACKUP SUMMARY;
LIST BACKUP OF DATABASE;
 
-- Restore and recover
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
 
-- Point-in-time recovery
RUN {
    SET UNTIL TIME "TO_DATE('2024-01-15 10:30:00','YYYY-MM-DD HH24:MI:SS')";
    RESTORE DATABASE;
    RECOVER DATABASE;
}
ALTER DATABASE OPEN RESETLOGS;
 
-- Cross-check and delete obsolete
CROSSCHECK BACKUP;
DELETE OBSOLETE;
DELETE EXPIRED BACKUP;

SQL Server BACKUP Command

SQL Server's native backup capabilities are powerful and well-integrated:

Key Features:

Full, differential, and transaction log backups
Native compression
Backup encryption
Mirror to multiple destinations
Backup striping for large databases
VSS integration for application-consistent snapshots

sqlserver_backup_examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
-- SQL Server BACKUP Examples
 
-- Full database backup with compression
BACKUP DATABASE Production
TO DISK = 'D:\Backup\Production_Full.bak'
WITH 
    COMPRESSION,
    CHECKSUM,
    STATS = 10,
    NAME = 'Production Full Backup',
    DESCRIPTION = 'Daily full backup';
 
-- Differential backup (changes since last full)
BACKUP DATABASE Production
TO DISK = 'D:\Backup\Production_Diff.bak'
WITH 
    DIFFERENTIAL,
    COMPRESSION,
    CHECKSUM,
    STATS = 10;
 
-- Transaction log backup
BACKUP LOG Production
TO DISK = 'D:\Backup\Production_Log.trn'
WITH 
    COMPRESSION,
    CHECKSUM,
    STATS = 10;
 
-- Striped backup to multiple files (parallel)
BACKUP DATABASE Production
TO DISK = 'D:\Backup\Prod_stripe1.bak',
   DISK = 'E:\Backup\Prod_stripe2.bak',
   DISK = 'F:\Backup\Prod_stripe3.bak',
   DISK = 'G:\Backup\Prod_stripe4.bak'
WITH 
    COMPRESSION,
    CHECKSUM,
    MAXTRANSFERSIZE = 4194304,
    BUFFERCOUNT = 50;
 
-- Encrypted backup
BACKUP DATABASE Production
TO DISK = 'D:\Backup\Production_Encrypted.bak'
WITH 
    COMPRESSION,
    ENCRYPTION(
        ALGORITHM = AES_256,
        SERVER CERTIFICATE = BackupCertificate
    );
 
-- Mirror backup to two destinations
BACKUP DATABASE Production
TO DISK = 'D:\Backup\Production.bak'
MIRROR TO DISK = '\\backupserver\share\Production.bak'
WITH 
    FORMAT,
    COMPRESSION;
 
-- Copy-only backup (doesn't affect backup chain)
BACKUP DATABASE Production
TO DISK = 'D:\Backup\Production_CopyOnly.bak'
WITH 
    COPY_ONLY,
    COMPRESSION;
 
-- Verify backup integrity
RESTORE VERIFYONLY
FROM DISK = 'D:\Backup\Production_Full.bak'
WITH CHECKSUM;
 
-- View backup history
SELECT 
    s.database_name,
    s.backup_start_date,
    s.backup_finish_date,
    DATEDIFF(MINUTE, s.backup_start_date, s.backup_finish_date) AS Duration_Minutes,
    s.backup_size / 1048576 AS Size_MB,
    s.compressed_backup_size / 1048576 AS Compressed_MB,
    m.physical_device_name
FROM msdb.dbo.backupset s
JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = 'Production'
ORDER BY s.backup_start_date DESC;
 
-- Restore with point-in-time recovery
RESTORE DATABASE Production
FROM DISK = 'D:\Backup\Production_Full.bak'
WITH NORECOVERY;
 
RESTORE LOG Production
FROM DISK = 'D:\Backup\Production_Log1.trn'
WITH NORECOVERY;
 
RESTORE LOG Production
FROM DISK = 'D:\Backup\Production_Log2.trn'
WITH STOPAT = '2024-01-15T10:30:00',
     RECOVERY;

Enterprise Backup Platforms

Enterprise backup platforms provide centralized management, multi-platform support, and advanced features beyond native tools.

Common Enterprise Backup Platforms

Enterprise Backup Platform Comparison
Platform	Strengths	Database Support	Key Differentiator
Veeam Backup & Replication	VM integration, ransomware protection, instant recovery	SQL Server, Oracle, PostgreSQL, MySQL	Fastest recovery times, excellent UI/UX
Commvault Complete	Comprehensive coverage, deduplication, cloud mobility	All major databases + SAP, ERP	Single platform for all data protection
Veritas NetBackup	Scale, enterprise track record, AIX/HP-UX support	All major databases + mainframe	Largest install base, proven at scale
Cohesity DataProtect	Modern architecture, instant access, API-first	SQL Server, Oracle, PostgreSQL, MongoDB	Web-scale architecture, analytics
Dell EMC PowerProtect	Hardware integration, APEX cloud, deduplication	All major databases	Integration with Dell storage ecosystem
Rubrik	SLA automation, multi-cloud, immutability	SQL Server, Oracle, PostgreSQL, MySQL	Policy-based, zero-trust architecture

Evaluation Criteria for Enterprise Platforms

When evaluating enterprise backup platforms for database protection, consider:

1. Database Agent Quality

Native or log-based capture?
Impact on database performance?
Support for database-specific features (PITR, standby creation)?

2. Consistency Guarantees

Application-consistent backups?
Multi-database coordination?
Quiesce and snapshot integration?

3. Recovery Capabilities

Point-in-time recovery support?
Granular recovery (single table, single transaction)?
Instant access/mounting capabilities?

4. Operational Integration

API availability for automation?
Monitoring and alerting?
Compliance reporting?

5. Scale and Cost

Licensing model (capacity, instances, sockets)?
Storage efficiency (deduplication, compression)?
Multi-site replication?

Agent vs. Agentless

Enterprise platforms offer both agent-based (installed on database server) and agentless (via APIs, snapshots) backup. Agent-based typically provides better granularity and database awareness but requires more management. Agentless is simpler but may have limitations. Choose based on your recovery requirements and operational preferences.

Cloud-Native Backup Solutions

Cloud databases typically include built-in backup capabilities, with additional services for enhanced protection.

AWS Database Backup

RDS Automated Backups:

Daily snapshots with transaction log backup
Point-in-time recovery up to 35 days
Encrypted by default with KMS

AWS Backup (centralized):

Policy-based protection across services
Cross-account and cross-region copy
Compliance reporting

Azure Database Backup

Azure SQL Backup:

Geo-redundant storage by default
Long-term retention (up to 10 years)
Point-in-time restore

Google Cloud Backup

Cloud SQL Backup:

On-demand and automated backups
Point-in-time recovery
Cross-region backup

cloud_backup_examples.sh
Bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
#!/bin/bash
# Cloud-Native Backup Management Examples
 
# =====================================
# AWS RDS
# =====================================
 
# Create manual snapshot
aws rds create-db-snapshot \
    --db-instance-identifier mydb \
    --db-snapshot-identifier mydb-manual-$(date +%Y%m%d)
 
# Copy snapshot to another region (DR)
aws rds copy-db-snapshot \
    --source-db-snapshot-identifier arn:aws:rds:us-east-1:123456789:snapshot:mydb-manual \
    --target-db-snapshot-identifier mydb-dr-copy \
    --source-region us-east-1 \
    --region us-west-2
 
# Restore to point in time
aws rds restore-db-instance-to-point-in-time \
    --source-db-instance-identifier mydb \
    --target-db-instance-identifier mydb-recovered \
    --restore-time "2024-01-15T10:30:00Z" \
    --db-instance-class db.r5.large
 
# List snapshots
aws rds describe-db-snapshots \
    --db-instance-identifier mydb \
    --query 'DBSnapshots[*].[DBSnapshotIdentifier,SnapshotCreateTime,Status]' \
    --output table
 
# AWS Backup - Create backup plan
aws backup create-backup-plan --backup-plan '{
    "BackupPlanName": "DailyDBBackup",
    "BackupPlanRule": [{
        "RuleName": "DailyRule",
        "TargetBackupVaultName": "Default",
        "ScheduleExpression": "cron(0 5 * * ? *)",
        "StartWindowMinutes": 60,
        "CompletionWindowMinutes": 180,
        "Lifecycle": {
            "DeleteAfterDays": 30
        }
    }]
}'
 
# =====================================
# Azure SQL
# =====================================
 
# Create database copy (snapshot)
az sql db copy \
    --resource-group myResourceGroup \
    --server myserver \
    --name mydatabase \
    --dest-server myserver \
    --dest-name mydatabase-copy
 
# Point-in-time restore
az sql db restore \
    --resource-group myResourceGroup \
    --server myserver \
    --name mydatabase \
    --dest-name mydatabase-restored \
    --time "2024-01-15T10:30:00Z"
 
# Configure long-term retention
az sql db ltr-policy set \
    --resource-group myResourceGroup \
    --server myserver \
    --database mydatabase \
    --weekly-retention P4W \
    --monthly-retention P12M \
    --yearly-retention P5Y \
    --week-of-year 1
 
# =====================================
# Google Cloud SQL
# =====================================
 
# Create on-demand backup
gcloud sql backups create \
    --instance=myinstance \
    --description="Manual backup $(date +%Y-%m-%d)"
 
# Restore from backup
gcloud sql backups restore BACKUP_ID \
    --restore-instance=myinstance
 
# Clone instance to point in time
gcloud sql instances clone myinstance myinstance-clone \
    --point-in-time="2024-01-15T10:30:00Z"
 
# List backups
gcloud sql backups list --instance=myinstance

Cloud Backup Limitations

Cloud-native backups are convenient but may have limitations: (1) Limited retention periods for automated backups, (2) Cross-region copies incur additional costs, (3) Restore may create new instances rather than in-place recovery, (4) Less granular recovery options than native tools. Evaluate whether cloud backups alone meet your RPO/RTO requirements.

Tool Selection Guidelines

Selecting the right backup tools requires balancing multiple factors. Use this framework to guide your decisions:

Tool Selection Matrix
Scenario	Recommended Tools	Rationale
Single PostgreSQL database, small team	pg_dump + pg_basebackup, scripts	Native tools are free, reliable, sufficient
PostgreSQL with serious RPO requirements	pgBackRest	PITR, encryption, verification, incremental
MySQL/MariaDB production	XtraBackup + mysqldump	Physical for DR, logical for portability
Oracle enterprise	RMAN + Data Guard	Best-in-class for Oracle, tightly integrated
SQL Server enterprise	Native BACKUP + enterprise platform	Strong native tools, supplement with management
Multi-database environment	Enterprise platform (Veeam, Commvault)	Unified management, cross-platform
Cloud-native (RDS, Cloud SQL)	Cloud-native + cross-region copy	Native integration, minimal operations
Hybrid cloud	Enterprise platform with cloud support	Consistent policy across environments

Key Selection Criteria

•RPO Requirements — Determine backup frequency needs; affects tool choices
•RTO Requirements — Fast recovery may require physical backup or instant access
•Database Platform(s) — Match tools to your specific databases
•Operational Expertise — Choose tools your team can operate effectively
•Integration Needs — APIs, monitoring, automation requirements
•Budget — Balance cost against capabilities; native tools are often sufficient
•Compliance — Encryption, audit logging, retention policy enforcement
•Scale — Tool must handle your data volumes and growth

Page Complete

You now have a comprehensive understanding of the backup tool landscape—from native database utilities to enterprise platforms and cloud-native solutions. This knowledge enables you to select appropriate tools for your environment and requirements. Next, we'll explore backup storage strategies, covering storage media, retention, and architectural considerations.

4 / 5

Loading learning content...

Database Management SystemsBackup Implementation

Backup Implementation Strategies

LevelIntermediate

Duration60 mins

TopicBackup Implementation

4 / 5

Backup Tools

The Landscape of Database Backup Tools

What You Will Learn

Categories of Backup Tools

Backup tools can be categorized along several dimensions. Understanding these categories helps frame tool evaluation and selection.

By Scope

Native Database Tools: Utilities provided by the database vendor, optimized for that specific database
Multi-Platform Tools: Enterprise solutions supporting multiple database types
General Purpose Tools: File system backup tools adapted for database use

By Backup Type

Physical Backup Tools: Copy raw database files (data files, logs)
Logical Backup Tools: Export data as SQL or structured formats
Hybrid Tools: Support both physical and logical backup

By Deployment Model

On-Premises Tools: Run in your data center
Cloud-Native Tools: Designed for cloud database services
SaaS Backup Services: Managed backup as a service

Converting Mermaid diagram...

Tool Category Selection Criteria
Category	Best For	Considerations
Native Database Tools	Single-platform shops, maximum performance, direct control	Requires scripting for automation, limited cross-platform
Enterprise Platforms	Multi-database, compliance requirements, centralized management	Cost, complexity, vendor dependency
Cloud-Native	Cloud databases, managed services, minimal operations	Vendor lock-in, limited customization
General Purpose (rsync, tar)	Cold backup, simple requirements, Linux expertise	Requires careful coordination with database

PostgreSQL Native Tools

PostgreSQL provides a robust set of native backup tools, each suited for different use cases.

pg_dump - Logical Backup

The standard tool for logical backups, producing SQL or custom archive formats:

Capabilities:

Exports database objects as SQL statements
Selective backup (specific schemas, tables)
Multiple output formats (plain SQL, custom, directory, tar)
Parallel dump for large databases
Cross-version compatibility

pg_dump_examples.sh
Bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
#!/bin/bash
# pg_dump - PostgreSQL Logical Backup Tool
 
# Basic SQL dump
pg_dump mydb > /backup/mydb.sql
 
# Custom format (compressed, parallel restore)
pg_dump -Fc mydb > /backup/mydb.dump
 
# Directory format with parallel dump (4 jobs)
pg_dump -Fd -j 4 mydb -f /backup/mydb_dir/
 
# Tar format
pg_dump -Ft mydb > /backup/mydb.tar
 
# Selective backup - specific schema
pg_dump -n sales_schema mydb > /backup/sales_schema.sql
 
# Selective backup - specific tables
pg_dump -t 'orders*' -t customers mydb > /backup/order_tables.sql
 
# Exclude large tables
pg_dump --exclude-table=audit_log --exclude-table=session_data mydb > /backup/mydb_small.sql
 
# Schema only (structure, no data)
pg_dump -s mydb > /backup/mydb_schema.sql
 
# Data only (use with existing schema)
pg_dump -a mydb > /backup/mydb_data.sql
 
# With compression (gzip via pipe)
pg_dump mydb | gzip > /backup/mydb.sql.gz
 
# Consistent dump with serializable isolation
pg_dump --serializable-deferrable mydb > /backup/mydb_consistent.sql
 
# Include BLOB data
pg_dump -b mydb > /backup/mydb_with_blobs.sql
 
# Generate INSERT statements instead of COPY
pg_dump --inserts mydb > /backup/mydb_inserts.sql
 
# All databases (pg_dumpall)
pg_dumpall > /backup/all_databases.sql
 
# Roles and tablespaces only
pg_dumpall --globals-only > /backup/globals.sql

pg_basebackup - Physical Backup

The standard tool for physical (file-level) backup:

Capabilities:

Streaming replication protocol for consistent backup
Includes WAL needed for consistency
Supports compression and parallel streaming
Can create standby server directly

pg_basebackup_examples.sh
Bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
#!/bin/bash
# pg_basebackup - PostgreSQL Physical Backup Tool
 
# Basic backup with streamed WAL
pg_basebackup -D /backup/base -X stream -P
 
# Tar format with compression
pg_basebackup -D /backup -Ft -z -X stream
 
# With specific host/port/user
pg_basebackup -h db.example.com -p 5432 -U replication_user -D /backup
 
# Fast checkpoint (minimize wait time)
pg_basebackup -D /backup --checkpoint=fast -X stream
 
# With progress reporting
pg_basebackup -D /backup -X stream -P -v
 
# Maximum rate limiting (100MB/s) to limit I/O impact
pg_basebackup -D /backup -X stream -r 100M
 
# Create replication slot for backup (prevents WAL removal)
pg_basebackup -D /backup -X stream -S backup_slot -C
 
# With manifest for verification (PostgreSQL 13+)
pg_basebackup -D /backup -X stream --manifest-checksums=SHA256
 
# Incremental backup preparation (PostgreSQL 17+)
# Create manifest-only for incremental base
pg_basebackup -D /backup/full --manifest-only -X stream
 
# Verify backup integrity (PostgreSQL 13+)
pg_verifybackup /backup/base
 
# Tar to stdout (for piping to storage)
pg_basebackup -D - -Ft -X stream | aws s3 cp - s3://bucket/backup.tar
 
# Create standby directly
pg_basebackup -D /var/lib/postgresql/standby -X stream -R

pgBackRest - Enterprise-Grade PostgreSQL Backup

pgBackRest is a feature-rich backup tool specifically for PostgreSQL, offering capabilities beyond native tools:

Key Features:

Parallel backup and restore
Full, differential, and incremental backup
Local and remote (via SSH or TLS) operation
Compression with multiple algorithms (gzip, lz4, zstd)
Encryption (AES-256)
Backup verification and consistency checks
Repository management with multiple storage backends (local, S3, Azure, GCS)

pgbackrest_examples.sh
Bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
#!/bin/bash
# pgBackRest - Enterprise PostgreSQL Backup
 
# Configuration (/etc/pgbackrest/pgbackrest.conf)
cat > /etc/pgbackrest/pgbackrest.conf <<EOF
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=4
compress-type=zstd
compress-level=3
 
[main]
pg1-path=/var/lib/postgresql/14/main
pg1-port=5432
 
# Optional: S3 storage
# repo1-type=s3
# repo1-s3-bucket=my-backup-bucket
# repo1-s3-endpoint=s3.amazonaws.com
# repo1-s3-region=us-east-1
# repo1-s3-key=ACCESS_KEY
# repo1-s3-key-secret=SECRET_KEY
EOF
 
# Create stanza (initialize backup configuration)
pgbackrest --stanza=main stanza-create
 
# Full backup
pgbackrest --stanza=main --type=full backup
 
# Differential backup (changes since last full)
pgbackrest --stanza=main --type=diff backup
 
# Incremental backup (changes since last backup of any type)
pgbackrest --stanza=main --type=incr backup
 
# Check backup integrity
pgbackrest --stanza=main check
 
# Verify backup (read and validate all files)
pgbackrest --stanza=main --set=20240115-120000F verify
 
# List backups
pgbackrest --stanza=main info
 
# Restore to original location
sudo systemctl stop postgresql
pgbackrest --stanza=main --delta restore
sudo systemctl start postgresql
 
# Restore to specific time (PITR)
pgbackrest --stanza=main --delta \
    --type=time "--target=2024-01-15 10:30:00" \
    --target-action=promote restore
 
# Expire old backups (based on retention policy)
pgbackrest --stanza=main expire

MySQL/MariaDB Backup Tools

MySQL ecosystem offers several backup tools, from simple logical dumps to sophisticated physical backup solutions.

mysqldump - Logical Backup

The classic MySQL backup tool for logical exports:

mysqldump_examples.sh
Bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
#!/bin/bash
# mysqldump - MySQL Logical Backup Tool
 
# Basic database dump
mysqldump -u root -p mydb > /backup/mydb.sql
 
# All databases
mysqldump -u root -p --all-databases > /backup/all_dbs.sql
 
# Consistent backup with single transaction (InnoDB)
mysqldump -u root -p --single-transaction mydb > /backup/mydb_consistent.sql
 
# With routines and triggers
mysqldump -u root -p --routines --triggers mydb > /backup/mydb_full.sql
 
# Include events
mysqldump -u root -p --events mydb > /backup/mydb_with_events.sql
 
# Specific tables
mysqldump -u root -p mydb orders customers > /backup/mydb_tables.sql
 
# Where clause filter
mysqldump -u root -p mydb orders --where="order_date > '2024-01-01'" > /backup/recent_orders.sql
 
# Schema only
mysqldump -u root -p --no-data mydb > /backup/mydb_schema.sql
 
# Data only
mysqldump -u root -p --no-create-info mydb > /backup/mydb_data.sql
 
# With compression
mysqldump -u root -p mydb | gzip > /backup/mydb.sql.gz
 
# Quick dump (skip sorting, less memory)
mysqldump -u root -p --quick mydb > /backup/mydb_quick.sql
 
# Extended insert for faster restore
mysqldump -u root -p --extended-insert mydb > /backup/mydb_ext.sql
 
# Lock all tables for MyISAM consistency
mysqldump -u root -p --lock-all-tables mydb > /backup/mydb_locked.sql
 
# Master data for replication setup
mysqldump -u root -p --single-transaction --master-data=2 mydb > /backup/mydb_repl.sql
 
# GTID compatible
mysqldump -u root -p --single-transaction --set-gtid-purged=AUTO mydb > /backup/mydb_gtid.sql

Percona XtraBackup - Physical Backup

The industry-standard open-source tool for MySQL physical backup:

Key Features:

Non-blocking backup for InnoDB
Point-in-time recovery capability
Incremental backup support
Compression and encryption
Streaming to remote servers

xtrabackup_examples.sh
Bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
#!/bin/bash
# Percona XtraBackup - MySQL Physical Backup Tool
 
# Full backup
xtrabackup --backup --user=root --password=secret \
    --target-dir=/backup/full
 
# With compression (qpress)
xtrabackup --backup --compress --compress-threads=4 \
    --user=root --password=secret --target-dir=/backup/compressed
 
# Parallel backup threads
xtrabackup --backup --parallel=4 --user=root --password=secret \
    --target-dir=/backup/parallel
 
# Prepare backup (required before restore!)
xtrabackup --prepare --target-dir=/backup/full
 
# Incremental backup (based on full)
xtrabackup --backup --user=root --password=secret \
    --incremental-basedir=/backup/full \
    --target-dir=/backup/incr1
 
# Second incremental (based on first incremental)
xtrabackup --backup --user=root --password=secret \
    --incremental-basedir=/backup/incr1 \
    --target-dir=/backup/incr2
 
# Prepare incremental chain
xtrabackup --prepare --apply-log-only --target-dir=/backup/full
xtrabackup --prepare --apply-log-only --target-dir=/backup/full \
    --incremental-dir=/backup/incr1
xtrabackup --prepare --target-dir=/backup/full \
    --incremental-dir=/backup/incr2
 
# Streaming backup to another server
xtrabackup --backup --stream=xbstream --user=root --password=secret | \
    ssh remote-server "xbstream -x -C /backup/remote"
 
# Streaming with compression
xtrabackup --backup --stream=xbstream --compress --user=root --password=secret | \
    ssh remote-server "xbstream -x -C /backup/remote"
 
# Restore procedure
sudo systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql
 
# Validate backup
xtrabackup --validate-backup --target-dir=/backup/full

MySQL Enterprise Backup

Oracle's commercial backup solution for MySQL (requires MySQL Enterprise subscription):

Key Features:

Hot backup of InnoDB, MyISAM, and other storage engines
Incremental and differential backup
Point-in-time recovery
Encryption and compression
Integration with MySQL Enterprise Monitor

MariaDB Backup

Oracle and SQL Server Native Tools

Enterprise databases include sophisticated native backup tools designed for mission-critical deployments.

Oracle RMAN (Recovery Manager)

Oracle's comprehensive backup and recovery tool:

Key Features:

Block-level corruption detection
Incremental forever (block change tracking)
Backup optimization (skip unchanged blocks)
Integration with Data Guard
Recovery catalog for centralized management
Encryption and compression
Cross-platform backup and restore

rman_examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- Oracle RMAN - Recovery Manager Examples
 
-- Connect to RMAN
-- $ rman target /
-- $ rman target sys/password@orcl catalog rcat_user/password@rcat
 
-- Configure RMAN settings
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT';
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES256';
 
-- Full database backup with compression
BACKUP AS COMPRESSED BACKUPSET DATABASE 
    PLUS ARCHIVELOG 
    TAG 'DAILY_FULL';
 
-- Incremental backup strategy (Level 0 = base, Level 1 = changes)
-- Level 0 - full baseline
BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'WEEKLY_LEVEL0';
 
-- Level 1 - differential (changes since last level 0 or 1)
BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'DAILY_LEVEL1';
 
-- Level 1 - cumulative (changes since last level 0 only)
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'DAILY_CUMUL';
 
-- Backup to specific location
BACKUP AS COMPRESSED BACKUPSET 
    FORMAT '/backup/oracle/%d_%T_%s_%p.bkp'
    DATABASE PLUS ARCHIVELOG;
 
-- Backup tablespace
BACKUP TABLESPACE users, data TAG 'TS_BACKUP';
 
-- Backup datafile
BACKUP DATAFILE 4 TAG 'DATAFILE_BACKUP';
 
-- Backup archived logs
BACKUP ARCHIVELOG ALL DELETE INPUT;
 
-- Validate backup (check for corruption)
VALIDATE DATABASE;
VALIDATE BACKUPSET TAG 'DAILY_FULL';
 
-- List backups
LIST BACKUP SUMMARY;
LIST BACKUP OF DATABASE;
 
-- Restore and recover
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
 
-- Point-in-time recovery
RUN {
    SET UNTIL TIME "TO_DATE('2024-01-15 10:30:00','YYYY-MM-DD HH24:MI:SS')";
    RESTORE DATABASE;
    RECOVER DATABASE;
}
ALTER DATABASE OPEN RESETLOGS;
 
-- Cross-check and delete obsolete
CROSSCHECK BACKUP;
DELETE OBSOLETE;
DELETE EXPIRED BACKUP;

SQL Server BACKUP Command

SQL Server's native backup capabilities are powerful and well-integrated:

Key Features:

Full, differential, and transaction log backups
Native compression
Backup encryption
Mirror to multiple destinations
Backup striping for large databases
VSS integration for application-consistent snapshots

sqlserver_backup_examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
-- SQL Server BACKUP Examples
 
-- Full database backup with compression
BACKUP DATABASE Production
TO DISK = 'D:\Backup\Production_Full.bak'
WITH 
    COMPRESSION,
    CHECKSUM,
    STATS = 10,
    NAME = 'Production Full Backup',
    DESCRIPTION = 'Daily full backup';
 
-- Differential backup (changes since last full)
BACKUP DATABASE Production
TO DISK = 'D:\Backup\Production_Diff.bak'
WITH 
    DIFFERENTIAL,
    COMPRESSION,
    CHECKSUM,
    STATS = 10;
 
-- Transaction log backup
BACKUP LOG Production
TO DISK = 'D:\Backup\Production_Log.trn'
WITH 
    COMPRESSION,
    CHECKSUM,
    STATS = 10;
 
-- Striped backup to multiple files (parallel)
BACKUP DATABASE Production
TO DISK = 'D:\Backup\Prod_stripe1.bak',
   DISK = 'E:\Backup\Prod_stripe2.bak',
   DISK = 'F:\Backup\Prod_stripe3.bak',
   DISK = 'G:\Backup\Prod_stripe4.bak'
WITH 
    COMPRESSION,
    CHECKSUM,
    MAXTRANSFERSIZE = 4194304,
    BUFFERCOUNT = 50;
 
-- Encrypted backup
BACKUP DATABASE Production
TO DISK = 'D:\Backup\Production_Encrypted.bak'
WITH 
    COMPRESSION,
    ENCRYPTION(
        ALGORITHM = AES_256,
        SERVER CERTIFICATE = BackupCertificate
    );
 
-- Mirror backup to two destinations
BACKUP DATABASE Production
TO DISK = 'D:\Backup\Production.bak'
MIRROR TO DISK = '\\backupserver\share\Production.bak'
WITH 
    FORMAT,
    COMPRESSION;
 
-- Copy-only backup (doesn't affect backup chain)
BACKUP DATABASE Production
TO DISK = 'D:\Backup\Production_CopyOnly.bak'
WITH 
    COPY_ONLY,
    COMPRESSION;
 
-- Verify backup integrity
RESTORE VERIFYONLY
FROM DISK = 'D:\Backup\Production_Full.bak'
WITH CHECKSUM;
 
-- View backup history
SELECT 
    s.database_name,
    s.backup_start_date,
    s.backup_finish_date,
    DATEDIFF(MINUTE, s.backup_start_date, s.backup_finish_date) AS Duration_Minutes,
    s.backup_size / 1048576 AS Size_MB,
    s.compressed_backup_size / 1048576 AS Compressed_MB,
    m.physical_device_name
FROM msdb.dbo.backupset s
JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = 'Production'
ORDER BY s.backup_start_date DESC;
 
-- Restore with point-in-time recovery
RESTORE DATABASE Production
FROM DISK = 'D:\Backup\Production_Full.bak'
WITH NORECOVERY;
 
RESTORE LOG Production
FROM DISK = 'D:\Backup\Production_Log1.trn'
WITH NORECOVERY;
 
RESTORE LOG Production
FROM DISK = 'D:\Backup\Production_Log2.trn'
WITH STOPAT = '2024-01-15T10:30:00',
     RECOVERY;

Enterprise Backup Platforms

Enterprise backup platforms provide centralized management, multi-platform support, and advanced features beyond native tools.

Common Enterprise Backup Platforms

Enterprise Backup Platform Comparison
Platform	Strengths	Database Support	Key Differentiator
Veeam Backup & Replication	VM integration, ransomware protection, instant recovery	SQL Server, Oracle, PostgreSQL, MySQL	Fastest recovery times, excellent UI/UX
Commvault Complete	Comprehensive coverage, deduplication, cloud mobility	All major databases + SAP, ERP	Single platform for all data protection
Veritas NetBackup	Scale, enterprise track record, AIX/HP-UX support	All major databases + mainframe	Largest install base, proven at scale
Cohesity DataProtect	Modern architecture, instant access, API-first	SQL Server, Oracle, PostgreSQL, MongoDB	Web-scale architecture, analytics
Dell EMC PowerProtect	Hardware integration, APEX cloud, deduplication	All major databases	Integration with Dell storage ecosystem
Rubrik	SLA automation, multi-cloud, immutability	SQL Server, Oracle, PostgreSQL, MySQL	Policy-based, zero-trust architecture

Evaluation Criteria for Enterprise Platforms

When evaluating enterprise backup platforms for database protection, consider:

1. Database Agent Quality

Native or log-based capture?
Impact on database performance?
Support for database-specific features (PITR, standby creation)?

2. Consistency Guarantees

Application-consistent backups?
Multi-database coordination?
Quiesce and snapshot integration?

3. Recovery Capabilities

Point-in-time recovery support?
Granular recovery (single table, single transaction)?
Instant access/mounting capabilities?

4. Operational Integration

API availability for automation?
Monitoring and alerting?
Compliance reporting?

5. Scale and Cost

Licensing model (capacity, instances, sockets)?
Storage efficiency (deduplication, compression)?
Multi-site replication?

Agent vs. Agentless

Cloud-Native Backup Solutions

Cloud databases typically include built-in backup capabilities, with additional services for enhanced protection.

AWS Database Backup

RDS Automated Backups:

Daily snapshots with transaction log backup
Point-in-time recovery up to 35 days
Encrypted by default with KMS

AWS Backup (centralized):

Policy-based protection across services
Cross-account and cross-region copy
Compliance reporting

Azure Database Backup

Azure SQL Backup:

Geo-redundant storage by default
Long-term retention (up to 10 years)
Point-in-time restore

Google Cloud Backup

Cloud SQL Backup:

On-demand and automated backups
Point-in-time recovery
Cross-region backup

cloud_backup_examples.sh
Bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
#!/bin/bash
# Cloud-Native Backup Management Examples
 
# =====================================
# AWS RDS
# =====================================
 
# Create manual snapshot
aws rds create-db-snapshot \
    --db-instance-identifier mydb \
    --db-snapshot-identifier mydb-manual-$(date +%Y%m%d)
 
# Copy snapshot to another region (DR)
aws rds copy-db-snapshot \
    --source-db-snapshot-identifier arn:aws:rds:us-east-1:123456789:snapshot:mydb-manual \
    --target-db-snapshot-identifier mydb-dr-copy \
    --source-region us-east-1 \
    --region us-west-2
 
# Restore to point in time
aws rds restore-db-instance-to-point-in-time \
    --source-db-instance-identifier mydb \
    --target-db-instance-identifier mydb-recovered \
    --restore-time "2024-01-15T10:30:00Z" \
    --db-instance-class db.r5.large
 
# List snapshots
aws rds describe-db-snapshots \
    --db-instance-identifier mydb \
    --query 'DBSnapshots[*].[DBSnapshotIdentifier,SnapshotCreateTime,Status]' \
    --output table
 
# AWS Backup - Create backup plan
aws backup create-backup-plan --backup-plan '{
    "BackupPlanName": "DailyDBBackup",
    "BackupPlanRule": [{
        "RuleName": "DailyRule",
        "TargetBackupVaultName": "Default",
        "ScheduleExpression": "cron(0 5 * * ? *)",
        "StartWindowMinutes": 60,
        "CompletionWindowMinutes": 180,
        "Lifecycle": {
            "DeleteAfterDays": 30
        }
    }]
}'
 
# =====================================
# Azure SQL
# =====================================
 
# Create database copy (snapshot)
az sql db copy \
    --resource-group myResourceGroup \
    --server myserver \
    --name mydatabase \
    --dest-server myserver \
    --dest-name mydatabase-copy
 
# Point-in-time restore
az sql db restore \
    --resource-group myResourceGroup \
    --server myserver \
    --name mydatabase \
    --dest-name mydatabase-restored \
    --time "2024-01-15T10:30:00Z"
 
# Configure long-term retention
az sql db ltr-policy set \
    --resource-group myResourceGroup \
    --server myserver \
    --database mydatabase \
    --weekly-retention P4W \
    --monthly-retention P12M \
    --yearly-retention P5Y \
    --week-of-year 1
 
# =====================================
# Google Cloud SQL
# =====================================
 
# Create on-demand backup
gcloud sql backups create \
    --instance=myinstance \
    --description="Manual backup $(date +%Y-%m-%d)"
 
# Restore from backup
gcloud sql backups restore BACKUP_ID \
    --restore-instance=myinstance
 
# Clone instance to point in time
gcloud sql instances clone myinstance myinstance-clone \
    --point-in-time="2024-01-15T10:30:00Z"
 
# List backups
gcloud sql backups list --instance=myinstance

Cloud Backup Limitations

Tool Selection Guidelines

Selecting the right backup tools requires balancing multiple factors. Use this framework to guide your decisions:

Tool Selection Matrix
Scenario	Recommended Tools	Rationale
Single PostgreSQL database, small team	pg_dump + pg_basebackup, scripts	Native tools are free, reliable, sufficient
PostgreSQL with serious RPO requirements	pgBackRest	PITR, encryption, verification, incremental
MySQL/MariaDB production	XtraBackup + mysqldump	Physical for DR, logical for portability
Oracle enterprise	RMAN + Data Guard	Best-in-class for Oracle, tightly integrated
SQL Server enterprise	Native BACKUP + enterprise platform	Strong native tools, supplement with management
Multi-database environment	Enterprise platform (Veeam, Commvault)	Unified management, cross-platform
Cloud-native (RDS, Cloud SQL)	Cloud-native + cross-region copy	Native integration, minimal operations
Hybrid cloud	Enterprise platform with cloud support	Consistent policy across environments

Key Selection Criteria

•RPO Requirements — Determine backup frequency needs; affects tool choices
•RTO Requirements — Fast recovery may require physical backup or instant access
•Database Platform(s) — Match tools to your specific databases
•Operational Expertise — Choose tools your team can operate effectively
•Integration Needs — APIs, monitoring, automation requirements
•Budget — Balance cost against capabilities; native tools are often sufficient
•Compliance — Encryption, audit logging, retention policy enforcement
•Scale — Tool must handle your data volumes and growth

Page Complete

4 / 5