Backup Room Database

how to correctly backup room database

Finally, I found a solution. Database has 3 files that I have to save those files for backup:

val dbName = RoomDb.DATABASENAME
val documentFolder = Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOCUMENTS) 
when (item.itemId) {
    R.id.mnuBackup -> {
        val db = getDatabasePath(dbName).absolutePath
        val wal = getDatabasePath("$dbName-wal").absolutePath
        val shm = getDatabasePath("$dbName-shm").absolutePath
        File(db).copyTo(File(documentFolder, "$dbName"), true)
        File(wal).copyTo(File(documentFolder, "$dbName-wal"), true)
        File(shm).copyTo(File(documentFolder, "$dbName-shm"), true)
    }
    R.id.mnuRestore -> {
        val dbExternal = "$documentFolder/$dbName"
        val walExternal = "$documentFolder/$dbName-wal"
        val shmExternal = "$documentFolder/$dbName-shm"

        val db = getDatabasePath("$dbName").absolutePath
        val wal = getDatabasePath("$dbName-wal").absolutePath
        val shm = getDatabasePath("$dbName-shm").absolutePath
        File(dbExternal).copyTo(File(db), true)
        File(walExternal).copyTo(File(wal), true)
        File(shmExternal).copyTo(File(shm), true)
    }
    else -> {

    }
}

Backup Room database

This doesn't answer the original question

How can I properly re-open room db after I close it?

However, moving everything to the original database file is what you want to do, then you don't have to close the database in the first place. You can instead force a checkpoint using the wal_checkpoint pragma.

Query the following statement against the database. We use raw queries here as pragma is not yet supported by Room (it will trigger a UNKNOWN query type error). Have this query inside of your DAO:

@RawQuery
int checkpoint(SupportSQLiteQuery supportSQLiteQuery);

And then when you call the checkpoint method, use the query then:

myDAO.checkpoint(new SimpleSQLiteQuery("pragma wal_checkpoint(full)"));

This link may shed some light on what wal_checkpoint does.

how to restore backup file in room database easily?

This is with SQLite, but the process should be the same:

https://medium.com/@gavingt/refactoring-my-backup-and-restore-feature-to-comply-with-scoped-storage-e2b6c792c3b

Following this will also show you how to comply with scoped storage, because the way you're doing it now won't work at all on Android 11:

https://developer.android.com/preview/privacy/storage

You'll also likely want to disable write-ahead logging in onOpen(). This is what it looks like in SQLite:

    @Override
public void onOpen(SQLiteDatabase db) {
    super.onOpen(db);
    //disable write-ahead logging to make backup/restore work on all devices.
    db.disableWriteAheadLogging();
}

See this link for where to put the onOpen() callback in Room:

https://medium.com/@srinuraop/database-create-and-open-callbacks-in-room-7ca98c3286ab

Room best ways to create backups for offline application?

There is very little need to do anything complex, rather simply save the SQLiteDatabase file.

Basically close Room db then save the file.

e.g. the following is a very rudimentary example that saves to the downloads directory in a sub-directory called DBsaves :-

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    resetSequenceAction();
    if(Build.VERSION.SDK_INT >= Build.VERSION_CODES.M) {
        verifyStoragePermissions(this);
    }
}

@Override
protected void onStart() {
    super.onStart();
    mTestDB = Room.databaseBuilder(this,TestDatabase.class,TestDatabase.DBNAME).build();
    addSomeData();
    addSomeData();
    addSomeData();
    addSomeData();
    mTestDB.close();
    File dbfile = this.getDatabasePath(TestDatabase.DBNAME);
    File sdir = new File(Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS),"DBsaves");
    String sfpath = sdir.getPath() + File.separator + "DBsave" + String.valueOf(System.currentTimeMillis());
    if (!sdir.exists()) {
        sdir.mkdirs();
    }
    File savefile = new File(sfpath);
    try {
        savefile.createNewFile();
        int buffersize = 8 * 1024;
        byte[] buffer = new byte[buffersize];
        int bytes_read = buffersize;
        OutputStream savedb = new FileOutputStream(sfpath);
        InputStream indb = new FileInputStream(dbfile);
        while ((bytes_read = indb.read(buffer,0,buffersize)) > 0) {
            savedb.write(buffer,0,bytes_read);
        }
        savedb.flush();
        indb.close();
        savedb.close();

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

public void verifyStoragePermissions(Activity activity) {

    final int REQUEST_EXTERNAL_STORAGE = 1;
    String[] PERMISSIONS_STORAGE = {

            //Manifest.permission.READ_EXTERNAL_STORAGE,
            Manifest.permission.WRITE_EXTERNAL_STORAGE
    };

    int permission = ActivityCompat.checkSelfPermission(
            activity,
            Manifest.permission.WRITE_EXTERNAL_STORAGE);

    if(permission != PackageManager.PERMISSION_GRANTED) {
        ActivityCompat.requestPermissions(
                activity,
                PERMISSIONS_STORAGE,
                REQUEST_EXTERNAL_STORAGE
        );
    }
}

• Note the the onCreate and verifyStoragePermissions methods only included to get the permission to write to external storage (note user permissions also set in manifest).

  • The import thing is to do this outside of Room (likewise if you were to restore from a backup).

After running :-

And then copying the file to a PC and opening with SQLite Manager :-

This being entirely as expected and as shown highly portable i.e. you can drop it into any SQLite tool (SQLite version used by such a tool may be a restrictive factor)

Android backup/restore: how to backup an internal database?

A cleaner approach would be to create a custom BackupHelper:

public class DbBackupHelper extends FileBackupHelper {

    public DbBackupHelper(Context ctx, String dbName) {
        super(ctx, ctx.getDatabasePath(dbName).getAbsolutePath());
    }
}

and then add it to BackupAgentHelper:

public void onCreate() {
    addHelper(DATABASE, new DbBackupHelper(this, DB.FILE));
}

---

Related Topics