SQLite .DUMP

The .dump command enables you to dump the entire database or tables into a text file.

By default, the .dump command outputs the SQL statements on screen. To issue the output to a file, you use the .output FILENAME command.

An example:

sqlite> .output ~/Documents/sqlite/db/new_world.db
sqlite> .dump
sqlite> .exit

Easy Dumping and Restoring (its Reverse)

You can use .dump dot command to export complete database in a text file using the following SQLite command at the command prompt.

$sqlite3 my_database.db .dump > my_database.sql

The above command will convert the entire contents of my_database.db database into SQLite statements and dump it into ASCII text file my_database.sql. You can perform restoration from the generated my_database.sql in a simple way as follows:

$sqlite3 my_database.db < my_database.sql

Dump a specific table using the SQLite .dump command

To dump a specific table, you specify the table name after the .dump command. For example, the following command saves the albums table to the albums.sql file.

sqlite> .output ~/Documents/sqlite/db/Euphoria.sql
sqlite> .dump Euphoria
sqlite> .quit

Dump tables structure only using .schema command

To dump the table structures in a database, you use the .schema command.

The following commands set the output file to new_world_structure.sql file and save the table structures into the new_world_structure.sql file:

sqlite> .output ~/Documents/sqlite/db/new_world_structure.sql
sqlite> .schema
sqlite> .quit

Dump data of one or more tables into a file

To dump the data of a table into a text file, you use these steps:

  1. First, set the mode to insert using the .mode command as follows:

    sqlite> .mode insert

    From now on, every SELECT statement will issue the result as the INSERT statements instead of pure text data.

  2. Second, set the output to a text file instead of the default standard output. The following command sets the output file to the data.sql file.

    sqlite> .output data.sql

  3. Third, issue the SELECT statements to query data from a table that you want to dump. The following command returns data from the artists table.

    sqlite> select * from artists;

To dump data from other tables, you need to issue the SELECT statements to query data from those tables.