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

[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!

[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.

[DougieLawson]

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.

[scruss]

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.

[John_Spikowski]

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.