Guide

SQL Dump Format: Database Backups, Exports & Migration Files

PC By Pablo Cirre • Updated May 7, 2026

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

Extension	Associated System
`.sql`	Most common — MySQL, MariaDB, PostgreSQL, SQLite
`.dump`	PostgreSQL custom dumps (also `.dmp`)
`.bak`	Microsoft SQL Server backups
`.dmp`	Oracle Database exports

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)

Restore the SQL dump to a local database: mysql -u root -p mydb < backup.sql
Open the database in a GUI (DBeaver, TablePlus, MySQL Workbench, DataGrip)
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

Restore dump to local MySQL/PostgreSQL
Connect via DBeaver (free), TablePlus, or MySQL Workbench
Right-click table → Export Data → choose Excel (.xlsx) or CSV
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:

Frequently Asked Questions

A .sql file is plain text — you can open it in any text editor (VS Code, Notepad++, nano) to read the SQL statements. To actually run it and restore the database, you need to connect to a database server: for MySQL use `mysql -u username -p database_name < backup.sql` in the terminal, or open it in a database GUI like DBeaver, MySQL Workbench, or TablePlus and execute it. For SQLite: `sqlite3 database.db < backup.sql`.

A .sql arquivo é plain text — Você pode abrir it in any text editor (VS Code, Notepad++, nano) to read the SQL statements. To actually run it e restore the database, Você precisa connect para um database server: para MySQL usar `mysql -u username -p database_name < backup.sql` no terminal, ou abrir it em um database GUI like DBeaver, MySQL Workbench, ou TablePlus e execute it. para SQLite: `sqlite3 database.db < backup.sql`.

A .sql Datei is plain text — Sie können öffnen it in any text editor (VS Code, Notepad++, nano) to read the SQL statements. To actually run it und restore the database, Sie müssen connect zu einem database server: für MySQL verwenden `mysql -u username -p database_name < backup.sql` im terminal, oder öffnen it in einem database GUI like DBeaver, MySQL Workbench, oder TablePlus und execute it. für SQLite: `sqlite3 database.db < backup.sql`.

A .sql archivo is plain text — Puedes abrir it in any text editor (VS Code, Notepad++, nano) to read the SQL statements. To actually run it y restore the database, Necesitas connect a un database server: para MySQL usar `mysql -u username -p database_name < backup.sql` en el terminal, o abrir it en un database GUI like DBeaver, MySQL Workbench, o TablePlus y execute it. para SQLite: `sqlite3 database.db < backup.sql`.

On KaijuConverter every file is processed inside an isolated container, encrypted in transit (TLS 1.3) and at rest, and automatically deleted after 60 minutes with multi-pass overwrite. We never train on, share, or analyze user content. For maximum privacy on extremely sensitive material, prefer offline tools (ImageMagick, FFmpeg, LibreOffice) that you control end-to-end.

Use the mysqldump command-line tool: `mysqldump -u your_username -p your_database_name > backup.sql`. You'll be prompted for your password. This creates a backup.sql file in the current directory. For a compressed backup: `mysqldump -u username -p database_name | gzip > backup.sql.gz`. Most database GUIs (phpMyAdmin, DBeaver, MySQL Workbench) also have Export/Dump options that generate SQL dump files.

Use the mysqldump command-line tool: `mysqldump -u your_username -p your_database_name > backup.sql`. You'll be prompted para your password. This creates a backup.sql arquivo no current directory. para a comprimido backup: `mysqldump -u username -p database_name | gzip > backup.sql.gz`. Most database GUIs (phpMyAdmin, DBeaver, MySQL Workbench) also have Export/Dump options that generate SQL dump files.

Use the mysqldump command-line tool: `mysqldump -u your_username -p your_database_name > backup.sql`. You'll be prompted für your password. This creates a backup.sql Datei im current directory. für a komprimiert backup: `mysqldump -u username -p database_name | gzip > backup.sql.gz`. Most database GUIs (phpMyAdmin, DBeaver, MySQL Workbench) also have Export/Dump options that generate SQL dump files.

Use the mysqldump command-line tool: `mysqldump -u your_username -p your_database_name > backup.sql`. You'll be prompted para your password. This creates a backup.sql archivo en el current directory. para a comprimido backup: `mysqldump -u username -p database_name | gzip > backup.sql.gz`. Most database GUIs (phpMyAdmin, DBeaver, MySQL Workbench) also have Export/Dump options that generate SQL dump files.

For 95% of use cases, yes — server-side ImageMagick, FFmpeg and LibreOffice produce identical output to the same tools on your laptop. Desktop software wins for: extremely large files (multi-GB), batch jobs of thousands of files, scripted pipelines, or content too sensitive to upload. KaijuConverter caps at 500 MB per file (1 GB on paid plans).

The cleanest approach: (1) Restore the SQL dump to a local database (`mysql -u root -p mydb < backup.sql`), then (2) Export specific tables as CSV using your database GUI (DBeaver, TablePlus, MySQL Workbench all have CSV export). For command-line: `mysql -u root -p -e "SELECT * FROM mytable" mydb | sed 's/\t/,/g' > output.csv`. Alternatively, `mysqldump --tab=/tmp/ mydb tablename` exports the table data as a TSV file directly.

The cleanest approach: (1) Restore the SQL dump para um local database (`mysql -u root -p mydb < backup.sql`), then (2) Export specific tables as CSV usando your database GUI (DBeaver, TablePlus, MySQL Workbench all have CSV export). para command-line: `mysql -u root -p -e "SELECT * de mytable" mydb | sed 's/\t/,/g' > output.csv`. Alternatively, `mysqldump --tab=/tmp/ mydb tablename` exports the table data como um TSV arquivo directly.

The cleanest approach: (1) Restore the SQL dump zu einem local database (`mysql -u root -p mydb < backup.sql`), then (2) Export specific tables as CSV using your database GUI (DBeaver, TablePlus, MySQL Workbench all have CSV export). für command-line: `mysql -u root -p -e "SELECT * von mytable" mydb | sed 's/\t/,/g' > output.csv`. Alternatively, `mysqldump --tab=/tmp/ mydb tablename` exports the table data als ein TSV Datei directly.

The cleanest approach: (1) Restore the SQL dump a un local database (`mysql -u root -p mydb < backup.sql`), then (2) Export specific tables as CSV using your database GUI (DBeaver, TablePlus, MySQL Workbench all have CSV export). para command-line: `mysql -u root -p -e "SELECT * de mytable" mydb | sed 's/\t/,/g' > output.csv`. Alternatively, `mysqldump --tab=/tmp/ mydb tablename` exports the table data como un TSV archivo directly.

Most format conversions are lossy by design — JPG, MP3, MP4, WebP all discard perceptual data to save bytes. Going through a lossy intermediate compounds the loss. To minimize visible/audible drift: convert from the original master, choose a higher quality setting, and avoid converting back and forth between lossy formats.

Foreign key constraint errors occur when you're inserting data into a table that references another table, but that referenced table's data hasn't been inserted yet. The fix: add `SET FOREIGN_KEY_CHECKS=0;` at the very beginning of the dump file (before any INSERT statements) and `SET FOREIGN_KEY_CHECKS=1;` at the very end. This temporarily disables constraint checking during import. In MySQL Workbench or phpMyAdmin, look for the 'Disable foreign key checks' option in the import dialog.

Foreign key constraint errors occur when you're inserting data em a table that references another table, mas that referenced table's data hasn't been inserted yet. The fix: add `SET FOREIGN_KEY_CHECKS=0;` at the very beginning of the dump arquivo (before any INSERT statements) e `SET FOREIGN_KEY_CHECKS=1;` at the very end. This temporarily disables constraint checking durante import. In MySQL Workbench ou phpMyAdmin, look para the 'Disable foreign key checks' option no import dialog.

Foreign key constraint errors occur when you're inserting data in einem table that references another table, aber that referenced table's data hasn't been inserted yet. The fix: add `SET FOREIGN_KEY_CHECKS=0;` at the very beginning des dump Datei (before any INSERT statements) und `SET FOREIGN_KEY_CHECKS=1;` at the very end. This temporarily disables constraint checking während import. In MySQL Workbench oder phpMyAdmin, look für the 'Disable foreign key checks' option im import dialog.

Foreign key constraint errors occur when you're inserting data en a table that references another table, pero that referenced table's data hasn't been inserted yet. The fix: add `SET FOREIGN_KEY_CHECKS=0;` at the very beginning del dump archivo (before any INSERT statements) y `SET FOREIGN_KEY_CHECKS=1;` at the very end. This temporarily disables constraint checking durante import. In MySQL Workbench o phpMyAdmin, look para the 'Disable foreign key checks' option en el import dialog.

Yes — KaijuConverter accepts multiple files in a single drop and returns a ZIP. For very large batches (thousands of files) consider command-line tools or our API: <code>find . -name "*.heic" -exec magick {} {.}.jpg \;</code> or similar one-liners scale to millions of files when run locally.