Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to backup sqlite database?

What's the proper way to do it? Do I just copy the .sq3 file?

What if there are users on the site and file is being written while it's being copied?

like image 471
thelolcat Avatar asked Sep 04 '14 21:09

thelolcat


People also ask

How do I backup SQLite database in Python?

Take a backup of SQLite database from PythonUsing a connection. backup() method, you can take the backup of the SQLite database. This function takes a backup of the SQLite database, and a copy will be written into the argument target , which must be another Connection instance.

How do I backup SQLite database on Android?

1. copy your db to sdcard 2. if reinstall, copy db back to the app. @earthw0rmjim Any approach in which I can get the original state retained without the network.

Can I copy SQLite database?

If your application install in android device, you can copy the sqlite DB file to the external folder with following code, then you can send the DB file by email or upload the DB file by Google drive, then you can download the file in PC.


4 Answers

The sqlite3 command line tool features the .backup dot command.

You can connect to your database with:

sqlite3 my_database.sq3

and run the backup dot command with:

.backup backup_file.sq3

Instead of the interactive connection to the database, you can also do the backup and close the connection afterwards with

sqlite3 my_database.sq3 ".backup 'backup_file.sq3'"

Either way the result is a copy named backup_file.sq3 of the database my_database.sq3.

It's different from regularly file copying, because it takes care of any users currently working on the database. There are proper locks set on the database, so the backup is done exclusively.

like image 116
Googie Avatar answered Sep 26 '22 02:09

Googie


.backup is the best way.

sqlite3 my_database .backup my_database.back

you can also try .dump command , it gives you the ability to dump the entire database or tables into a text file. If TABLE specified, only dump tables matching LIKE pattern TABLE.

sqlite3 my_database .dump > my_database.back

A good way to make an archival copy using dump and store, Reconstruct the database at a later time.

sqlite3 my_database .dump | gzip -c > my_database.dump.gz
zcat my_database.dump.gz | sqlite3 my_database

Also check this question Do the SQLite3 .backup and .dump commands lock the database?

like image 23
Lava Sangeetham Avatar answered Sep 27 '22 02:09

Lava Sangeetham


Short and simple answer would be

sqlite3 m_database.sq3 ".backup m_database.sq3.bak"

like image 27
Saurabh Dhage Avatar answered Sep 25 '22 02:09

Saurabh Dhage


For streaming replication of SQLite, check out Litestream.

Compared to using the sqlite3-backup command, this is automatic, and incremental.

If you need to restore from backup, the data will be a lot more up to date than if you did a regular backup every hour for example.

like image 30
Tobias Bergkvist Avatar answered Sep 26 '22 02:09

Tobias Bergkvist