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: 2613
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.

User avatar
DougieLawson
Posts: 36511
Joined: Sun Jun 16, 2013 11:19 pm
Location: 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: Having anything humorous in your signature is completely banned on this forum. Wear a tin-foil hat and you'll get a ban.

Any DMs sent on Twitter will be answered next month.

This is a doctor free zone.

User avatar
scruss
Posts: 2613
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.

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”