SQL BACKUP DATABASE
"Backup" means different things on different databases. The unifying idea: capture enough state to recreate the database exactly.
SQL Server — built-in syntax
SQL
-- Full backup BACKUP DATABASE shop TO DISK = 'D:\backups\shop.bak'; -- Differential — just what's changed since last full BACKUP DATABASE shop TO DISK = 'D:\backups\shop_diff.bak' WITH DIFFERENTIAL;
MySQL / MariaDB — mysqldump
Shell
mysqldump --single-transaction -u admin -p shop > shop.sql gzip shop.sql
PostgreSQL — pg_dump
Shell
pg_dump -Fc -U admin shop > shop.dump # custom format, restore with pg_restore pg_dump -U admin shop > shop.sql # plain SQL
The three backup questions
| Question | Why it matters |
|---|---|
| How often? | Sets your RPO — how much data you can lose. |
| How long to restore? | Sets your RTO — how long the outage lasts. |
| Have you tested restore? | Untested backups fail in production. |
Point-in-time recovery
Combine a full backup with the database's transaction log (binlog in MySQL, WAL in Postgres, transaction log in SQL Server) to restore to any moment between the full backup and now.
Tip: If you only test backup creation but never restoration, you don't really have backups — you have hope.
Example
Exercise
MySQL CLI tool used for logical backups.
-u admin -p shop > shop.sql
Nine letters.
Discussion
Loading…