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!
Cheers,
Jonathan
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:
<https://secure.php.net/manual/en/function.copy.php>
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:
<https://www.sqlite.org/backup.html>
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@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
