Written by MrBackup
on June 4, 2014
Historically, backups (copies) of SQLite databases have been created using the following method:
- Establish a shared lock on the database file using the SQLite API (i.e. the shell tool).
- Copy the database file using an external tool (for example the unix ‘cp’ utility or the DOS ‘copy’ command).
- Relinquish the shared lock on the database file obtained in step 1.
This procedure works well in many scenarios and is usually very fast. However, this technique has the following shortcomings:
- 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.
Attempting a simple copy&paste of such a SQL database while it is in use, will typically result in data corruption and a failed backup.
These considerations are commonly associated with all SQL database systems, such as
- MySQL
- Microsoft SQL Server
- PostgreSQL
- Firebird
- SQLite
