On Thu, 8 Jun 2006, [EMAIL PROTECTED] wrote:
I also occasionally back up the database using subversion ("svn
commit"), while the app that uses it is still running. My belief is
that subversion only reads a file to commit it, and doesn't write to it,
but it's possible that is wrong.
Subversion only reads the file, but it doesn't do the atomically! This
backup strategy has a race condition that could result in the backup of a
corrupted file. DON'T use this method.
Instead, take a stable snapshot using the .dump command from the SQLite
shell, and save the resulting text file as your backup:
$ sqlite3 dbfile .dump | gzip > dbfile.backup.gz
To restore the backup, feed the contents of the backup into the SQLite
shell. The backup is simply a sequence of SQL commands to restore the
data, and compresses pretty well:
$ zcat dbfile.backup.gz | sqlite3 new.dbfile
Thanks to the backup, I only lost about a day's worth of data, and much
of that was recoverable from other sources. It sounds like corruption
is fairly rare, so for now I'll just bolster my backup & recovery
procedures and stick with it.
If you're finding corruption a problem, you might want to check the
reliability of the machine. As Jay suggested, memtest86 is a good bet. You
might also want to test on a backup machine to see of the problem can be
replicated. If the problem is common enough, perhaps take an hourly backup
using the above method, keeping perhaps the last 24 hours worth of
backups.
Thanks,
- Joe
