Backup Room database

Question

I'm trying to backup a room database programmatically.

For that, I'm simply copying the .sqlite file that contains the whole database

But, before copying, due to the fact that room has write ahead logging enabled, we must close the database so that -shm file and -wal file merge into a single .sqlite file. As pointed out here

I run .close() on RoomDatabase object:

Everything works fine with the backup, BUT, later on, when I try to execute an INSERT query, I get this error:

android.database.sqlite.SQLiteException: no such table: room_table_modification_log (code 1)

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

PS: .isOpen() on RoomDatabase object returns true before INSERT

Room version: 1.1.1-rc1

Answer 1

This doesn't answer the original question

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

However, if 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.

Answer 2

To more specifically answer your question, this is how I backup the room database in one of my Apps.

1-Check for permission to read from / write to the external storage. 2-Close your RoomDatabase. In my case AppDatabase refers to a singleton that contains logic for building the room database initially. AppDatabase.getInstance(this@MainActivity) gets the current instance of the singleton, and its current database class, that extends from RoomDatabase. 3-Then essentially call dbInstance.close().

private fun createBackup() {
        val db = AppDatabase.getInstance(this@MainActivity)
        db.close()
        val dbFile: File = getDatabasePath(DATABASE_NAME)
        val sDir = File(Environment.getExternalStorageDirectory(), "Backup")
        val fileName = "Backup (${getDateTimeFromMillis(System.currentTimeMillis(), "dd-MM-yyyy-hh:mm")})"
        val sfPath = sDir.path + File.separator + fileName
        if (!sDir.exists()) {
            sDir.mkdirs()
        }
        val saveFile = File(sfPath)
        if (saveFile.exists()) {
            Log.d("LOGGER ", "File exists. Deleting it and then creating new file.")
            saveFile.delete()
        }
        try {
            if (saveFile.createNewFile()) {
                val bufferSize = 8 * 1024
                val buffer = ByteArray(bufferSize)
                var bytesRead: Int
                val saveDb: OutputStream = FileOutputStream(sfPath)
                val indDb: InputStream = FileInputStream(dbFile)
                do {
                    bytesRead = indDb.read(buffer, 0, bufferSize)
                    if (bytesRead < 0)
                        break
                    saveDb.write(buffer, 0, bytesRead)
                } while (true)
                saveDb.flush()
                indDb.close()
                saveDb.close()
            }
        } catch (e: Exception) {
            e.printStackTrace()

        }
    }

You have to include save file in

try {
//backup process
      }
        } catch (e: Exception) {
            e.printStackTrace()

        }

in order of any error occur and to avoid app crashes.

And to get date from currentTimeMillis use this function

fun getDateTimeFromMillis(millis: Long, pattern: String): String {
    val simpleDateFormat = SimpleDateFormat(pattern, Locale.getDefault()).format(Date())
    return simpleDateFormat.format(millis)
}

The Code For Resoting Db passing file object to Uri.fromFile

try {
                                    val fileUri: Uri = Uri.fromFile(file)
                                    val inputStream = contentResolver.openInputStream(fileUri)
                                    println("restoring ")
                                    restoreDatabase(inputStream);
                                    inputStream?.close()
                                } catch (e: IOException) {
                                    println( e.message)
                                    e.printStackTrace()
                                }

**Or returning result with start activity for result **

protected void onActivityResult(int requestCode, int resultCode, Intent data) {
        super.onActivityResult(requestCode, resultCode, data);
        if (requestCode == 12 && resultCode == RESULT_OK && data != null) {
            Uri fileUri = data.getData();
            try {
                assert fileUri != null;
                InputStream inputStream = getContentResolver().openInputStream(fileUri);       
                    restoreDatabase(inputStream);
                    inputStream.close();
                
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

restoreDatabase function

private fun restoreDatabase(inputStreamNewDB: InputStream?) {
        val db = AppDatabase.getInstance(this@MainActivity)
        db.close()
        val oldDB = getDatabasePath(DATABASE_NAME)
        if (inputStreamNewDB != null) {
            try {
                copyFile(inputStreamNewDB as FileInputStream?, FileOutputStream(oldDB))
                println("restore success")
            } catch (e: IOException) {
                Log.d("BindingContextFactory ", "ex for is of restore: $e")
                e.printStackTrace()
            }
        } else {
            Log.d("BindingContextFactory ", "Restore - file does not exists")
        }
    }

now you need to copy file from backup into real db file use copyFile

@Throws(IOException::class)
    fun copyFile(fromFile: FileInputStream?, toFile: FileOutputStream) {
        var fromChannel: FileChannel? = null
        var toChannel: FileChannel? = null
        try {
            fromChannel = fromFile?.channel
            toChannel = toFile.channel
            fromChannel?.transferTo(0, fromChannel.size(), toChannel)
        } finally {
            try {
                fromChannel?.close()
            } finally {
                toChannel?.close()
            }
        }
    }

Answer 3

First thing that needs to be done is to create the database with appropriate journal mode.

Room.databaseBuilder(context, AppDatabase::class.java, name)
    .setJournalMode(RoomDatabase.JournalMode.TRUNCATE)
    .build()

After that the following checkpoint query needs to be executed to ensure all of the pending transactions are applied.

For this a following method needs to be added to the database Dao interface

interface UserDao {
    @RawQuery
    fun checkpoint(supportSQLiteQuery: SupportSQLiteQuery?): Single<Int>
}

Then the method needs to be called with the following SQL query

userDao.checkpoint((SimpleSQLiteQuery("pragma wal_checkpoint(full)")))

Once the checkpoint method has succeeded the database backup file can finally be saved.

Finally the database backup file can be retrieved using the following code

File(database.openHelper.writableDatabase.path)

The file then needs to be copied into the backup file location.

To restore the file the onky thing that needs to be done is to overwrite the database file (this can be retrieved using above snippet) with the backup file.

You can read about this in more detail on my blog

https://androidexplained.github.io/android/room/2020/10/03/room-backup-restore.html

Answer 4

It already has been answered above. No need to close/re-open database.

I am using MVVM pattern in my android app to back up db file to upload it to google drive. Just want to summarise the solution that worked for me:

Mention below code in your DAO file:

@RawQuery
    int checkpoint(SupportSQLiteQuery supportSQLiteQuery);

Mention below code in your repository file:

    /* Android database has three files under /data/data/com.package.app/databases/
    ** test.db, test.db-shm, test.db-wal - those extra files have recent commits.
    ** To merge data from other shm and wal files to db, run following method - useful before taking backup.
    */
    void checkPoint() {
        ItemRoomDatabase.databaseWriteExecutor.execute(() -> {
           itemDao.checkpoint(new SimpleSQLiteQuery("pragma wal_checkpoint(full)"));
        });
    }

mention following in your ViewModel:

public void checkPoint() {
      itemRepository.checkPoint();
}

Now you can call this method just before doing backup from your Activity file

ItemViewModel itemViewModel = new ViewModelProvider(this).get(ItemViewModel.class);
itemViewModel.checkPoint();

Answer 5

First, the database must be closed to apply changes from the "dbName.db-wal" file.

Then you can copy the database with all tables and last data changes

 AppDatabase appDatabase = AppDatabase.getAppDatabase(getApplicationContext());
 appDatabase.close();

Answer 6

As an alternative, you can always create your Room database while forcing it not to use write ahead logging:

Room.databaseBuilder(context, db.class, dbName)
    .setJournalMode(JournalMode.TRUNCATE)
    .build();

Answer 7

To more specifically answer your question, this is how I backup the room database in one of my Apps.

1. Check for permission to read from / write to the external storage. You can ignore this step if you write to your App files directory.
2. Close your RoomDatabase. In my case AppDatabase refers to a singleton that contains logic for building the room database initially. AppDatabase.getInstance(this).getDatabase() gets the current instance of the singleton, and its current database class, that extends from RoomDatabase. This essentially calls RoomDatabase.close().
3. Define the source and destination files, depending on backing up or restoring. I include shm and wal files, even though they are temporary files.
4. Copy the files with your method of choice. FileUtils in this case, refers to commons-io.

The code

if(id == R.id.action_save_db) {
    int permission = ActivityCompat.checkSelfPermission(this, Manifest.permission.WRITE_EXTERNAL_STORAGE);
    if(permission == PackageManager.PERMISSION_GRANTED) {
        AppDatabase.getInstance(this).getDatabase().close();

        File db = getDatabasePath("my-db");
        File dbShm = new File(db.getParent(), "my-db-shm");
        File dbWal = new File(db.getParent(), "my-db-wal");

        File db2 = new File("/sdcard/", "my-db");
        File dbShm2 = new File(db2.getParent(), "my-db-shm");
        File dbWal2 = new File(db2.getParent(), "my-db-wal");

        try {
            FileUtils.copyFile(db, db2);
            FileUtils.copyFile(dbShm, dbShm2);
            FileUtils.copyFile(dbWal, dbWal2);
        } catch (Exception e) {
            Log.e("SAVEDB", e.toString());
        }
    } else {
        Snackbar.make(mDrawer, "Please allow access to your storage", Snackbar.LENGTH_LONG)
                .setAction("Allow", view -> ActivityCompat.requestPermissions(this, new String[] {
                        Manifest.permission.WRITE_EXTERNAL_STORAGE
                }, 0)).show();
    }
} else if(id == R.id.action_load_db) {
    int permission = ActivityCompat.checkSelfPermission(this, Manifest.permission.READ_EXTERNAL_STORAGE);
    if(permission == PackageManager.PERMISSION_GRANTED) {
        AppDatabase.getInstance(this).getDatabase().close();

        File db = new File("/sdcard/", "my-db");
        File dbShm = new File(db.getParent(), "my-db-shm");
        File dbWal = new File(db.getParent(), "my-db-wal");

        File db2 = getDatabasePath("my-db");
        File dbShm2 = new File(db2.getParent(), "my-db-shm");
        File dbWal2 = new File(db2.getParent(), "my-db-wal");

        try {
            FileUtils.copyFile(db, db2);
            FileUtils.copyFile(dbShm, dbShm2);
            FileUtils.copyFile(dbWal, dbWal2);
        } catch (Exception e) {
            Loge("RESTOREDB", e.toString());
        }
    } else {
        Snackbar.make(mDrawer, "Please allow access to your storage", Snackbar.LENGTH_LONG)
                .setAction("Allow", view -> ActivityCompat.requestPermissions(this, new String[] {
                        Manifest.permission.READ_EXTERNAL_STORAGE
                }, 0)).show();
    }
 }