Don't forget to stop writing to the database while doing the backup, otherwise you can run into an infinite loop if you write faster than sqlite3 .backup is doing the backup :D

Learned that the hard way when implementing sqlite3 backups on Gladys Assistant ( open-source home automation platform https://github.com/GladysAssistant/Gladys )

Can you elaborate more? I think the cron-solution will be unable to synchronize with your application code to determine when to stop writing, so more background and your solution would be of interest.

As I understand it, while you do the backup, other writes should go to the WAL log and only get commited until after the backup?

It's not what I've experienced!

In my experience, as soon as there is some new data coming in the DB, the .backup command will continue, and if the writes are not stopping, the backup will never stop as well :D

In Gladys case, we put in the application logic a blocking transaction to lock writes during the backup. I haven't found any other way to avoid infinite backups in case of write-heavy databases

> The VACUUM command with an INTO clause is an alternative to the backup API for generating backup copies of a live database....The VACUUM INTO command is transactional in the sense that the generated output database is a consistent snapshot of the original database.

EDIT: Litestream docs will also recommend that: https://github.com/benbjohnson/litestream.io/issues/56