A recent study tested five methods for backing up SQLite databases, including file copying, SQLite Backup API, and VACUUM INTO. The tests revealed that simple file copying, such as shutil.copy2(), often results in incomplete backups when the database is in WAL mode, as it fails to capture data stored in the -wal journal file.
The tests showed that while integrity_check might return 'ok' for these partial copies, they can be missing up to 90% of data. Copying the -wal file separately is also unreliable, as non-atomic operations can lead to inconsistent snapshots, especially if a checkpoint occurs between file copies.
The SQLite Backup API and VACUUM INTO proved to be the most reliable methods for creating consistent snapshots. Both mechanisms correctly handle WAL mode and ensure all committed transactions are included in the backup, even while the application continues to write data.
VACUUM INTO offers the additional benefit of compacting the database by removing free pages, resulting in smaller file sizes compared to the Backup API, which preserves the original structure. However, VACUUM INTO requires more disk I/O and time, which may impact performance on large databases.
The study also highlighted that VACUUM INTO will fail with an OperationalError if the destination file already exists, requiring careful management of backup schedules and file cleanup to avoid errors.
