Hi Simon,

Thanks for your thoughts. Sorry, I should have been clearer: I have no way of knowing if there are other open connections to the file - there may be as it's a web-application. So I'll assume there are connections.

At this point I'm starting to think that the best option is to create a new database with the requisite structure and copy the data across via an ATTACH (there are only two tables and one will almost always be empty at this point).

Any other thoughts welcome though!

On 2019-03-18 13:37, Simon Slavin wrote:
On 18 Mar 2019, at 1:10pm, Jonathan Moules <jonathan-li...@lightpear.com> wrote:

I was wondering if there was a good way of backing up an SQLite database if you 
do *not* have access to the SQLite command line tool (which I know has .backup 
- https://stackoverflow.com/a/25684912). [snip]
I've considered simply running "PRAGMA wal_checkpointer;" and then copying the 
file immediately after that, but that still seems prone to error.
Ideally, rather than force a WAL checkpoint, close the file, make the copy, 
then open it again.  This does not take significantly more time, and it ensures 
that you will copy the right thing no matter what caching and optimization your 
tools are trying to do.

In more general terms ...

Are you trying to backup while the database is being modified using SQLite 
function calls ?

If not, then the data is just a single file.  Assuming all programs using 
SQLite calls closed their connections properly, just copy the file using any 
file copy commands, or file copy primatives in your favourite programming 
language.  In PHP I'd use the built-in copy command:


There may be a journal file there and you can copy that too, but just the 
database file is enough for a backup for emergency purposes.

If you're trying to copy a file while connections still have it open then you 
should use SQLite API calls to do it.  The obvious ones are in the SQLite 
Online Backup API, which is the set of calls underlying the '.backup' command 
you mentioned.  You can find documentation for this here:


Unfortunately I don't think the PHP sqlite3 tools give access to this API.

Hope that helps.  Don't hesitate to get back to us if we can help.
sqlite-users mailing list

sqlite-users mailing list

Reply via email to