On 2016/04/14 10:18 PM, Cecil Westerhof wrote: > 2016-04-14 22:07 GMT+02:00 Clemens Ladisch <clemens at ladisch.de>: > >> Cecil Westerhof wrote: >>> what is the best way to make a backup? >> With the backup API: <http://www.sqlite.org/backup.html>. >> (Also available as .backup in the shell.) >> > ?I should be more precise in my communication. :'-( > > I prefer to make my backup with .dump. Then I have just a text based > backup. This works fine for the regular data, but not for photos: they can > be 13 MB big. (And I am not even using raw. When I am going to do that, > they will become much bigger.) So I would like to have my photos split out > when making the dump. > > But maybe it will be better to use external blobs. > > Something to think about. >
The BLOBS and data inside SQLite is quite safe, and the backup API does a magnificent job, why would you prefer text dumps to backed up DB's? Either way, you do not need to use the .dump to make data dumps, you can quite easily push data out using a query in which you can SELECT the fields you'd like, sans those you do not wish to dump. The caveat here is that you would not be able to reconstruct the DB perfectly from the dump, but then if you don't dump the BLOBs, that was never going to happen anyway. I know from the old days with other DB engines, text dumps made one feel safe because nobody really knew what magic went on inside the files the DB engine used. With SQLite it's quite different though, it's very open, there's a myriad things that can read SQLite DBs, including the always free always available sqlite3.exe from the web site, and even some people here have data recovery systems for SQLite if you do succeed in hurting a DB file. Apart from maliciousness, there's really nothing that can hurt an SQLite file that cannot also hurt a text file - I don't think there is any legitimate reason to prefer text dumps over SQLite files anymore (especially for backups) - but that's up to you.
