Restore from Room Sqlite database from sd card not working

Question

I looked at others answers like Backup and restore SQLite database to sdcard and Restoring SQLite DB file etc. but i still dont see the restoring of database when i uninstall and reinstall app and restore backup. Here is the code I have currently.

        public class BackupAndRestore {
        
            public static void importDB(Context context) {
                try {
                    File sd = Environment.getExternalStorageDirectory();
        
                    if (sd.canRead()) {
                        File currentDB = context.getDatabasePath(AppDatabase.DATABASE_NAME);
                        File backupDB = new File(sd, AppDatabase.DATABASE_NAME);
        
                        if (currentDB.exists()) {
                            FileChannel src = new FileInputStream(backupDB).getChannel();
                            FileChannel dst = new FileOutputStream(currentDB).getChannel();
                            dst.transferFrom(src, 0, src.size());
                            src.close();
                            dst.close();
                            Toast.makeText(context, "Database Restored successfully", Toast.LENGTH_SHORT).show();
                        }
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        
            public static void exportDB(Context context) {
                try {
                    File sd = Environment.getExternalStorageDirectory();
                    File data = Environment.getDataDirectory();
        
        
                    if (sd.canWrite()) {
        
                        File currentDB = context.getDatabasePath(AppDatabase.DATABASE_NAME);
                        File backupDB = new File(sd, AppDatabase.DATABASE_NAME);
        
                        if (currentDB.exists()) {
                            FileChannel src = new FileInputStream(currentDB).getChannel();
                            FileChannel dst = new FileOutputStream(backupDB).getChannel();
                            dst.transferFrom(src, 0, src.size());
                            src.close();
                            dst.close();
                            Toast.makeText(context, "Backup is successful to SD card", Toast.LENGTH_SHORT).show();
                        }
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
        
            }
        
        }

So I install app and I add content to database. Then I also grant permission to write to external storage before calling this export method above. it shows toast message "Backup is successful... " and I can see the file created in the external storage. Then I uninstall and reinstall and I request permission again to external storage. Then call the method import above. The toast message again is seen "Database Restored.." but I don't see the database content that existed before. I tested on android 7 device and android 10 device. I will appreciate help. Thanks.

Answer 1

After testing and looking around much and thanks to @MikeT i was able to use this class below for backup and restore.

public class BackupAndRestore {

        public static void importDB(Context context) {
            try {
                File sd = Environment.getExternalStorageDirectory();
                // by closing the database some other database files ending with -shm and -wal are deleted so that there is one database file and it has all content
                AppDatabase.getDatabaseInstance(context).close();

                if (sd.canRead()) {
                    File currentDB = context.getDatabasePath(AppDatabase.DATABASE_NAME);
                    File backupDB = new File(sd, AppDatabase.DATABASE_NAME);

                    transfer(context, backupDB, currentDB);
                    Toast.makeText(context, "Database Restored successfully", Toast.LENGTH_SHORT).show();

                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }


        public static void exportDB(Context context) {
            try {
                File sd = Environment.getExternalStorageDirectory();
                // by closing the database some other database files ending with -shm and -wal are deleted so that there is one database file and it has all content
                AppDatabase.getDatabaseInstance(context).close();

                if (sd.canWrite()) {
                    File currentDB = context.getDatabasePath(AppDatabase.DATABASE_NAME);
                    File backupDB = new File(sd, AppDatabase.DATABASE_NAME);
                    transfer(context, currentDB, backupDB);

                    Toast.makeText(context, "Backup is successful to SD card", Toast.LENGTH_SHORT).show();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        private static void transfer(Context context, File sourceFile, File destinationFile) throws IOException {
            if (sourceFile.exists()) {
                FileChannel src = new FileInputStream(sourceFile).getChannel();
                FileChannel dst = new FileOutputStream(destinationFile).getChannel();
                dst.transferFrom(src, 0, src.size());
                src.close();
                dst.close();
            }
        }

    }

In my main activity I call method like below

    ...

    if (item.getItemId() == R.id.backup) {
        BackupAndRestore.exportDB(getBaseContext());
        restartApplication();
    } else if (item.getItemId() == R.id.restore) {
        BackupAndRestore.importDB(getBaseContext());
        restartApplication();
    }

    ...

So the restartApplication method is called after backup because I noticed the room database was not working properly after backup unless i restart the application and also after after restoring I could not see the restored data unless the application was restarted. The restart method is below

private void restartApplication() {
    finish();
    startActivity(getIntent());
    System.exit(0);
}



 

Answer 2

You issue could well be that the database is using WAL (Write-Ahead logging) as opposed to journal mode.

With WAL changes are written to the WAL file (database file name suffixed with -wal and also another file -shm). If the database hasn't been committed and you only backup/restore the database file. You will lose data.

• When fully committed, the -wal file will be 0 bytes or not exist, in which case it is not needed.

From Android 9 the default was changed from journal mode to WAL.

• https://source.android.com/devices/tech/perf/compatibility-wal

Assuming that this is your issue you have some options:-

1. Use Journal mode (e.g. use the SQLiteDatabase disableWriteAheadLogging method)
2. Backup/Restore all 3 files (if they exist)
3. Fully Commit the database and then backup (closing the database should fully commit) and delete/rename the -wal file and -shm file before restoring.

Option 3 would be the recommended way as you then gain the advantages of WAL.

Here's an example of fully checkpointing (a little over the top but it works):-

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(); // Should checkpoint the database anyway.
}

When restoring the following is used to delete the -wal and -shm files :-

                    // Added for Android 9+ to delete shm and wal file if they exist
                    File dbshm = new File(dbfile.getPath() + "-shm");
                    File dbwal = new File(dbfile.getPath() + "-wal");
                    if (dbshm.exists()) {
                        dbshm.delete();
                    }
                    if (dbwal.exists()) {
                        dbwal.delete();
                    }