Backup SQLite Database

The SQLite database is just a single file located in your filesystem (by default it is /sites/default/files/.ht.sqlite) so in order to backup the db all you have to do is copy that single file. To find the location of the SQLite file in case you changed the default path, you can look at settings.php.

Maybe this should be a group discussion.

Ok, i know how to backup and restore = copy and paste. But what I have want to modify it through phpmyadmin? Because host provider doesn't detect it. Is there another appart of: http://drupal.org/project/dbtng_migrator converting to mysql, modifying, converting to sqlite with the risk it entails of losing data, bad conversion, etc?

I would like to suggest reconsidering SQLite backup support.

The SQLite database is just a single file located in your filesystem (by default it is /sites/default/files/.ht.sqlite) so in order to backup the db all you have to do is copy that single file.

It's not that simple. First of all, it's not single file all the times, it creates -journal or -wal files during transactions. And even if you try to copy journal/WAL files also, it's not quite safe either, because.. well... database is in changing state.

Please see section 1.2 in How To Corrupt An SQLite Database File:

1.2 Backup or restore while a transaction is active

Systems that run automatic backups in the background might try to make a backup copy of an SQLite database file while it is in the middle of a transaction. The backup copy then might contain some old and some new content, and thus be corrupt.

The best approach to make reliable backup copies of an SQLite database is to make use of the backup API that is part of the SQLite library. Failing that, it is safe to make a copy of an SQLite database file as long as there are no transactions in progress by any process. If the previous transaction failed, then it is important that any rollback journal (the *-journal file) or write-ahead log (the *-wal file) be copied together with the database file itself.

So, if you have very simple shared PHP hosting, without access to shell, and without possibility to stop web server to make sure no new transactions will be started, it is dangerous to rely on simple file copy.

As a side note, if someone wants to make atomic SQLite dump, can try to use this snippet:

shell_exec("sqlite3 sites/default/files/.ht.sqlite.db .dump | gzip > sites/default/files/.ht.sqlite.sql.gz");

But most probably shell_exec() / exec() will disabled in shared hosting...

Another alternative would be opening (NORMAL/EXCLUSIVE ?) transaction in PDO and copying a file during transaction?