How to backup a SQLite database to a file.

There are several ways of backing up a SQLite database to file.

• Use the .backup command.
• Use the .clone command.
• Use the .dump command.
• Copy the file on the file system.

The .backup Command

This command backs up a database to a file. It accepts a database alias (i.e. the database to backup), and a file name (for the backup file).

If you omit the database alias, it will use the main database.

Here's an example:

.backup mybackup.db

This will create a file called backup.db containing a backup of the database. You can attach this back into the SQLite3 command-line shell if required (then do a .databases to view the list of database connections):

sqlite> ATTACH DATABASE 'mybackup.db' AS MyBackup;sqlite> .databasesseq  name             file---  ---------------  ----------------------------------------------------------0    main             /Users/quackit/sqlite/music.db1    temp2    MyBackup         /Users/quackit/sqlite/mybackup.db

In practice, you would probably backup the database to a different location. Therefore, you would provide the full file path in the .backup command. For example, .backup /remote/folder/mybackup.db

The .clone Command

The .clone command is similar to the .backup command. However, .clone only uses the current database, so you can't specify another database to clone.

To clone the current database, type .clone followed by the name of the database file for the cloned data.

Like this:

.clone myclone.db

Running that looks like this:

sqlite> .clone myclone.dbArtists... doneAlbums... doneAlbums2... doneCatalog... doneGenres... done

You can attach the cloned database just like the other one:

sqlite> ATTACH DATABASE 'myclone.db' AS MyClone;sqlite> .databasesseq  name             file---  ---------------  ----------------------------------------------------------0    main             /Users/quackit/sqlite/music.db1    temp2    MyBackup         /Users/quackit/sqlite/mybackup.db3    MyClone          /Users/quackit/sqlite/myclone.db

The .dump Command

You can use the .dump command to dump the database to an ASCII file. For example, you could dump it to an .sql file that contains the SQL statements to generate the database from.

Dump the whole DB

This example dumps the music.db file to music.sql.

sqlite3 music.db .dump > music.sql

Contents of music.sql (scrolling required):

PRAGMA foreign_keys=OFF;BEGIN TRANSACTION;CREATE TABLE Artists(ArtistId    INTEGER PRIMARY KEY,  ArtistName  TEXT NOT NULL, Bio TEXT);INSERT INTO "Artists" VALUES(1,'Joe Satriani',NULL);INSERT INTO "Artists" VALUES(2,'Steve Vai',NULL);INSERT INTO "Artists" VALUES(3,'The Tea Party',NULL);INSERT INTO "Artists" VALUES(4,'Noiseworks',NULL);INSERT INTO "Artists" VALUES(5,'Wayne Jury',NULL);INSERT INTO "Artists" VALUES(6,'Mr Percival',NULL);INSERT INTO "Artists" VALUES(7,'Iron Maiden',NULL);INSERT INTO "Artists" VALUES(8,'Atmasphere','Australian jazz band centred around polyrhythms.');INSERT INTO "Artists" VALUES(9,'Ian Moss',NULL);INSERT INTO "Artists" VALUES(10,'Magnum',NULL);INSERT INTO "Artists" VALUES(13,'Primus',NULL);INSERT INTO "Artists" VALUES(14,'Pat Metheny',NULL);INSERT INTO "Artists" VALUES(15,'Frank Gambale',NULL);INSERT INTO "Artists" VALUES(16,'Mothers of Invention',NULL);CREATE TABLE Albums(AlbumId     INTEGER PRIMARY KEY,  AlbumName   TEXT NOT NULL,ReleaseDate TEXT,ArtistId INTEGER NOT NULL,FOREIGN KEY(ArtistId) REFERENCES Artists(ArtistId));INSERT INTO "Albums" VALUES(1,'Killers','1981',7);INSERT INTO "Albums" VALUES(2,'Powerslave','1984',7);INSERT INTO "Albums" VALUES(3,'Surfing with the Alien','1987',1);INSERT INTO "Albums" VALUES(4,'Heavy as a Really Heavy Thing','1995',11);INSERT INTO "Albums" VALUES(6,'Out of the Loop','2007',6);INSERT INTO "Albums" VALUES(7,'Suck on This','1989',13);INSERT INTO "Albums" VALUES(8,'Pork Soda','1993',13);INSERT INTO "Albums" VALUES(9,'Sailing the Seas of Cheese','1991',13);INSERT INTO "Albums" VALUES(10,'Flying in a Blue Dream','1989',1);INSERT INTO "Albums" VALUES(11,'Black Swans and Wormhole Wizards','2010',1);INSERT INTO "Albums" VALUES(12,'Somewhere in Time','1986',7);CREATE TABLE Albums2(AlbumId INT,AlbumName TEXT,ArtistId INT);INSERT INTO "Albums2" VALUES(1,'Killers',7);INSERT INTO "Albums2" VALUES(2,'Powerslave',7);INSERT INTO "Albums2" VALUES(3,'Surfing with the Alien',1);INSERT INTO "Albums2" VALUES(4,'Heavy as a Really Heavy Thing',11);INSERT INTO "Albums2" VALUES(5,'Yummy Yummy',17);INSERT INTO "Albums2" VALUES(6,'Out of the Loop',6);INSERT INTO "Albums2" VALUES(7,'Suck on This',13);INSERT INTO "Albums2" VALUES(8,'Pork Soda',13);INSERT INTO "Albums2" VALUES(9,'Sailing the Seas of Cheese',13);INSERT INTO "Albums2" VALUES(10,'Flying in a Blue Dream',1);INSERT INTO "Albums2" VALUES(11,'Black Swans and Wormhole Wizards',1);INSERT INTO "Albums2" VALUES(12,'Somewhere in Time',7);INSERT INTO "Albums2" VALUES(13,'Big Red Car',17);CREATE TABLE Catalog("AlbumId" TEXT,"AlbumName" TEXT,"ArtistName" TEXT);INSERT INTO "Catalog" VALUES('1','Killers','Iron Maiden');INSERT INTO "Catalog" VALUES('2','Powerslave','Iron Maiden');INSERT INTO "Catalog" VALUES('12','Somewhere in Time','Iron Maiden');INSERT INTO "Catalog" VALUES('3','Surfing with the Alien','Joe Satriani');INSERT INTO "Catalog" VALUES('10','Flying in a Blue Dream','Joe Satriani');INSERT INTO "Catalog" VALUES('11','Black Swans and Wormhole Wizards','Joe Satriani');INSERT INTO "Catalog" VALUES('6','Out of the Loop','Mr Percival');INSERT INTO "Catalog" VALUES('7','Suck on This','Primus');INSERT INTO "Catalog" VALUES('8','Pork Soda','Primus');INSERT INTO "Catalog" VALUES('9','Sailing the Seas of Cheese','Primus');CREATE TABLE Genres(GenreId    INTEGER PRIMARY KEY,  Genre      TEXT NOT NULL);INSERT INTO "Genres" VALUES(1,'Rock');INSERT INTO "Genres" VALUES(2,'Country');INSERT INTO "Genres" VALUES(3,'Pop');INSERT INTO "Genres" VALUES(4,'Comedy');INSERT INTO "Genres" VALUES(5,'Jazz');INSERT INTO "Genres" VALUES(6,'Blues');INSERT INTO "Genres" VALUES(7,'Techno');COMMIT;

Copy the File

The above methods allow you to backup a database from within the SQLite3 command-line shell.

You can also backup a database simply by copying/pasting the physical file on the filesystem.

If you're not sure of the location of the physical file, you can use the .databases command to find the location:

sqlite> .databasesseq  name             file---  ---------------  ----------------------------------------------------------0    main             /Users/quackit/sqlite/music.db1    temp

So armed with the above knowledge, I can now navigate to the /Users/quackit/sqlite/ directory, copy the music.db file, and paste it to a safe location.