How to backup sqlite database?


Database Problem Overview

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?

Database Solutions

Solution 1 - Database

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.

Solution 2 - Database

.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

Solution 3 - Database

Short and simple answer would be

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


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionthelolcatView Question on Stackoverflow
Solution 1 - DatabaseGoogieView Answer on Stackoverflow
Solution 2 - DatabaseLava SangeethamView Answer on Stackoverflow
Solution 3 - DatabaseSaurabh DhageView Answer on Stackoverflow