1 Answer
1

If you use something like FTP or SCP to get it directly while the server is online, it is quite possible that you will retrieve a corrupt file. Think about it: over the Internet, transferring such a file would need at least a few seconds - a few seconds during which your site is still online and transactions are performed on the server DB file. So the first bytes of the file would reflect e.g. transaction 1003, while the last bytes would reflect transaction 1015. Essentially the DB file changes while you are still downloading it.

And we still have not got into DB journal files and partial transactions.

What you need is an atomic copy of the DB file. Even cp on the server may not be fast enough. You need a way to lock the DB while copying the file.

There are three ways to do this:

Use the sqlite3 shell utilty on the server and .dump the DB. You can then compress the SQL dump, download it and rebuild a local DB. A bit cumbersome but almost guaranteed to work.

Use the SQLite3 backup API. The sqlite3 shell utility has a .backup command that does pretty much the same. Then you can just download the new DB file. Unfortunately older SQLite versions may not support this.

Lock the DB file using the sqlite3 shell utility, use cp to copy it, unlock. The old, slightly dangerous way, which is why I'll not go into more details.

Stop your server so that no transactions are performed, copy the file, restart. I don't consider this an actual solution, hence its not being included in the final count.

BTW, this page contains a few common ways to corrupt an SQLite3 DB - you might want to have a look...

Yes via FTP, but the file is very small like 64KB and it isn't used that much, my doubt is that the file may still hanging and not been closed.
–
BrainCrashJul 13 '11 at 12:16

@BrainCrash: It's quite possible that there is an active journal file that you are not downloading. In general the only way to reliably copy DB files - in any DBMS, not just SQLite - is through the DBMS itself.
–
thkalaJul 13 '11 at 12:20

Somehow I was able to download a working copy of the file. My problem is that I have one of those cheap host so can't run shell commands on server. Thanks.
–
BrainCrashJul 13 '11 at 12:52