SQLite - Backup a Database to File
How to backup a SQLite database to a file.
There are several ways of backing up a SQLite database to file.
- Use the
.backupcommand. - Use the
.clonecommand. - Use the
.dumpcommand. - 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:
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> .databases seq name file --- --------------- ---------------------------------------------------------- 0 main /Users/QHMit/sqlite/music.db 1 temp 2 MyBackup /Users/QHMit/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:
Running that looks like this:
sqlite> .clone myclone.db Artists... done Albums... done Albums2... done Catalog... done Genres... done
You can attach the cloned database just like the other one:
sqlite> ATTACH DATABASE 'myclone.db' AS MyClone; sqlite> .databases seq name file --- --------------- ---------------------------------------------------------- 0 main /Users/QHMit/sqlite/music.db 1 temp 2 MyBackup /Users/QHMit/sqlite/mybackup.db 3 MyClone /Users/QHMit/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.
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> .databases seq name file --- --------------- ---------------------------------------------------------- 0 main /Users/QHMit/sqlite/music.db 1 temp
So armed with the above knowledge, I can now navigate to the /Users/QHMit/sqlite/ directory, copy the music.db file, and paste it to a safe location.