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]