SQLite Forum

Hot backup database in WAL mode by coping

Since our database can grow quite a bit, we'd like to avoid an extra copy of the main database file. Hence we like to avoid using the SQLite Online Backup API. The [Backup documentation page](https://www.sqlite.org/backup.html) mentions the old option of coping the SQLite database while open and holding a read lock. Is that method safe when using the WAL mode?

From what I understand, `BEGIN IMMEDIATE` should be sufficient to get the lock, then copying the database file as well as the `-wal` file should lead to a coherent backup, is this correct?

As an optimization, we were planning on using `PRAGMA wal_checkpoint(TRUNCATE);` before taking the lock. In this case we could even omit the `-wal` file. Afaict, on restore we'll have to make sure that a `-wal` file will be removed (if present) to avoid having a non-matching `-wal` file.

There is one small concern: From what I understand this does not guarantee that another transaction sneaks in between creating the checkpoint and taking the shared lock. Is there a way to make sure that the two operations are not interrupted by another command?

Best regards,