A dump is a database written out as the SQL statements that would build it again. Run the file against an empty database and the tables come back with their rows.
What it holds
A dump is a text file. It has no header, no signature and no index. Everything in it is a statement, a comment or spacing. A statement says what to do, and the database does it, in the order the file gives. So a dump is not a description of a database but a program that makes one.
That has some useful results. A dump can be read, searched and compared with ordinary text tools. It does not depend on the machine or on how the server stores its files. It can usually be loaded into a later version of the same database. It is also why loading a dump takes far longer than copying data files: every row is parsed and inserted again, and every index is built again.
Dumps are not a single standard format. Each database has its own dialect of SQL, and each tool writes in its own way. The SQL standard defines the language, but no standard defines a dump.
The order of the statements
The order matters, because each statement relies on what came before. Tools write roughly this order:
| Part | Holds |
|---|---|
| Heading comments | The tool, its version, the server and the database |
| Session settings | The character set, the time zone, and turning off checks that would slow the load or fail on forward references |
| Definitions | CREATE TABLE, and in PostgreSQL also types, sequences and functions |
| Data | The rows of each table, as INSERT statements or as a COPY block |
| Keys and indexes | In some tools these come after the data, because building an index once is faster than updating it for each row |
| Foreign keys | Added last, or turned off during the load, so a row can refer to a row that comes later |
| Views, triggers and routines | Written after the tables they depend on |
| Closing settings | The settings changed at the start, put back |
Here is a small invented dump, in the common order:
-- A small dump of a table called fruit
CREATE TABLE fruit (
id integer PRIMARY KEY,
name varchar(20) NOT NULL
);
INSERT INTO fruit VALUES (1, 'apple'), (2, 'pear');
INSERT INTO fruit VALUES (3, 'plum');
CREATE INDEX fruit_name ON fruit (name);Making and loading one
| Database | Make it with | Load it with |
|---|---|---|
| MySQL, MariaDB | mysqldump, or phpMyAdmin’s Export tab | the mysql client |
| PostgreSQL | pg_dump (plain format) | psql |
| SQLite | the shell’s .dump command | sqlite3, or .read |
| SQL Server | Generate Scripts in SQL Server Management Studio | sqlcmd, or Management Studio |
The file needs the matching client. COPY blocks, DELIMITER
and GO are read by the client tools, not by the database, so sending
the file through a program that only knows SQL does not always work.
Large dumps are often compressed, for example as .sql.gz, and a client can
read them through a pipe, as the sqlite3 example in its documentation does
with zcat.