Menu

Subscribe

sqlite3 maintenance

Dump database to SQL
echo ".dump" | sqlite3 /path/to/database_file.db > /path/to/output.sql

Detect malformation (Output ok means database is valid)
sqlite3> PRAGMA integrity_check;

New database from SQL contents
echo ".read /path/to/output.sql" | sqlite3 /path/to/new_database_file.db

Making your dump compatible with other SQL servers (MariaDB/MySQL)

  • Remove double quotes around column names, search CREATE TABLE, CREATE INDEX
  • Remove PRAGMA foreign_keys=OFF;
  • Remove BEGIN TRANSACTION; and COMMIT; if you don't feel the need to create and populate a full DB in one transaction...