ericg75
Posts: 5
Joined: Tue Nov 22, 2016 5:36 pm

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

Wed Sep 25, 2019 7:14 pm

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!

User avatar
scruss
Posts: 3067
Joined: Sat Jun 09, 2012 12:25 pm
Location: Toronto, ON
Contact: Website

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

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.
‘Remember the Golden Rule of Selling: “Do not resort to violence.”’ — McGlashan.
Pronouns: he/him

User avatar
DougieLawson
Posts: 38458
Joined: Sun Jun 16, 2013 11:19 pm
Location: A small cave in deepest darkest Basingstoke, UK
Contact: Website Twitter

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

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.
Note: Any requirement to use a crystal ball or mind reading will result in me ignoring your question.

I'll do your homework for you for a suitable fee.

Any DMs sent on Twitter will be answered next month.
All non-medical doctors are on my foes list.

User avatar
scruss
Posts: 3067
Joined: Sat Jun 09, 2012 12:25 pm
Location: Toronto, ON
Contact: Website

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

Thu Sep 26, 2019 1:31 am

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.
‘Remember the Golden Rule of Selling: “Do not resort to violence.”’ — McGlashan.
Pronouns: he/him

User avatar
John_Spikowski
Posts: 1614
Joined: Wed Apr 03, 2019 5:53 pm
Location: Anacortes, WA USA
Contact: Website Twitter

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

Thu Sep 26, 2019 4:33 am

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.

Return to “General programming discussion”