top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

How to take SQLite dump ?

+2 votes
40 views

I need to take a dump file from the database SQLite. Can you suggest me how to take a dump?

Is it similar to MySQL dump?

posted Nov 30, 2018 by anonymous

Share this question
Facebook Share Button Twitter Share Button Google+ Share Button LinkedIn Share Button Multiple Social Share Button

1 Answer

+1 vote

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>
1
2
3
4
C:\sqlite>sqlite3 c:/sqlite/chinook.db
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sqlite>
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> .exit
1
2
3
sqlite> .output c:/sqlite/chinook.sql
sqlite> .dump
sqlite> .exit
Dump a specific table using the SQLite dump command
If you want to dump a specific table, you need to specify the table name followed 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> .quit
1
2
3
sqlite> .output c:/sqlite/albums.sql
sqlite> .dump albums
sqlite> .quit
The following picture shows the content of the albums.sql file.

SQLite dump database example

Dump tables structure only using schema command
If you want to dump the structures of tables in a database, you use the .schema command. The following commands set the output file to chinook_structure.sql file and save the structures of tables into the chinook_structure.sql file.

sqlite> .output c:/sqlite/chinook_structure.sql
sqlite> .schema
sqlite> .quit
1
2
3
sqlite> .output c:/sqlite/chinook_structure.sql
sqlite> .schema
sqlite> .quit
The following picture shows the content of the chinook_structure.sql file.

SQLite dump structure

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:

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

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

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
1
sqlite> .output data.sql
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;
1
sqlite> select * from artists;
Check the content of the data.sql file, if everything is fine, you will see the following output:

SQLite dump data only

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

In this tutorial, you have learned how to dump data into a text file using the SQLite dump command and other commands.

answer Mar 15 by Rushabh Verma R.
Similar Questions
+1 vote

I want to check specified table contains value or its empty. I used number of complex method to find but no luck, any body can help me.

+2 votes

I am using SQLite through either Python 2.5 or 2.7, which is the sqlite3 module. In a desktop application, every now and then, and in a fairly irreproducible way, when committing to the database I get this error:

"sqlite3.OperationalError: SQL logic error or missing database"

I thought this was a PySqlite generated error, but now I see the same error is seen with Ruby, PHP, C++ and other languages, so now I think it is generated by SQLite itself...but I really don't know.

If I try additional commits in that same instance of my app being open, it gives me the same error every time. If I close the app and re-open it, it does not give me this error, with the same or very similar data
being written in the same routines. So I "know" that the code as written is correct (a significant--greater than 90%?--of the time I don't see this error).

In terms of what is causing this, I don't know. But I've noticed that on the occasions that this has happened my computer's RAM was pretty bogged down and my computer is pretty laggy. That said, I've had other times when my RAM was hogged just as much and it didn't do this.

This error might go away if I used a newer/cleaner/more RAM computer, but I want to "stress test" my application for those who may be using similarly clunky computers--I want to try to avoid it even for older
model computers.

+1 vote

In my table I have three unique column this can be referred by other table.
Which key I have to use for this.

Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...