3

I wanted to know what the best practices are for backing up and restoring a SQLite DB on android. Currently I approach the issue by taking the DB to be backed up and using File Input/Output Streams to copy it to the sd card. Then I use the reverse process if I want to restore and old backup.

This method seems to work and has not yet corrupted my data. I am wondering if this is the best approach or if there is a safer way of doing this?

Thanks

ControlAltDelete
  • 3,576
  • 5
  • 32
  • 50

3 Answers3

17

Just an FYI on backing up the database. I currently do this in my app the same way you are explaining above. Beware when creating backups this way. It works great for the most part, but the problem is that the backup that's created is not guaranteed to be compatible with all devices and Android versions. I thought this sounded odd the first time I heard that, but I'm now finding that it's true. I have been receiving several reports lately of missing data, disappearing data and such. This was all happening when the user was restoring a backup either from a different device or a different Android version or ROM. A few of them contacted me directly, which was great, so I was able to get backup files from the to test them out and examine them. When I tried to restore them, I would get the following logcat error: android.database.sqlite.sqlitedatabasecorruptexception: database disk image is malformed

What I was finding out was that, mainly, certain HTC devices and some custom roms (on any device) were creating these backups that wouldn't restore to other devices or roms. The databases weren't really corrupt, but Android thought they were. I would take them into a SQLite browser and no data would show there either. It turns out that newer versions of SQLite have WAL (Write Ahead Logging) enabled by default and if it is enabled and a backup is made with that database, it cannot be restored to an older version of SQLite or even sometimes the same version (for some odd reason). So, I disabled WAL with "PRAGMA journal_mode = DELETE" and then I was able to view the database in the browser and able to restore it on my test device fine. The other problem is that there doesn't seem to be a way to catch this exception in code and Android automatically deletes the database when it comes across this exception (very bad management on Android's part in my opinion).

Sorry for the long response, but I wanted to explain what I was seeing happening with this sort of backup. I'm in the process of trying to find another way to create a universal backup on the SD card. Creating csv files and sql scripts like @Kingamajick said may be another way to do it. It is more code and more work, but if it works on any device, SQLite version and ROM then it would be worth it. Your customers losing data is never a good thing.

ssuperz28
  • 1,834
  • 1
  • 14
  • 23
  • Can you specify what API releases you have found issues in. thx – ControlAltDelete Jun 01 '12 at 15:48
  • 1
    This has been happening on HTC devices running Android 2.3 I've also seen it on CM7 and CM9 roms. It's not all HTC devices though, most notably the Desire series. Apparently those HTC devices and Cyanogemod have WAL enabled by default in SQLite and they don't play well with other roms, devices or SQLite versions. I'm thinking as more devices get ICS and a newer version of SQLite, these older backups that are created on pre 4.0 devices will start to be problematic when they are restoring. I didn't see any exceptions like this until 2.3.3+ was released and more devices moved away from FROYO. – ssuperz28 Jun 01 '12 at 16:48
  • are you using transactions in Sqlite to make sure data is either all or not at all written? – ControlAltDelete Jun 01 '12 at 17:01
  • Yes I'm using transactions. The data is there in the backup, it can be viewed in a SQLite browser that incorporates the newest version of SQLite or one that incorporates an older version as long as you set "PRAGMA journal_mode = DELETE". – ssuperz28 Jun 01 '12 at 17:56
  • Here is what I am thinking could be a good fix. What if you create your database at the deployment of your app. That way you know what the journal mode is at deployment and don't have to worry about it later. I suppose you could do a update of an exiting database and issue a rawsql command to keep Pragma journal_mode=Delete. Let me know if you think I am missing something here. Thx – ControlAltDelete Jun 03 '12 at 14:07
4

This seems like it would be the best approach. You may want to consider taking a checksum of the SQLite file before copying and comparing it with the destination file for extra assurance. Just ensure there are no open connections to the database when you take the copy, otherwise you may end up with the DB in an unexpected state when it's restored.

The only other way I could see to do it, would be to read the actual contents of the DB and generate a file containing the SQL which which it can be restored from, this is obviously a more complex and doesn't offer any advantages to justify this complexity.

Kingamajick
  • 2,281
  • 1
  • 16
  • 19
0

I'd add one additional comment to Kingamajick's answer (the forum won't let me add it as an actually comment there). In the approach that simply copies the file, if a user ever restores the DB and there happens to be any data already in it, it will be overwritten. For example, if a user upgrades to a new phone, uses it for a while, and then restores the DB from their old phone, any data already on the new phone will be lost. That is one advantage to the complexity of reading the DB and writing it out to a file (XML or CSV, etc.).

I posted another question (Android sqlite backup/restore without overwriting) in hopes someone had a better solution that avoided this problem, but so far it seems like there isn't one. Between that and the concerns ssuperz28 noted it seems like a much safer way to backup your DB is to write it out to xml and then read it and add it back in on restore.

Also, https://stackoverflow.com/a/34477622/3108762 is the best of the other suggestions I've seen so far and promises a better way to approach this starting in Marshmallow.

Community
  • 1
  • 1
T. Shaffner
  • 359
  • 1
  • 5
  • 22