How to backup and restore SQLite database?

SQLite provides multiple backup and recovery methods:

1. File Copy Backup

   • Simplest backup method: directly copy the database file
   • Need to ensure no write operations are in progress
   • Suitable for small databases or offline backups

   bash
   cp database.db database_backup.db

2. Online Backup API

   • Use SQLite's backup API for online backup
   • No need to stop database service
   • Supports incremental backup

   sql
   -- Command line tool
   sqlite3 source.db ".backup backup.db"

3. VACUUM INTO Backup

   • Use VACUUM INTO command to create a database copy
   • Simultaneously performs database optimization and defragmentation

   sql
   VACUUM INTO 'backup.db';

4. Export SQL Script

   • Use .dump command to export as SQL script
   • Can selectively export specific tables or data

   bash
   sqlite3 database.db ".dump" > backup.sql

5. Recovery Methods

   • Recover from file backup: directly copy backup file
   • Recover from SQL script: execute exported SQL script

   bash
   sqlite3 database.db < backup.sql

6. Incremental Backup

   • When using WAL mode, can backup WAL files
   • Perform full backup after regular checkpoints
   • Combine with WAL files for point-in-time recovery

7. Backup Best Practices

   • Regularly perform automatic backups
   • Keep multiple historical backup versions
   • Verify backup file integrity
   • Test recovery process to ensure availability
   • Consider encrypting sensitive data

8. Cross-Platform Backup

   • SQLite database files are cross-platform
   • Can copy database files between different operating systems
   • Pay attention to file paths and permission issues

Choosing the appropriate backup strategy depends on the importance of the application, frequency of data changes, and recovery time requirements.