Re: “How to back up SQLite databases the right way (not by copying them!)”

On 2026-05-10, Lawrence D’Oliveiro <[email protected]> wrote:
> Mr Serdar Yegulalp occasionlly comes up with useful titbits of
> information, but I’m not sure I’d count this among them
><https://www.infoworld.com/video/4164431/how-to-back-up-sqlite-databases-the-right-way-not-by-copying-them.html>.
>
> Does anybody else use SQLite’s backup API? I’ve never bothered,
> because SQLite is a single-user DBMS. To backup a database, I just
> shut down the program accessing it, and do a regular file copy on the
> database file. Simple.

You don't even need to do that - just lock the database during the copy.
For most SQLite instances where the data is small the duration of the
lock would probably be barely noticeable.

> I think the backup API might be useful from within the app accessing
> the database itself, to provide its own backup function while it is
> still running. Again, not something I’ve felt much need for; in my
> experience, long-running service-style apps tend to use multiuser
> DBMSes, which can handle multiple simultaneous connections without
> things getting confused. And backing them up is easily done by making
> yet another connection from a database dump utility, usually provided
> as part of the DBMS package.

They explain why the backup API exists here:

https://sqlite.org/backup.html

  * Any database clients wishing to write to the database file while a
    backup is being created must wait until the shared lock is
    relinquished.
  * It cannot be used to copy data to or from in-memory databases.
  * If a power failure or operating system failure occurs while copying
    the database file the backup database may be corrupted following
    system recovery.

I must admit I don't understand the last one - a power failure during
the backup would be incredibly rare, and if it happens, just re-do the
backup again. (Obviously you wouldn't overwrite the previous backup
with the new backup, in case the primary database died during the
backup.)