If you need to back up a live SQLite database, the worst thing you can do is copy the main .db file while your application is writing to it. A plain file copy captures pages from different moments. The result is an unusable mess. The correct approach uses SQLite's own tools, which create a coherent snapshot even while the database is being read and written. Here are the safe methods, why a simple copy fails, and how to avoid the most common backup mistakes.
Why a Plain File Copy Fails
When you copy a .db file while the database is open and in use, you are copying a moving target. The engine writes pages in a specific order. If you read the file during a write, you capture some pages from before the write and some from after. That mix of old and new pages is not a valid snapshot of any single point in time. The backup looks corrupt when you try to open it later.
There is another problem. SQLite can run in WAL mode (Write-Ahead Logging). In that mode, committed transactions land first in a separate -wal file. Only later are they moved into the main .db file. Copy only the .db file and you miss those committed transactions still sitting in the -wal file. The backup is missing recent data, even though the file itself looks fine.
A plain file copy is not a reliable way to back up a live SQLite database. You need a method that produces a coherent snapshot. SQLite provides several.
Use the Sqlite3 Shell .backup Command
The Shell Backup Method
The sqlite3 shell's .backup backup.db command is the simplest way to get a dependable backup of a live database. It uses SQLite's Online Backup API, which reads the database in a way that keeps it coherent even while other connections are writing. The command works with both the main database and any attached databases. It produces a complete, coherent copy in a new file.
Open a terminal and run:
sqlite3 source.db ".backup backup.db"This creates backup.db as a coherent snapshot of source.db at the moment the backup runs. It is safe to run while your application uses the database. You do not need to stop the application or lock the database for long.
The .backup command works from the shell, but you can also call the Online Backup API directly from your application code if you need more control. The shell command is the easiest way to get a correct backup without writing extra code.
Use VACUUM INTO for a Compacted Copy
The VACUUM INTO Statement
Another safe method is the VACUUM INTO 'backup.db'; SQL statement. This command has been available since SQLite version 3.27.0 (released in 2019). It writes a coherent, compacted copy of the database to a new file. The copy reflects a single point in time, even if other connections write to the original database while the command runs.
Connect to the database and execute:
VACUUM INTO 'backup.db';This method has a few advantages. It produces a compacted file. Free pages and unused space are removed, so the backup can be smaller than the original. It is a pure SQL command, so you can run it from any SQLite client, including your application code.
The main trade-off: VACUUM INTO writes the entire database to a new file, which takes time and disk space if the database is large. For most applications, it is a straightforward way to create a backup without stopping the database.
Checkpoint the WAL File First
When You Must Copy the File Directly
If you prefer to copy the .db file directly, you must first make sure the database is in a coherent state. In WAL mode, that means moving the contents of the -wal file into the main database file. Use the PRAGMA wal_checkpoint(TRUNCATE); command.
This command does two things:
- It copies all committed transactions from the -wal file into the main .db file.
- It truncates the -wal file, so it no longer contains any pending data.
After the checkpoint completes, the main .db file alone is complete, provided that no other connection writes to the database between the checkpoint and your copy. Run the checkpoint and then copy the file immediately. If any other connection writes in that window, checkpoint again.
This method is less convenient than .backup or VACUUM INTO. It requires you to coordinate with all connections to avoid new writes. But it works if you are already managing the database manually and you understand the risk.
Avoid Cloud-Sync Folders for Live Backups
The Cloud-Sync Trap
Cloud-sync folders like Dropbox, OneDrive, or iCloud Drive are convenient for sharing files. They are not a safe way to back up a live SQLite database. These services sync files in the background. They can sync the .db file and the -wal file at different moments. If the service uploads the -wal file before the .db file, or vice versa, the copy on the cloud server ends up unusable.
Even if you checkpoint before syncing, the sync service might pick up the file mid-write. It might sync the -wal file after you have already copied the .db. The result is a backup that looks fine but contains missing or corrupt data.
If you must use a cloud-sync service, create a coherent backup first using one of the methods above. Then sync that backup file. Do not sync the live database files directly.
Verify and Restore Your Backup
Verify Before You Trust It
After you create a backup, verify that it is usable. Run the PRAGMA integrity_check; command on the backup copy. If the command returns ok, the backup is sound and can be opened. If it returns an error, the backup is corrupt. Recreate it.
Restoring the Right Way
Restoring a backup is straightforward, but you must follow a few rules:
- Make sure no process has the database open. Close all connections to the database file.
- Replace the current .db file with the backup copy.
- Delete any stale -wal and -shm files that belong to the old database. These files contain old WAL data and shared-memory information. Leaving them can cause errors or data loss.
After restoring, run PRAGMA integrity_check; again on the restored database to confirm it is healthy before you reopen it in your application.
Choose the Right Method for Your Situation
For most live backups, the sqlite3 .backup command is the best choice. It is simple, uses the Online Backup API, and produces a coherent copy without stopping the database. If you are already using SQL from your application, VACUUM INTO is a good alternative, especially if you want a compacted backup.
If you are in a hurry and you understand the risks, checkpoint and then copy the .db file. This is only safe if you are sure no other connection will write during the copy. For automated or unattended backups, use one of the coherent methods.
If you ever end up with a corrupt backup or a damaged database, recovery tools exist. The sqlite.repair website offers an online service that can recover data from corrupted SQLite files. It is free to use and does not add any watermark to your recovered data.