David Costa <[EMAIL PROTECTED]>
03/06/2004 09:26 AM
To: [EMAIL PROTECTED]
cc:
Subject: [sqlite] Sqlite Secure backup
> I am in the process of writing a PEAR package to handle Sqlite backups
> (both local backups and remote backups) and I am looking for some
> suggestions.
> Is the right way to proceed the db dump ? I assume that copying the db
> will result in a corrupted file if the db is used at the time of
> backup.
Using .dump in the sqlite command-line utility is always going to be the
simplest, lowest risk solution. I recommend it, especially for small
databases. Using .dump, you don't have to worry about other readers and
writers. You don't have to worry about stray -journal files. All of this
is taken care of by the engine itself. You end up with a sequence of sql
commands that are suitable for rebuilding the sqlite database. This list
has a number of advantages over the basic file format:
1) Although sqlite database changes have in the past been forward
compatible, it is more likely that the .dump format of an ancient version
will be restorable on modern sqlite versions than that the old database
files is restorable.
2) .dump output is more likely to be backwards compatible, in case you
decide you have to downgrade your sqlite version
3) The dump file is human readible and is more likely to be able to be
applied to other database software if the need arises
4) The .dump output is more compressable than the original file format. In
gzip form it will usually take up less space than the gzip of the original
database file.
The only significant downside to the .dump format in my experience is that
it takes longer to restore from your media than a straight ungzip would
take. this is particularly true if you have large tables with indices. On
some (old) hardware I use a 400meg file takes only a few seconds to
restore from gzip on backup media, but the equivalent .dump format takes
in the order of half an hour to rebuild its tables and indices.
If you have large tables and restoration time is critial, the original
sqlite files are probably the best thing to backup. If you're more
interested in the security of your data, I recommend backing up the .dump
output instead.
Benjamin
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
