Summary: in this tutorial, you will learn how to use the SQLite dump command to back up and restore a database.
SQLite project delivers the sqlite3 tool that allows you to interact with the SQLite database using a command-line program.
By using the sqlite3 tool, you can use the SQL statements to query or update data in the database. Also, you can use special commands, which are known as dot commands to perform various useful database operations.
One of these dot-commands is the .dump command that gives you the ability to dump the entire database or tables into a text file.
Dump the entire database into a file using the SQLite dump command
The following command opens a new SQLite database connection to the chinook.db file.
C:\sqlite>sqlite3 c:/sqlite/chinook.db
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sqlite>Code language: JavaScript (javascript)To dump a database into a file, you use the .dump command. The .dump command converts the entire structure and data of an SQLite database into a single 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.
The following commands specify the output of the dump file to chinook.sql and dump the chinook database into the chinook.sql file.
sqlite> .output c:/sqlite/chinook.sql
sqlite> .dump
sqlite> .exitCode language: JavaScript (javascript)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 c:/sqlite/albums.sql
sqlite> .dump albums
sqlite> .quitCode language: JavaScript (javascript)The following picture shows the contents of the albums.sql file.