Database Backup File not a Database Error

[Kevin Chapman]

Hello,

I recently experienced a crash of weewx version 4.2.0.  I am still on the default Sqlite database.  I have been backing it up nightly since around November of last year.  Noob mistake is that I assumed the database file backups were good. 

My backup routine consisted of stopping weewx, taking a copy of the sdb file and gzipping it to a NAS archive.  Then starting the weewx system. 

Now when I try to copy the unpacked sdb and pick up where I left off I get a message that the file is not a database.  I have tried a couple of archives from Dec and Jan.  All seem to be no good.  I did notice that when I reinstalled weewx it is now version 4.3.0.  Could I be dealing with a system version mismatch in the file or is there something wrong with my backup process. 

[vince]

On Monday, January 18, 2021 at 6:47:09 PM UTC-8 kdch...@gmail.com wrote:

Now when I try to copy the unpacked sdb and pick up where I left off I get a message that the file is not a database.  I have tried a couple of archives from Dec and Jan.  All seem to be no good.  I did notice that when I reinstalled weewx it is now version 4.3.0.  Could I be dealing with a system version mismatch in the file or is there something wrong with my backup process.  

Very likely a backup process issue, although the steps you mentioned looked good to me.

We'd need to see some actual logs or terminal errors to know for certain what you're actually seeing.

I guess what I'd do is:

• grab a recent backup, copy it to a scratch directory
• uncompress it to a .sdb file
• run 'file' against the .sdb file
• If it shows up as a sqlite3 db, then you can validate it with the sqlite3 utility.

Good output looks like....

# file weewx.sdb

weewx.sdb: SQLite 3.x database, last written using SQLite version 3026000

# echo "pragma integrity_check" | sqlite3 weewx.sdb

ok

[paul.ba...@gmail.com]

Hello,

Your error message is the one I receive when I try to use a sqlite3 file with sqlite2 or vice et versa. 

Use the command  "file xxx.sdb"  to find the type of sqlite [2/3]  it is, and verify which sqlite you are using. 

The command "sqlite -version" return the version as expected. Be careful that "sqlite" (no 2 neither3) is sometimes associated with one or the other. There is not much difference between them, except that they store the data in different format that are 100% incompatible!. Much better to always call the version explicitly.

In the (very improbable) case that your sdb file is in the wrong format, you can easily change it to the right one by dumping the data using the corresponding version of sqlite ( .output MyData.sql  <cr>  .dump) to write the data in readable format, then reading back with the correct sqlite (Most probably sqlite3 <  MyData.sql).

Regards,     Paul

[Tom Keffer]

It's not impossible, but I'd be surprised if there are any WeeWX installations using Sqlite 2. It was phased out nearly 5 years before the first version of WeeWX!

--
You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/2357efa0-741b-41bd-9724-5080eb29a73cn%40googlegroups.com.

[Kevin Chapman]

Ok the file command is showing the backups to be large ASCII files.  The backup command script is using the sqlite3 .dump command.  The ascii files are the SQL commands to rebuild the file.  Here is the code.

echo 'stop the weewx daemon and wait 30 seconds'
sudo /usr/sbin/service weewx stop
retn_code=$?
if [ $retn_code -ne 0 ]; then
  exit 7
fi

sleep 30s

echo 'dump sqlite3 database'
echo '.dump' | sqlite3 $WEEWX_DB | gzip -c > $DUMP_FILE

echo 'restart the weewx daemon'
sudo /usr/sbin/service weewx start

I am not good with sqlite.  I have read a few different sites talking about how to read a dump file into a database.  So far I am not having luck recovering my database.  If anyone has a site they trust or have a quick command for recovering sqlite dump files it would be much appreciated.  So far when I run sqlite3 <DB>  and then .read <dump file>  It seems to run but in about 30 seconds ends at the prompt with no errors on screen and the database file is empty. 

Thank you all for your input and time. 

[p q]

I don't know anything, but I can google...

https://stackoverflow.com/questions/311691/importing-a-sqlite3-dump-back-into-the-database

--
You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/52eb0a6c-a7d1-4fcf-9f22-717f6bfe22fdn%40googlegroups.com.

--

Peter Quinn
(415)794-2264

[Mike Revitt]

I am in the process of industrialising my backups, but this script works from the command line and may give you an idea of how to backup your database without having to stop and start weewx. I will shortly have a post up on how to do this once I have it working properly

[Kevin Chapman]

Thank you to everyone that offered advice.  I was able to get my system restored.  My daily backups went sour around 12-29-2020.  I guess 2020's final blow.  I was able to use the sqlite3 .read command to restore the database and then pulled weather station data from wunderground for missing days. 

You received this message because you are subscribed to a topic in the Google Groups "weewx-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/weewx-user/7B1GyPjoDME/unsubscribe.
To unsubscribe from this group and all its topics, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAA1SM22FHfufJ-cSdUu9Msufu3U%3DpGi7UsDGeWrNOD07XUjLug%40mail.gmail.com.

--

-------------------------------------------------------------------------------------------------
Kevin Chapman
(713) 245-9368

Life is a food chain.  Be at the top or be on the menu!
-------------------------------------------------------------------------------------------------