SQLite | SQLite Commands | Back Up and Restore a Database (.backup/.restore)
You can back up a SQLite database by copying its file or by using dump and import. This article explains another approach using the .backup and .restore commands.
Backing Up with .backup
Use .backup to create a database backup. The syntax is as follows.
.backup ?DB? FILE
The first argument identifies the database to back up. It defaults to main, the name normally assigned to the primary connected database, so it can usually be omitted.
The second argument is the backup file name. Any name and extension may be used. If the file does not exist, SQLite creates it.
–
Connect to the database to back up. This example database contains two tables.
.tables
sqlite> .tables
color user
sqlite>
Back up the database to mydb.back in the current directory. SQLite creates the file because it does not yet exist.
.backup ./mydb.back
sqlite> .backup ./mydb.back
sqlite>
After the backup completes, mydb.back appears in the current directory. It is a binary database file, not a text file.
$ ls -al
total 5080
drwxr-xr-x@ 8 kimkc staff 272 11 4 23:31 .
drwxr-xr-x 9 kimkc staff 306 10 17 23:49 ..
drwxr-xr-x 27 kimkc staff 918 11 4 23:31 bak
-rw-r--r-- 1 kimkc staff 16384 11 4 23:30 mydb.back
-rw-r--r-- 1 kimkc staff 16384 11 4 22:38 mydb.sqlite3
-rwxr-xr-x@ 1 kimkc staff 691768 10 11 18:31 sqldiff
-rwxr-xr-x@ 1 kimkc staff 1152260 10 11 18:32 sqlite3
-rwxr-xr-x@ 1 kimkc staff 719796 10 11 18:31 sqlite3_analyzer
$
The database backup is complete.
Restoring with .restore
Use .restore to restore a database from a backup. The syntax is as follows.
.restore ?DB? FILE
The first argument identifies the database to restore and defaults to main. The second argument is the backup file.
Connect to the target database before restoring. Be careful: restoring removes its current tables and data before replacing them with the backup contents.
–
Create a new database named newdb.sqlite3 as the restore target.
sqlite3 newdb.sqlite3
$ sqlite3 newdb.sqlite3
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite>
Run .restore with the previously created mydb.back file.
.restore ./mydb.back
sqlite> .restore ./mydb.back
sqlite>
The restore replaces the database contents with the data stored in the backup.
Inspect the schema to confirm that the two tables were restored.
.tables
sqlite> .tables
color user
sqlite>
Query the tables to confirm that their data matches the backup.
select * from user;
sqlite> select * from user;
devkuma|28|Seoul
kimkc|22|Busan
araikuma|32|Seoul
happykuma|23|Seoul
mykuma|23|Daejeon
sqlite>
select * from color;
sqlite> select * from color;
1|Red
2|Blue
3|White
sqlite>
The backed-up database has now been restored into the new database.