SQL - Backup Database

SQL Database Backup and Restore

Database backup is a critical process for disaster recovery, data migration, and testing. It involves creating a copy of your database that can be restored in case of data loss, corruption, or system failure. This tutorial covers the fundamental concepts of database backups and provides practical examples for modern database systems.

Core Concepts of Database Backups

Backup Types

• Full Backup: A complete copy of the entire database. It includes all data, table structures, indexes, stored procedures, and triggers. Restoring from a full backup is straightforward but can be slow and require significant storage space.
• Differential Backup: Backs up only the data that has changed since the last full backup. Restoring requires the last full backup plus the latest differential backup. They are faster to create than full backups.
• Incremental/Transaction Log Backup: Backs up all transactions that have occurred since the last backup (of any type). This is the most granular form of backup and is essential for Point-In-Time Recovery (PITR). To restore, you need the last full backup, possibly the last differential, and all subsequent transaction log backups.

Logical vs. Physical Backups

Most command-line tools like mysqldump or pg_dump perform logical backups. They export the database content as a set of SQL statements (like CREATE TABLE and INSERT). Physical backups copy the actual data files, which is often faster but less flexible across different versions or operating systems.

Backup and Restore by Database System

The tools and syntax for backup operations are specific to each database management system (DBMS).

PostgreSQL: Using pg_dump and pg_restore

pg_dump is a powerful command-line utility for creating logical backups of a PostgreSQL database.

# To create a compressed, custom-format backup:
# -U: username, -W: prompt for password, -F c: custom format, -d: database name
pg_dump -U postgres -W -F c -d mydatabase > mydatabase.dump

# To restore from the backup file:
# -d: target database (must exist and be empty)
createdb -U postgres new_database
pg_restore -U postgres -W -d new_database mydatabase.dump

MySQL/MariaDB: Using mysqldump

mysqldump is the standard utility for backing up MySQL or MariaDB databases.

# To create a backup including stored procedures and triggers:
# --routines: include stored procedures/functions
# --triggers: include triggers
mysqldump -u username -p --routines --triggers mydatabase > mydatabase_backup.sql

# To restore the database:
# First, create the database if it doesn't exist
mysql -u username -p -e "CREATE DATABASE IF NOT EXISTS new_database;"

# Then, import the dump file
mysql -u username -p new_database < mydatabase_backup.sql

MS SQL Server: Using T-SQL Commands

SQL Server uses BACKUP DATABASE and RESTORE DATABASE commands within a query editor.

-- To create a full backup
BACKUP DATABASE TestDB
TO DISK = 'D:\Backups\TestDB_Full.bak'
WITH NOFORMAT, INIT, NAME = N'TestDB-Full Database Backup', COMPRESSION;
GO

-- To restore the database, potentially overwriting an existing one
RESTORE DATABASE TestDB
FROM DISK = 'D:\Backups\TestDB_Full.bak'
WITH REPLACE, RECOVERY;
GO

Modern Best Practices for Database Backups

• Automation: Schedule regular backups using cron jobs, Windows Task Scheduler, or CI/CD pipelines. Cloud services (like AWS RDS, Azure SQL) offer automated snapshot capabilities.
• Test Your Backups: A backup is useless if it cannot be restored. Regularly test your restore process in a non-production environment.
• 3-2-1 Rule: Keep at least three copies of your data, on two different types of media, with one copy stored off-site (or in a different cloud region).
• Encryption: Encrypt your backup files to protect sensitive data, especially if stored in the cloud or on portable media.
• Monitoring: Monitor your backup jobs to ensure they complete successfully and be alerted on failures.