How to backup sqlite database?

Question

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?

• Colonel Thirty Two: SQLite has an API for this
• CL.: What language and driver are you using to access the database?
• thelolcat: i'm using PHP and the PDO extension
• Brian Minton: There is currently a feature request to expose the sqlite backup API in php: bugs.php.net/bug.php?id=70950
• Rüdiger Herrmann: There is also a VACUUM INTO command as an alternative to the backup API.

Answer 1

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.

• Mark Setchell: You can do it all in one line... sqlite3 m_database.sq3 ".backup m_database.sq3.bak"
• Googie: @mOna: This is just a mechanism for making backups. Replication means propagating changes on the fly (kind of a distributed database), which this won't do for you.
• Googie: @RonJohn It actually makes a copy of a file, but it also ansures, that write access to the database is restricted with proper locks, so it's an atomic operation, no intermediate modifications.
• Googie: @RadioControlled As I said earlier - this method locks database to avoid data corruption when there are concurrent users. If other user was first to lock the database (like in your case), then of course backup request will fail, cause it cannot secure a lock.
• Melvyn: You should also specify a timeout to minimize database is locked errors... sqlite3 source.db ".timeout 1000" ".backup backup.db"

Answer 2

.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?

• Francesc Rosas: On SQLite 3.8.2, .backup doesn't work as shown above ("missing FILENAME argument on .backup")
• Memristor: This is the best answer. If you are using .backup on a working database used by many it may not work because at some point the database is locked. So if you are using this in a CRON it won't work and won't tell you there is an error... better use .dump (allways works) or the API given by SQLite.
• Torsten Bronger: @Memristor But doesn't .dump lock the DB for others? FWIW, I would prefer a failed backup (with mail to the admin) to a disrupted service.
• Nashev: Please fix .backup syntax in your answer. It doesn't need '>' operator
• JohnyTex: @Memristor but locks exist for a reason, i.e. to not dump a corrupted database in the middle of a write. Problem is better solved by first issuing a .timeout [ms]; command before .backup; That way .backup will wait for lock to be released up to [ms] milliseconds before failing.

Answer 3

Short and simple answer would be

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

Answer 4

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.

Answer 5

People who want to do the backup from within their app should check out the backup API at https://www.sqlite.org/backup.html

• starball: note: some example usage could improve the long-term value of this post.