{"id":1124,"date":"2016-05-29T09:39:50","date_gmt":"2016-05-29T02:39:50","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=1124"},"modified":"2022-04-03T15:41:08","modified_gmt":"2022-04-03T08:41:08","slug":"sqlite-dump","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-dump\/","title":{"rendered":"How To Use The SQLite Dump Command"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQLite dump command to back up and restore a database.<\/p>\n\n\n\n

SQLite project delivers the sqlite3<\/code> tool that allows you to interact with the SQLite database using a command-line program.<\/p>\n\n\n\n

By using the sqlite3<\/code> 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.<\/p>\n\n\n\n

One of these dot-commands is the  .dump<\/code> command that gives you the ability to dump the entire database or tables into a text file.<\/p>\n\n\n\n

Dump the entire database into a file using the SQLite dump command<\/h2>\n\n\n\n

The following command opens a new SQLite database connection to the chinook.db<\/code> file.<\/p>\n\n\n

C:\\sqlite>sqlite3 c:\/sqlite\/<\/span>chinook.db\nSQLite version 3.13<\/span>.0<\/span> 2016<\/span>-05<\/span>-18<\/span> 10<\/span>:57<\/span>:30<\/span>\nEnter \".help\"<\/span> for<\/span> usage hints.\nsqlite><\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
To dump a database into a file, you use the .dump<\/code> command. The .dump<\/code> command converts the entire structure and data of an SQLite database into a single text file.<\/p>\n\n\n\n
By default, the .dump<\/code> command outputs the SQL statements on screen. To issue the output to a file, you use the .output FILENAME<\/code> command.<\/p>\n\n\n\n
The following commands specify the output of the dump file to chinook.sql<\/code> and dump the chinook database into the chinook.sql<\/code> file.<\/p>\n\n\n
sqlite> .output c:\/sqlite\/<\/span>chinook.sql\nsqlite> .dump\nsqlite> .exit<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Dump a specific table using the SQLite dump command<\/h2>\n\n\n\n
To dump a specific table, you specify the table name after the .dump<\/code> command. For example, the following command saves the albums<\/code> table to the albums.sql<\/code> file.<\/p>\n\n\n
sqlite> .output c:\/sqlite\/<\/span>albums.sql\nsqlite> .dump albums\nsqlite> .quit<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the contents of the albums.sql<\/code> file.<\/p>\n\n\n\n
\"SQLite<\/figure>\n\n\n\n
Dump tables structure only using schema command<\/h2>\n\n\n\n
To dump the table structures in a database, you use the .schema<\/code> command. <\/p>\n\n\n\n
The following commands set\u00a0the output file to chinook_structure.sql<\/code> file and save the table structures into the chinook_structure.sql<\/code> file:<\/p>\n\n\n
sqlite> .output c:\/sqlite\/<\/span>chinook_structure.sql\nsqlite> .schema\nsqlite> .quit<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the content of the chinook_structure.sql<\/code> file.<\/p>\n\n\n\n
\"SQLite<\/figure>\n\n\n\n
Dump data of one or more tables into a file<\/h2>\n\n\n\n
To dump the data of a table into a text file, you use these steps:<\/p>\n\n\n\n
First, set the mode to insert<\/code> using the .mode<\/code> command as follows:<\/p>\n\n\n
sqlite<\/span>> .mode<\/span> insert<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
From now on, every SELECT statement<\/a> will issue the result as the INSERT statements<\/a> instead of pure text data.<\/p>\n\n\n\n
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<\/code> file.<\/p>\n\n\n
sqlite<\/span>> .output<\/span> data<\/span>.sql<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
sqlite> select<\/span> * from<\/span> artists;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Check the content of the data.sql<\/code> file, if everything is fine, you will see the following output:<\/p>\n\n\n\n
\"SQLite<\/figure>\n\n\n\n
To dump data from other tables, you need to issue the SELECT<\/code> statements to query data from those tables.<\/p>\n\n\n\n
In this tutorial, you have learned how to dump data into a text file using the SQLite dump command and other commands.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Third, issue the SELECT<\/a> statements to query data from a table that you want to dump. The following command returns data from the artists<\/code> table.<\/p>\n\n\n