Merging two iPhone SMS sqlite databases

Disclaimer: I haven’t actually done it, yet.


Last night I rediscovered my original iPhone SMS database from before I did a factory-reset on my phone. I had jailbroken my 3G ages ago, and ever since then, the carrier wasn’t updating properly, regardless of the SIM card I used or country I was in. So in 2011 I reset the phone, and continued from there.

In 2012 I tried to merge the two databases using some hacky code I threw together, and of course it didn’t work. The problem, as it turned out, was that the SMS database format had changed significantly between iOS 4 and iOS 5 to make allowances for iMessage, and I was trying to import an iOS 4 database back onto my phone using iBackupBot.

So the first order of business was to restore the old database to my old iPhone, so that I could upgrade it to the new format, which is now done.

Thus far, I’ve managed to dump both sqlite databases, using the same database structure, to SQL scripts. I’m going to use some C# to build classes to represent each table, build up a list of each class in memory from the files, then renumber the newer ID fields (I’ll just increment them with the highest value of the old database), so that there aren’t any unique constraint collisions (the problem I’m sitting with right now).

Having both of the (20MB in total) SMS databases in memory will also allow me to strip out the MMS attachments, because those messages won’t work on the new phone. I want to update some of the telephone numbers as well, so that I don’t have to sit with twenty message threads from the same person.

Then when I’m done cleaning up the data, I’ll write it out to the same database structure, rebuild the triggers, and then use iBackupBot to copy the new database file back over again.