Page 1 of 1

how to create backup of an SDB (sqlite3 database) file?

Posted: Wed Sep 25, 2019 7:14 pm
by ericg75
I have a SDB sqlite3 database file that collects weather-station data every half hour. I would like to know HOW to CREATE A BACKUP of this file, every day, preferably on another box, so that I will not loose all of my historical weather data (again). I do not have any experience with coding, but I can and do use sudo to other system related functions. I do enjoy my RASPBERRY PI's (I have two). Many thanks!

Re: how to create backup of an SDB (sqlite3 database) file?

Posted: Wed Sep 25, 2019 8:32 pm
by scruss
You're going to like this: SQLite databases are completely contained in that one file. You can copy them just like regular files. This is much less work than moving other databases around.

Re: how to create backup of an SDB (sqlite3 database) file?

Posted: Wed Sep 25, 2019 11:02 pm
by DougieLawson
scruss wrote:
Wed Sep 25, 2019 8:32 pm
You're going to like this: SQLite databases are completely contained in that one file. You can copy them just like regular files. This is much less work than moving other databases around.
You do need to ensure there's no SQLite3 process running that has your database open.

With the sqlite3 command line program you can dump a database out as a bunch of SQL insert statements.

Dump:

Code: Select all

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

Code: Select all

cd /home/sqlite
mv sample.db sample.db.old
sqlite3 sample.db < sample.bak
The restore could be done on any other machine with sqlite3 installed.

Re: how to create backup of an SDB (sqlite3 database) file?

Posted: Thu Sep 26, 2019 1:31 am
by scruss
DougieLawson wrote:
Wed Sep 25, 2019 11:02 pm
You do need to ensure there's no SQLite3 process running that has your database open.
True enough. So a better way would be:

Code: Select all

sqlite3 source.sdb ".backup 'destination.sdb'"
This has the same effect as copying the file, but ensures that the database is locked while copying so other processes can't corrupt it.

Re: how to create backup of an SDB (sqlite3 database) file?

Posted: Thu Sep 26, 2019 4:33 am
by John_Spikowski
DougieLawson wrote:
Wed Sep 25, 2019 11:02 pm
scruss wrote:
Wed Sep 25, 2019 8:32 pm
You're going to like this: SQLite databases are completely contained in that one file. You can copy them just like regular files. This is much less work than moving other databases around.
You do need to ensure there's no SQLite3 process running that has your database open.

With the sqlite3 command line program you can dump a database out as a bunch of SQL insert statements.

Dump:

Code: Select all

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

Code: Select all

cd /home/sqlite
mv sample.db sample.db.old
sqlite3 sample.db < sample.bak
The restore could be done on any other machine with sqlite3 installed.
Thanks!

I have been looking for a SQLite export / import that works like phpmyadmin.