SQL - Backup & Restore

Overview

Backups protect against data loss. Combine regular full backups with incrementals or binlog backups and test restores. Keep copies offsite.

At a glance

• Schedule automated full + incremental (or binlog) backups; secure, encrypt, and verify with checksums.
• Practice restores regularly; document RPO/RTO and verify you can meet them.
• Use logical dumps for portability; use physical backups for speed and point-in-time recovery.

MySQL

# Logical dump
mysqldump -u {{user}} -p {{db}} > {{db}}_$(date +%F).sql

# Restore
mysql -u {{user}} -p {{db}} < backup.sql

# Physical copy (InnoDB) with Percona XtraBackup (example)
innobackupex --user={{user}} --password=$MYSQL_PW /backups

SQL Server

-- Full backup
BACKUP DATABASE [MyDb]
TO DISK = N'/backups/MyDb_full.bak'
WITH INIT, COMPRESSION;

-- Restore
RESTORE DATABASE [MyDb]
FROM DISK = N'/backups/MyDb_full.bak'
WITH REPLACE;

SQLite

-- Online backup using the shell
sqlite3 src.db ".backup 'backup.sqlite'"

-- File copy (when DB is not active)
cp my.db backups/my.db.$(date +%F)

Tips

• Automate schedules and retention.
• Encrypt backups and store checksums.
• Test restore procedures regularly.