Backup sqlite db in WAL mode without FileStreams // by export/backup sql commands

Question

I have searched lots of answers here, but all use FileInputStream and FileOutputStream to backup a database.

A file copy fails in the moment where you use PRAGMA statements with journal_mode=WAL (write-ahead-logging), asynchronous mode, and so on.

How can I backup/export/snapshot a sqlite database that runs in WAL mode on android?

A file copy can't be the right way. There must be some backup/export command available in sqlite (well, it is available through the sqlite command shell).

So far, I didn't find a solution. Any help greatly appreciated.

I don't think the Android bindings support sqlite's backup API, which is unfortunate. (Most languages's sqlite bindings seem to only support a small fraction of the features available in the native C API). The newest versions support `VACUUM INTO` as another way of making a backup, but the Android version of the sqlite library is likely not new enough either. The best way on Android is probably to change the journal mode back to `DELETE`, do a `BEGIN IMMEDIATE` to get an exclusive lock on the database so that nothing can change it in mid-copy, and then copy the file. — Shawn, Mar 29 '19 at 10:43

MikeT · Accepted Answer · 2019-03-29T22:28:21.043

A file copy fails in the moment where you use PRAGMA statements with journal_mode=WAL (write-ahead-logging), asynchronous mode, and so on.

In short, you need to either back and restore all three 3 files or you need to ensure that the database has been fully checkpointed, backup the database file and delete the -wal and -shm files when restoring.

Here's a more comprehensive answer.

This is an example of a method that checkpoints the database if in WAL mode (this is used where the journal mode could be either as per the Android default) :-

private void checkpointIfWALEnabled(Context context) {
    final String TAG = "WALCHKPNT";
    Cursor csr;
    int wal_busy = -99, wal_log = -99, wal_checkpointed = -99;
    SQLiteDatabase db = SQLiteDatabase.openDatabase(context.getDatabasePath(DBConstants.DATABASE_NAME).getPath(),null,SQLiteDatabase.OPEN_READWRITE);
    csr = db.rawQuery("PRAGMA journal_mode",null);
    if (csr.moveToFirst()) {
        String mode = csr.getString(0);
        //Log.d(TAG, "Mode is " + mode);
        if (mode.toLowerCase().equals("wal")) {
            csr = db.rawQuery("PRAGMA wal_checkpoint",null);
            if (csr.moveToFirst()) {
                wal_busy = csr.getInt(0);
                wal_log = csr.getInt(1);
                wal_checkpointed = csr.getInt(2);
            }
            //Log.d(TAG,"Checkpoint pre checkpointing Busy = " + String.valueOf(wal_busy) + " LOG = " + String.valueOf(wal_log) + " CHECKPOINTED = " + String.valueOf(wal_checkpointed) );
            csr = db.rawQuery("PRAGMA wal_checkpoint(TRUNCATE)",null);
            csr.getCount();
            csr = db.rawQuery("PRAGMA wal_checkpoint",null);
            if (csr.moveToFirst()) {
                wal_busy = csr.getInt(0);
                wal_log = csr.getInt(1);
                wal_checkpointed = csr.getInt(2);
            }
            //Log.d(TAG,"Checkpoint post checkpointing Busy = " + String.valueOf(wal_busy) + " LOG = " + String.valueOf(wal_log) + " CHECKPOINTED = " + String.valueOf(wal_checkpointed) );
        }
    }
    csr.close();
    db.close();
}

Note in the above the database name is obtained (resolved) via the constant DBConstants.DATABSENAME, it would be a simple signature change to allow the database name to be passed.

Backup sqlite db in WAL mode without FileStreams // by export/backup sql commands

1 Answers1

Linked