Backing Up and Restoring Data

It is, of course, essential to back up your data regularly. SQLite stores databases to the filesystem, so backing up your databases can be as easy as taking a copy of the database files themselves and putting them somewhere safe.

However, if you are copying the database file, can you be sure that it is not being written to at the time you issue the copy command? If there's even a small chance that a SQLite write operation could happen during the copy, you should not use this method to back up your database to ensure that data corruption cannot occur.

SQLite implements locking at the database level using a lock on the database file itself, which works to the extent that other processes using the SQLite library know when the database is being written to. Many processes can read from a database at the same time; however, a single writer process locks the entire database, preventing any other read or write operation taking place.

On Windows platforms, if SQLite has locked the file the operating system will acknowledge the lock and will not allow you to copy the file until SQLite is done with it. Likewise the copy operation will lock the database file so that SQLite cannot access it. However, on Unix systems, file locks are advisoryin other words they tell you that the file is locked if you care to check, but do not otherwise prevent access. Therefore it is entirely possible to initiate a cp command while SQLite has a database file locked for writing.

The .dump Command

The .dump command in sqlite provides a simple way to create a backup file in the form of a list of SQL statements that can be used to re-create the database from scratch. The CREATE TABLE statements are preserved and one INSERT command is written for each row of data in the table.

Note

Because the .dump command is part of an application that uses the SQLite library, there are no issues with file locking to worry about. If the database is locked at the moment you attempt to fetch the rows from the database, the busy handler will be invoked, which, in sqlite, is to wait for the specified timeout before returning an error message.

A backup operation can therefore be performed as follows:

 $ sqlite dbname .dump > backup.sql 

Future restoration from the generated backup.sql is simple:

 $ sqlite dbname < backup.sql 

You can optionally pass a table name to .dump to extract only the schema and records for that table. The following example shows the dump file produced for the contacts table:

 $ echo '.dump contacts'| sqlite db1 BEGIN TRANSACTION; CREATE TABLE contacts (   id INTEGER PRIMARY KEY,   first_name CHAR,   last_name  CHAR,   email      CHAR); INSERT INTO contacts VALUES(1,'Chris','Newman','[e-mail]'); INSERT INTO contacts VALUES(2,'Paddy','O''Brien','[e-mail]'); INSERT INTO contacts VALUES(3,'Tom','Thomas','[e-mail]'); INSERT INTO contacts VALUES(4,'Bill','Williams','[e-mail]'); INSERT INTO contacts VALUES(5,'Jo','Jones','[e-mail]'); COMMIT; 

Note that the INSERT statements generated do not contain a column list before the VALUES keyword. If you are using the .dump output to restore a table exactly as it used to be, this is no problem; however, you cannot alter the table schema in the CREATE TABLE statement without also modifying every INSERT in the extract file.

The .dump command can act as a handy workaround for the lack of ALTER TABLE command in SQLite if you are able to adjust the INSERT commands accordingly. The following example uses sed to change the SQL commands to full inserts so that you can go ahead and add columns to the CREATE TABLE statement to re-create the database from this file with the new schema.

 $ echo .dump | sqlite dbname | \      sed e 's/VALUES/(id,first_name,last_name,email) VALUES/g' \      > backup.sql 

Whether you decide to use .dump for regular backups or to simply copy the database file itself will depend on several factors. The output from .dump is often smaller than the SQLite database file and will certainly compress much smaller using gzip or some similar utility.

However, restoring from an SQL file can take some time, particularly if there are a lot of indexes to be rebuilt after the data has been loaded. As a backup should be as quick and easy to restore as possible, copying the SQLite database file may be the correct choice.

Using .dump is a better choice for a long-term archive of old data. Although there is, in theory, no reason that the same version of SQLite will not be available several years from now, you might actually want to load your archived data into a newer version of SQLite or even some other database system. The SQL output file produced by .dump will be fairly portable.