`ATTACH` and iterating over all tables with `INSERT INTO SELECT` is how `VACUUM INTO` is implemented (see src/vacuum.c).
A less complicated way to back up the database might be to run `BEGIN` followed by `PRAGMA user_version` to acquire a read lock, after which you can safely copy the database (and wal, if appropriate) files directly. Scott On Mar 18, 2019, at 08:21, Jonathan Moules <jonathan-li...@lightpear.com> wrote: > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
