2 ms·
Another option is to use the `.dump` command that produces a series of SQL statements to recreate the database and populate it with data, just like good ol' mys
by kijin 4y ago
Another option is to use the `.dump` command that produces a series of SQL statements to recreate the database and populate it with data, just like good ol' mysqldump.
I'm not sure whether this is faster or safer than `.backup` which produces an actual database file with indexes and whatnot, but a benefit of the plain text output is that it's very flexible. The output can be piped to gzip or any other program on the fly, even across the network, without waiting for an intermediate file to be written in full. It can also be imported to other types of RDBMS with minimal changes.
- benbjohnson 4y agoThe .dump command is a great option for smaller databases. The main downsides are that it produces a larger output, it'll take longer to compress that output, and the restore time is much longer since all the commands have to be replayed. The benefit to using the backup API is that the file is ready-to-go as the database and can be started up immediately.
- yencabulator 4y agoBeware, `.dump` doesn't preserve pragmas like `user_version`, and some SQLite-using apps use that to manage schema migrations. sqlite> PRAGMA user_version=42; sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; COMMIT; sqlite>