How to Backup an SQL Database

What you’ll build or solve

You’ll create a backup file from an existing SQL database using built-in tools such as mysqldump, pg_dump, or BACKUP DATABASE.

When this approach works best

This approach works best when:

• You want a copy of your data before deploying changes.
• You are migrating data to another server.
• You need a manual backup outside automated cloud snapshots.

Skip this if your managed provider already handles backups and you do not need an additional export.

Prerequisites

• Access to the database server
• Database credentials
• Permission to read and export the database
• Command-line access or database management tool

You must be connected to the server or have network access before running backup commands.

Step-by-step instructions

Step 1: Run the backup command for your system

Choose the command that matches your database.

Option A: MySQL with mysqldump

mysqldump-u username-p database_name > backup.sql

This creates a SQL file containing tables and data.

To back up all databases:

mysqldump-u username-p--all-databases > full_backup.sql

What to look for:

After running the command, a .sql file appears in your current directory.

Option B: PostgreSQL with pg_dump

pg_dump-U username-d database_name-F c-f backup.dump

• F c creates a compressed backup file.

To back up all databases:

pg_dumpall-U username > full_backup.sql

What to look for:

The specified file name should appear after execution.

Option C: SQL Server

BACKUP DATABASE database_name
TO DISK='C:\backup\database_name.bak';

This creates a .bak file at the specified path.

What to look for:

Confirm the file exists in the target directory.

Option D: SQLite

SQLite databases are stored as files. Copy the file directly:

cp database.db backup.db

Or use the built-in backup command:

sqlite3 database.db".backup backup.db"

What to look for:

A new database file appears in your directory.

What to look for after backup

• Confirm the backup file exists and has a reasonable file size.
• Store backups outside the database server to reduce risk.
• Test restoration periodically using a separate environment.
• Automate backups if you need regular protection.

Backup creation ends when the file is successfully generated. Storage strategy and restoration are separate processes.

Examples you can copy

Example 1: Backup a development database in MySQL

mysqldump-u root-p app_dev > app_dev_backup.sql

Example 2: Backup PostgreSQL database in compressed format

pg_dump-U postgres-d analytics_db-F c-f analytics_db.dump

Example 3: Backup SQL Server database

BACKUP DATABASE reporting_db
TO DISK='C:\backup\reporting_db.bak';

Example 4: Backup SQLite database file

sqlite3 my_database.db".backup my_database_backup.db"

Common mistakes and how to fix them

Mistake 1: Missing authentication flags

You might run:

mysqldump database_name > backup.sql

Why it breaks:

The command does not include username or password information.

Correct approach:

mysqldump-u username-p database_name > backup.sql

Mistake 2: Backing up the wrong database

You might accidentally export the wrong name.

Why it breaks:

You create a backup file, but it does not contain the intended data.

Correct approach:

Double-check the database name before running the command.

Mistake 3: Writing to a non-existent directory

You might run:

mysqldump-u username-p database_name > /backup/backup.sql

Why it breaks:

The target directory does not exist or lacks write permission.

Correct approach:

Verify the directory path or create it before running the command.

Troubleshooting

If you see “command not found,” confirm the database tools are installed and added to your PATH.

If authentication fails, verify username, password, and host settings.

If the backup file is empty, confirm the database name is correct.

If SQL Server reports file path errors, confirm the directory exists and has write permissions.

If SQLite backup fails, close other processes using the database file.

Quick recap

• Use built-in tools such as mysqldump or pg_dump.
• Run the correct backup command for your database system.
• Confirm a backup file is created.
• Store backups outside the database server.
• Test restoration separately to verify integrity.