How to backup sqlite database?

Question

What's the proper way to do it? Do I just copy the .sq3 file?

What if there are users on the site and file is being written while it's being copied?

What language and driver are you using to access the database? — CL., Sep 05 '14 at 07:28
There is currently a feature request to expose the sqlite backup API in php: https://bugs.php.net/bug.php?id=70950 — Brian Minton, Nov 30 '17 at 20:26
There is also a [VACUUM INTO](https://www.sqlite.org/lang_vacuum.html#:~:text=The%20VACUUM%20command%20with%20an,I%2FO%20may%20be%20reduced.) command as an alternative to the backup API. — Rüdiger Herrmann, Aug 16 '22 at 08:19

score 222 · Answer 1 · edited Oct 14 '17 at 09:12

222

The sqlite3 command line tool features the .backup dot command.

You can connect to your database with:

sqlite3 my_database.sq3

and run the backup dot command with:

.backup backup_file.sq3

Instead of the interactive connection to the database, you can also do the backup and close the connection afterwards with

sqlite3 my_database.sq3 ".backup 'backup_file.sq3'"

Either way the result is a copy named backup_file.sq3 of the database my_database.sq3.

It's different from regularly file copying, because it takes care of any users currently working on the database. There are proper locks set on the database, so the backup is done exclusively.

edited Oct 14 '17 at 09:12

Kissaki

8,810
5
40
42

answered Sep 05 '14 at 11:29

Googie

5,742
2
19
31

83

You can do it all in one line... `sqlite3 m_database.sq3 ".backup m_database.sq3.bak"` – Mark Setchell Sep 07 '14 at 18:02
@Googie: Can we use it for replication? or – mOna Oct 18 '16 at 07:48
4

@mOna: This is just a mechanism for making backups. Replication means propagating changes on the fly (kind of a distributed database), which this won't do for you. – Googie Oct 19 '16 at 08:22
Thanks for the reply :) – mOna Oct 19 '16 at 08:50
What does the `.backup` command actually do? Running `file m_database.sq3.bak` just says that it's an SQLite file. – RonJohn Dec 22 '18 at 23:57
2

@RonJohn It actually makes a copy of a file, but it also ansures, that write access to the database is restricted with proper locks, so it's an atomic operation, no intermediate modifications. – Googie Dec 24 '18 at 00:58
"file is being written while it's being copied" - In this case, I get `Error: database is locked` – Radio Controlled May 16 '19 at 07:13
1

@RadioControlled As I said earlier - this method locks database to avoid data corruption when there are concurrent users. If other user was first to lock the database (like in your case), then of course backup request will fail, cause it cannot secure a lock. – Googie May 17 '19 at 09:16
So during the backup write operations are blocked? – jjxtra Jul 31 '19 at 16:07
I'm not 100% sure, but my assumption is, that only some parts of database are locked at a moment, so only write operations working on these parts will be blocked. Then they will be allowed, once the backup process moves onto other parts of database. – Googie Aug 01 '19 at 14:44
5

You should also specify a timeout to minimize `database is locked` errors... `sqlite3 source.db ".timeout 1000" ".backup backup.db"` – Melvyn Jun 07 '20 at 19:03
1

@Melvyn It's correct that the `.timeout` command helps (internally, [it calls](https://github.com/sqlite/sqlite/blob/bc85a51595d8afb0f82b3723050f0a6faa6082bc/src/shell.c.in#L10170-L10172) [sqlite3_busy_timeout](https://sqlite.org/c3ref/busy_timeout.html)), however for larger databases the timeout should be way larger than 1000, as that is the time in miliseconds allowed to be accumulated before the command will error out with "database is locked" errors again. – pcworld Jun 08 '21 at 00:51
Is it possible to execute .backup with an SQLiteOpenHelper object in Android? I don't want to use the cumbersome shell command if possible. – JohnyTex Feb 22 '22 at 11:31
There is no `.backup` command in the SQL of SQLite, as it is a feature of the command line tool. Still, from version 3.27.0 you can use `VACUUM INTO filename;`, as documented here https://sqlite.org/lang_vacuum.html#vacuuminto It will not modify your source database at all. – Googie Feb 23 '22 at 12:28
Just overwitten the 700GB database file :( confused where the src and dst are – Sion C May 30 '22 at 09:04

score 25 · Answer 2 · edited Feb 26 '23 at 08:54

25

.backup is the best way:

sqlite3 my_database ".backup my_database.back"

you can also try .dump command, it gives you the ability to dump the entire database or tables into a text file. If TABLE specified, only dump tables matching LIKE pattern TABLE.

sqlite3 my_database .dump > my_database.back

A good way to make an archival copy using dump and store, Reconstruct the database at a later time.

sqlite3 my_database .dump | gzip -c > my_database.dump.gz
zcat my_database.dump.gz | sqlite3 my_database

Also check this question Do the SQLite3 .backup and .dump commands lock the database?

edited Feb 26 '23 at 08:54

Adam Shand

193
9

answered Apr 13 '17 at 17:37

Lava Sangeetham

2,943
4
38
54

8

On SQLite 3.8.2, `.backup` doesn't work as shown above ("missing FILENAME argument on .backup") – Francesc Rosas Oct 18 '17 at 09:11
5

This is the best answer. If you are using .backup on a working database used by many it may not work because at some point the database is locked. So if you are using this in a CRON it won't work and won't tell you there is an error... better use .dump (allways works) or the API given by SQLite. – Memristor Apr 07 '18 at 11:51
@Memristor But doesn't .dump lock the DB for others? FWIW, I would prefer a failed backup (with mail to the admin) to a disrupted service. – Torsten Bronger Sep 09 '18 at 05:34
1

Please fix .backup syntax in your answer. It doesn't need '>' operator – Nashev Dec 06 '18 at 14:59
@Memristor but locks exist for a reason, i.e. to not dump a corrupted database in the middle of a write. Problem is better solved by first issuing a .timeout [ms]; command before .backup; That way .backup will wait for lock to be released up to [ms] milliseconds before failing. – JohnyTex Feb 23 '22 at 12:40
@JohnyTex for sure, I did say that because I used to work on a project that wrongly chose SQLite... the thing is that while the database was being accessed some new programmer copy-pasted the database because "hey! it's just a file" and that's when the party began :) – Memristor Mar 06 '22 at 00:03

score 5 · Answer 3 · answered Jul 08 '22 at 00:28

For streaming replication of SQLite, check out Litestream.

Compared to using the sqlite3-backup command, this is automatic, and incremental.

If you need to restore from backup, the data will be a lot more up to date than if you did a regular backup every hour for example.

score 3 · Answer 4 · answered Oct 02 '21 at 12:12

3

Short and simple answer would be

sqlite3 m_database.sq3 ".backup m_database.sq3.bak"

answered Oct 02 '21 at 12:12

Saurabh Dhage

1,478
5
17
31

score 0 · Answer 5 · answered May 05 '23 at 23:12

0

People who want to do the backup from within their app should check out the backup API at https://www.sqlite.org/backup.html

answered May 05 '23 at 23:12

sjb-sjb

1,112
6
14

note: some example usage could improve the long-term value of this post. – starball May 06 '23 at 00:37

ccpizza · Answer 6 · 2023-08-20T19:49:42.857

In the simplest form you can run from project root a command of the following form (default format is JSON, other options include XML, YAML, see the links below):

./manage.py dumpdata --output mydata.json

You might want instead to dump only specific django apps in your project, e.g. assuming you created your app with manage.py startapp dinsdaleapp:

./manage.py dumpdata dinsdaleapp --output dinsdaleapp-data.json

It's also possible to only dump specific models:

./manage.py dumpdata dinsdaleapp.institution -o institutions-data.json

To restore the previously created dump

./manage.py loaddata mydata.json

To dump flatpages:

./manage.py dumpdata flatpages -o flatpages.json

In case you prefer a timestamped backup:

./manage.py dumpdata flatpages -o dumps/flatpages-$(date +%Y-%m-%d_%H_%M_%S).json

For details and options see:

./manage.py dumpdata --help
./manage.py loaddata --help

Also see:

How to backup sqlite database?

6 Answers6

Linked