ELS Howto: SQLite Backup and Restore


Synopsis

Many of the PHP-based applications that can run on an Elemental Linux Server (ELS) feature a SQLite database backend. This document will demonstrate how to backup and restore those databases.


Prerequisites

SQLite is included in the base packages and no installation is required. However, you may wish to install a phpBB web forum or a Serendipity blog to have some live data to work with.


Preparation

You will need to create a directory for databases and a sample database.

Example

The following example shows SQLite database creation that is consistent with the examples used in the ELS Howto: phpBB Web Forum and ELS Howto: Serendipity Blog documents.

mkdir /home/sqlite
chmod daemon /home/sqlite
touch sample.db
chmod daemon /home/sqlite/sample.db

Creating sample data

Use your favorite text editor to create the following file and save it as music.sql:

create table music(artist varchar(40), title varchar(60));
insert into music values('Beastie Boys','Paul''s Boutique');
insert into music values('John, Elton','Goodbye Yellow Brick Road');
insert into music values('Parliment','Mothership Connection');

Now use the file you just created to populate your sample database.

cd /home/sqlite
sqlite3 sample.db < music.sql

Verify that the data were successfully entered before continuing.

sqlite3 sample.db 'select * from music;'

Beastie Boys|Paul''s Boutique
John, Elton|Goodbye Yellow Brick Road
Parliment|Mothership Connection

Note

Pay particular attention to the repeated single quote in Paul''s Boutique. This is not a typo, it is how SQL escapes literal single quotes. If you forget to do this when entering data, you'll see an error about an incomplete SQL statement.


Backing up the database

To make a backup copy of the database, simply do a "dump" and redirect the results to a file.

cd /home/sqlite
sqlite3 sample.db .dump > sample.bak

Restoring the database

Restoring the database from a backup is just as easy as backing up, except we must make sure the destination database is empty first. Alternatively you may want to delete or rename the destination database and let sqlite create a new one for you.

cd /home/sqlite
mv sample.db sample.db.old
sqlite3 sample.db < sample.bak

After restoring, verify the results.

sqlite3 sample.db 'select * from music;'

Beastie Boys|Paul's Boutique
John, Elton|Goodbye Yellow Brick Road
Parliment|Mothership Connection

Real World Practice

To gain confidence with SQLite backups and restores, try backing up the database from phpBB or Serendipity. After dumping the database to a backup, try to restore it. (Warning: make sure to rename the original database and not delete it. That way if you make a mistake it's easily corrected.)

Once you are comfortable dumping and restoring databases, make a cron job to do a nightly backup of a SQLite database on your system.