SQL Backup DB

A database backup creates a operational copy of your data, schema, and transaction logs. Backups protect against server crashes, hardware failures, corruption, ransomware, and accidental deletions (DROP TABLE or DELETE without a WHERE clause).

Types of Database Backups

Relational database management systems generally utilize three main strategies to balance storage overhead with recovery speeds:

Backup Type	What It Captures	Advantages	Disadvantages
Full Backup	Complete database (all objects, schema, data, and active logs).	Easy to restore from a single file.	Large storage footprint; takes longer to run.
Differential Backup	Only the data that changed since the last full backup.	Faster and smaller than full backups.	Restores require both the last full backup and the latest differential.
Transaction Log Backup	All completed transactions since the last log backup.	Enables point-in-time recovery to exact minutes/seconds.	Requires sequential restoration of every log file in order.

SQL Server (T-SQL) Backup Commands

Microsoft SQL Server natively supports SQL statements for creating backup files (.bak and .trn).

1. Full Database Backup

BACKUP DATABASE SalesData
TO DISK = 'C:\Backups\SalesData_Full.bak'
WITH FORMAT,
     MEDIANAME = 'SalesData_Backups',
     NAME = 'Full Backup of SalesData';

2. Differential Database Backup

Captures only changes made after the last full backup:

BACKUP DATABASE SalesData
TO DISK = 'C:\Backups\SalesData_Diff.bak'
WITH DIFFERENTIAL;

3. Transaction Log Backup

Captures incremental changes to allow point-in-time recovery:

BACKUP LOG SalesData
TO DISK = 'C:\Backups\SalesData_Log.trn';

Engine-Specific Backup Utilities

Many open-source systems handle database backups via command-line tools rather than inline SQL queries.

1. PostgreSQL (`pg_dump`)

PostgreSQL uses the external pg_dump utility to export database definitions and data into a standard text SQL script or custom binary dump file:

# Export as plain SQL script
pg_dump -U username -d sales_db > sales_db_backup.sql

# Export as compressed binary file (recommended for production)
pg_dump -U username -F c -b -v -f sales_db_backup.dump sales_db

2. MySQL / MariaDB (`mysqldump`)

MySQL relies on mysqldump to generate a file filled with CREATE TABLE and INSERT INTO statements:

# Export single database
mysqldump -u root -p SalesData > SalesData_Backup.sql

# Export all databases on the server
mysqldump -u root -p --all-databases > AllDatabases_Backup.sql

Restoring a Database

Creating backups is only half the process; you must regularly verify that your system can restore from those files when a system fails.

Restoring in SQL Server

RESTORE DATABASE SalesData
FROM DISK = 'C:\Backups\SalesData_Full.bak'
WITH REPLACE;

Restoring in MySQL

mysql -u root -p SalesData < SalesData_Backup.sql

Restoring in PostgreSQL

# For custom binary dumps (.dump)
pg_restore -U username -d sales_db sales_db_backup.dump

# For plain text files (.sql)
psql -U username -d sales_db -f sales_db_backup.sql

Best Practices for Database Backups

Follow the 3-2-1 Rule: Maintain at least 3 copies of your data across 2 different storage media, with 1 copy stored offsite or in cloud storage.
Automate Scheduled Backups: Use tools like SQL Server Agent, cron jobs, or cloud-native snapshots (AWS RDS / Azure SQL) to automate backups off-peak hours.
Test Restores Periodically: A backup is only valid if it can successfully restore. Schedule regular drill tests on staging environments.
Secure Your Backup Files: Encrypt backup files at rest and restrict access to authorized personnel, as backup dumps contain unencrypted data extracts.