The SQLite command line provides several methods for exporting or backing up an entire database. One of those involves the .dump dot command.

The .dump command enables you to convert a whole database to a single ASCII text file. In other words, it renders the whole database as SQL. The text file contains all the SQL statements required to create the tables, insert data, etc.

You can also use .dump to create a compressed file. I outline both of these methods below.

Export as SQL

You can use .dump in conjunction with .output or .once to export the whole database to an .sql file.

This file will contain all the SQL statements required to reconstruct the database (including creating all tables, inserting all data, etc).

Here’s an example:

.once Store.sql
.dump

In this case I used the .once command. This simply directs the results of the next command or SQL statement into the specified file.

In this case, the next command is the .dump dot command, which by default, renders all database content as SQL.

You could alternatively use .output, but this will direct the results of all future commands/SQL statements into the file. This could have unintended consequences if you’re not careful.

Restore the Database

Once you’ve created the .sql backup file, you can reconstruct the whole database by simply reading that file from within SQLite.

For example, you can connect to SQLite while specifying a new database file (one that doesn’t already exist):

sqlite3 Store2.db

By specifying a database file that doesn’t exist, SQLite will create a blank database.

Now that you’re in SQLite, you can read the contents of the backup file:

.read Store.sql

That’s all there is to it. The database has been reconstructed from the .sql file. All tables have been created and all data has been inserted.

Create a Compressed File

If your database is large and contains a lot of data, you might be better off creating a compressed backup file.

Here’s an example:

sqlite3 Store .dump | gzip -c >Store.dump.gz

Note that this is run outside of SQLite. In other words, I didn’t connect to SQLite before running this command. I simply opened a new terminal window and ran that command.

Actually, I navigated to the backup folder first. If you don’t do this, you’ll need to include the path in the backup file.

You can reconstruct the database with zcat. The zcat utility allows you to view the contents of a compressed file without actually uncompressing it.

So the file created in the earlier example could be reconstructed using the zcat utility.

zcat Store.dump.gz | sqlite3 Store2

This may or may not work depending on your system. If this doesn’t work, you may need to use a different compression utility.