.SQL

Introduction

A SQL dump is a backup of a database written as SQL statements in a text file. Run against an empty database, the statements create the tables again and insert every row. Because it is plain text, a dump can be read, searched, compared and moved between servers and versions, which the database’s own files cannot.

Dumps are made by tools such as mysqldump, pg_dump and the .dump command of SQLite. Each database has its own dialect of SQL, so a dump from one system usually needs changes before another will load it. The same .sql extension is used for any file of SQL, such as hand-written queries and migration scripts.

Structure

The file is text, usually UTF-8, made of statements that each end with a semicolon. Comments start with -- or sit between /* and */. A typical dump has, in order:

• settings for the session, such as the character set, and a comment naming the tool and server version
• for each table, CREATE TABLE with its columns and keys
• the rows, as INSERT INTO … VALUES (…), (…); statements, or in PostgreSQL as a COPY … FROM stdin; block of tab-separated lines ended by \.
• indexes, constraints, views, triggers and other objects

Large dumps are often compressed, for example as .sql.gz.

Links