SQL BACKUP AND RESTORE

SQL
POSTGRES
INTERACTIVE

Essential SQL Backup & Restore Guide

1. Core Backup Concepts
Backup Types
  1. Full: Complete database copy
  2. Differential: Changes since last full backup
  3. Transaction Log: Point-in-time recovery capability
  4. Snapshot: Near-instant backups using storage tech
Critical Metrics
  1. RPO: Recovery Point Objective (data loss tolerance)
  2. RTO: Recovery Time Objective (downtime tolerance)
  3. Verification: Checksum validation processes
  4. Storage: Local vs cloud vs tape tradeoffs
2. Cross-Platform Examples
Cloud Backup Patterns
# AWS S3 Backup (PostgreSQL)
pg_dump -Fc dbname | \
aws s3 cp - s3://bucket/backup-$(date +%Y%m%d).dump

# Azure SQL Database Export
az sql db export \
  -s server -n database -g resource-group \
  -u username -p password \
  --storage-uri https://storage.blob.core.windows.net/container/bacpac.bacpac
Encrypted Backups
-- MySQL Encrypted Backup
openssl enc -aes-256-cbc -salt -in backup.sql -out backup.sql.enc

-- SQL Server Native Encryption
BACKUP DATABASE MyDB TO DISK = 'Z:\SQLBackups\MyDB.bak'
WITH ENCRYPTION (ALGORITHM = AES_256,
  SERVER CERTIFICATE = MyBackupCert);
3. Advanced Recovery Techniques
Bare Metal Recovery
# PostgreSQL Disaster Recovery
# 1. Install same PostgreSQL version
# 2. Restore config files
# 3. Deploy base backup:
pg_basebackup -h primary -U repl -D /var/lib/pgsql/data -P -Xs -R
# 4. Restore WAL archives
rsync -avz /backup/wal/ /var/lib/pgsql/wal_archive/
Partial Object Recovery
-- SQL Server Single Table Recovery
-- 1. Restore to temporary DB
RESTORE DATABASE tempdb FROM DISK='backup.bak' 
WITH FILE=1, NORECOVERY, MOVE 'data' TO 'C:\temp\tempdb.mdf'

-- 2. Export needed table
INSERT INTO production.dbo.table
SELECT * FROM tempdb.dbo.table

-- 3. Clean up
DROP DATABASE tempdb
4. Automation & Monitoring
Backup Monitoring Script
# Check PostgreSQL backup freshness
LAST_BACKUP=$(find /backups -name "*.dump" -mtime -1 | wc -l)

if [ $LAST_BACKUP -eq 0 ]; then
  echo "ALERT: No recent backups!" | \
  mail -s "Backup Failure" dba@example.com
  exit 1
fi
Retention Management
# Automated backup rotation
find /backups -name "*.dump" -mtime +30 -exec rm {} \;

# SQL Server Ola Hallengren solution
EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = '\\nas\sqlbackups',
@BackupType = 'FULL',
@CleanupTime = 24,
@Verify = 'Y'
5. Special Considerations
Large Database Strategies
  1. Filegroup Backups: Backup subsets of data files
  2. Parallel Backup: pg_dump -j / MySQL Shell util.dumpInstance
  3. Incremental Forever: Combine full + differential + log backups
  4. Split Archives: pg_dump -Fd (directory format)
High Availability Integration
  1. Replica Promotion: Convert replica to primary during DR
  2. Backup From Replicas: Offload backup overhead
  3. Geo-Redundant: Cross-region backup copies
  4. Backup Impact: Monitoring performance during backups