SQL Dump Format: Database Backups, Exports & Migration Files

What Is a SQL Dump File?

A SQL dump is a plain text file containing a sequence of SQL statements that, when executed against a database server, recreate a database's complete structure and data. The term "dump" comes from the Unix tradition of "dumping" memory or data state to a file for preservation or transfer.

SQL dump files are the universal format for database backup, migration, sharing development data, and seeding production databases. They are readable by any text editor, diff-able in version control, and executable by any compatible database client.

SQL Dump File Extensions

What a SQL Dump Typically Contains

-- Drop and recreate tables safely
DROP TABLE IF EXISTS `users`;

-- Table structure
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Data rows
INSERT INTO `users` (`id`, `email`, `name`, `created_at`) VALUES
(1, 'alice@example.com', 'Alice Smith', '2024-01-15 09:23:00'),
(2, 'bob@example.com', 'Bob Jones', '2024-01-16 14:05:00');

-- Indexes (sometimes separate from CREATE TABLE)
CREATE INDEX idx_users_name ON users(name);

A complete dump typically includes:

• SET statements for charset and SQL mode configuration
• DROP TABLE IF EXISTS for safe re-import (idempotent)
• CREATE TABLE statements with complete schema
• INSERT INTO statements with all data rows
• CREATE INDEX and CREATE VIEW statements
• GRANT statements for database permissions (in full server dumps)
• DELIMITER changes for stored procedures and triggers

Why SQL Dumps Are Used

• Database migration — moving a database from one server to another
• Backup and restore — creating point-in-time snapshots for disaster recovery
• Development sharing — sharing a database state with team members
• Deployment — seeding production databases with initial data
• Development snapshots — capturing a known-good state to roll back to
• Version control — tracking schema changes alongside application code

MySQL / MariaDB Dumps

Creating Dumps (Export)

# Export a single database
mysqldump -u username -p database_name > backup.sql

# Export all databases on a server
mysqldump -u username -p --all-databases > all_databases.sql

# Export specific tables only
mysqldump -u username -p database_name table1 table2 > tables.sql

# Export schema only (no data)
mysqldump -u username -p --no-data database_name > schema.sql

# Export data only (no CREATE TABLE)
mysqldump -u username -p --no-create-info database_name > data.sql

# Export compressed (saves disk space for large databases)
mysqldump -u username -p database_name | gzip > backup.sql.gz

Restoring Dumps (Import)

# Restore a dump into an existing database
mysql -u username -p database_name < backup.sql

# Create database first, then restore
mysql -u username -p -e "CREATE DATABASE mydb CHARACTER SET utf8mb4;"
mysql -u username -p mydb < backup.sql

# Restore compressed dump
gunzip < backup.sql.gz | mysql -u username -p database_name

PostgreSQL Dumps

Creating Dumps (Export)

# Plain SQL format (portable, readable)
pg_dump -U username database_name > backup.sql

# Custom binary format (faster, supports parallel restore)
pg_dump -U username -F c database_name > backup.dump

# Directory format (parallel dump for large databases)
pg_dump -U username -F d -j 4 database_name -f backup_dir/

# Schema only
pg_dump -U username --schema-only database_name > schema.sql

# Dump all databases including roles and tablespaces
pg_dumpall -U postgres > all_postgres.sql

Restoring Dumps (Import)

# Restore plain SQL dump
psql -U username database_name < backup.sql

# Restore custom format dump (supports parallel restore)
pg_restore -U username -d database_name backup.dump

# Parallel restore (faster for large databases)
pg_restore -U username -d database_name -j 4 backup.dump

SQLite Dumps

# Export
sqlite3 database.db .dump > backup.sql

# Export specific table only
sqlite3 database.db ".dump tablename" > table.sql

# Import (create new database from dump)
sqlite3 new_database.db < backup.sql

# Import into existing database
sqlite3 existing.db < backup.sql

SQL Dump Structure Walkthrough

Here is a realistic small dump with each section explained:

-- (1) Server settings — set correct charset for reliable restore
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;  -- Disable FK checks during import

-- (2) Table definition — recreates the schema
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `total` decimal(10,2) NOT NULL,
  `status` enum('pending','paid','shipped') DEFAULT 'pending',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_user` (`user_id`),
  CONSTRAINT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB;

-- (3) Data rows — inserted in batches for efficiency
INSERT INTO `orders` (`id`, `user_id`, `total`, `status`) VALUES
(1, 1, 99.99, 'paid'),
(2, 1, 149.50, 'shipped'),
(3, 2, 29.99, 'pending');

-- (4) Re-enable FK checks after all data is loaded
SET FOREIGN_KEY_CHECKS = 1;

Converting SQL Dump to CSV

Option 1: Restore then export (cleanest)

1. Restore the SQL dump to a local database: mysql -u root -p mydb < backup.sql
2. Open the database in a GUI (DBeaver, TablePlus, MySQL Workbench, DataGrip)
3. Right-click the table → Export → CSV

Option 2: Command-line extraction

# Export specific table as CSV via mysql client
mysql -u root -p -e "SELECT * FROM orders" mydb | sed 's/\t/,/g' > orders.csv

# mysqldump --tab exports each table as separate schema + TSV data files
mysqldump --tab=/tmp/ -u root -p mydb tablename
# Creates: /tmp/tablename.sql (schema) + /tmp/tablename.txt (TSV data)

Option 3: Python parsing (for simple cases)

import re, csv

with open('backup.sql', 'r') as f:
    content = f.read()

# Extract INSERT values for a specific table
pattern = r"INSERT INTO `orders`[^;]+VALUES\s*(.+?);"
matches = re.findall(pattern, content, re.DOTALL)
# Parse values manually or use a proper SQL parser library

SQL Dump to Excel Workflow

The recommended path: restore → GUI export

1. Restore dump to local MySQL/PostgreSQL
2. Connect via DBeaver (free), TablePlus, or MySQL Workbench
3. Right-click table → Export Data → choose Excel (.xlsx) or CSV
4. Open in Excel/LibreOffice Calc

Handling Large Dumps

# Split a large dump into 100,000-line chunks for processing
split -l 100000 backup.sql part_

# Extract only rows for a specific table
grep "INSERT INTO \`orders\`" backup.sql > orders_data.sql

# Count rows in dump (approximate)
grep -c "^INSERT INTO" backup.sql

# Monitor import progress with pv (pipe viewer)
pv backup.sql | mysql -u root -p database_name

Common Issues and Fixes

Charset / encoding problems

# Force UTF-8 during import
mysql -u root -p --default-character-set=utf8mb4 mydb < backup.sql

MySQL version mismatch

MySQL 8.0 dumps use newer syntax (e.g., utf8mb4_0900_ai_ci collation) that MySQL 5.7 doesn't understand. Fix: search and replace the collation name in the dump file, or use the --compatible flag when creating the dump:

mysqldump --compatible=mysql57 -u root -p mydb > backup.sql

Foreign key constraint errors

-- Add this at the TOP of the dump file (before any INSERT):
SET FOREIGN_KEY_CHECKS=0;

-- Add this at the BOTTOM (after all INSERTs):
SET FOREIGN_KEY_CHECKS=1;

Binary data (BLOB columns)

Binary data is stored as hex literals in MySQL dumps (0x89504E47...). This is correct behaviour — the binary data is preserved losslessly. No action needed unless you need to inspect the actual binary content.

Converting SQL Files

• SQL to CSV — extract table data as comma-separated values for Excel/Sheets
• SQL to JSON — convert table rows to JSON arrays for APIs
• SQL to Excel — restore and export via database GUI
• MySQL to PostgreSQL — dialect conversion (different syntax for types, AUTO_INCREMENT vs SERIAL, etc.)

Use KaijuConverter to convert database exports and structured data files instantly.

Related conversions

Frequent conversions across the catalogue:

• JPG to PNG
• MP4 to MP3
• PDF to DOCX
• PNG to JPG
• MP3 to WAV
• DOCX to PDF