On May 8, 2015, at 4:47 AM, Simon Slavin <slavins at bigfraud.org> wrote: > On 8 May 2015, at 8:09am, Jeff M <jmat at mac.com> wrote: > >> For a restore, the current DB is closed, then a backup file is copied in and >> opened. > >> I've been using this scheme for years without a problem. But, should I >> flush, or close, or lock before the copy? > > You need to close your connection, take the copy, then reopen your > connection. Just like you do when restoring a backup. While there is a > connection to the database some data from it might be in an associated > journal file. Only by closing all connections can you be sure that the > database is up-to-date, consistent with itself, and can be opened without > SQLite thinking it needs to repair something. > > That will cover you for all operations, journal types, journal settings, and > platforms. For some settings and situations it might be possible to get away > with something less than closing the connection, but then you would need to > be careful about what you were doing, whereas the above instructions are > simple and will definitely work every time.
If you're using wal mode journalling (which I'd recommend for running on iOS devices), you'll want to execute `PRAGMA wal_checkpoint(TRUNCATE);` before closing your last connection to ensure you don't leave any data behind in the journal when you move the database file. Alternately, you could store the database in a directory and treat it as a bundle. That would guarantee that you never lose track of any associated files.
