Randolph Success! Sort of …
Instead of the complicated method I described previously, I’ve gone manual, which is, yes, more complicated :-).
I started with the latest sms.db file from my new iPhone, and then editing SQLite scripts, incremented the ROWIDs by 30,000 for the message table, and 1000 for the others, so as to avoid collisions. I then ran the INSERT statements from the old sms.db file, with their original ROWIDs, so that they’d slot in before the newer stuff. There are significant gaps in the ROWIDs, but I don’t care about those because I can store two billion messages before I run out of numbers.
The iBackupBot restore was a success, albeit edge-of-my-seat stuff when I opened the Messages app and got a blank screen (it eventually opened up, but it took a while).
The biggest complication was the relationships between chats, handles and messages. I still don’t know what a handle is, but to be fair, I haven’t even looked at the table closely.
Because the chat table has a unique constraint on the [guid] field, which forced me to go manual, I had to map this value back to the old sms.db chat table, and this is where the “sort of” in my original statement comes from: some of the conversation threads (chats) were wrongly attributed to contacts, groups of contacts, and so on. This is due to my naïveté in understanding the data.
Second phase is to analyse the records more closely to see how handles and chats work exactly, and then use a different method to remove duplication.
So for all you people out there, I am using SQLiteAdmin, TextPad, and a lot of patience to manually match key fields between both databases.
2013-03-09 Edit: According to John Lehr, the handle_id is the link I should have deduplicated first, before doing the chat_id. Simple enough (though manual), so I’ll keep that in mind when I address this again.
It’s been fun following your attempts to do this. I’m running into some problems: I want to combine some sms messages from a 3GS with those from an iPhone 5.
I have created run all the relevant INSERT statements. The new SMS database is readable (and correct in terms of all the messages I want being there) in iBackupBot, but the restore fails. Any ideas?
When you created the backup file with iBackupBot, did you export an information file with it as well? That’s the file you’ll need to reimport the database into the backup set.
Other issues I ran into with compatibility came down to the version of SQLite I was using. I had to have the very latest DLLs to open and modify the iOS 6 SMS database format.
Yeah, the Info.plist file is there. I’m now getting the ‘restore successful’ message in iBackupBot, but the messages are not appearing (though, as I mentioned before, they appear fine – merged, as expected – when I view the list of messages in the iBackUpBot viewing pane). Officially out of ideas!
It took a VERY long time for any messages to appear when I did the restore. At one stage I thought I’d completely broken it.
I suspect it has to do with indexes and the Spotlight search.
I’d wait it out for at least five minutes.
Think I’ve figured it out:
http://stackoverflow.com/questions/8506085/moving-sms-from-one-iphone-to-another
Just need to calculate the checksum and filesize, and then edit manifest.mbdb
I’ll check back in and let you know how I get on!
I didn’t need to do that with the SMS.db file (I think iBackupBot modifies the manifest file), but I may be mistaken. Good luck!
No luck, which might be to do with the fact that I’m working from 3d0d7e5fb2ce288813306e4d4636395e047a3d28 taken from the backup folder, whereas you seem to have got hold of sms.db somehow (which, i guess, is from /private/var/mobile/Library/SMS/sms.db). Assuming you replace sms.db with the merged sms.db, then it would make sense for manifest.mbdb to be re-generated.
That is what I did, yes. I’m not sure why iBackupBot isn’t showing you the file name correctly.
Sorted! Thanks for your help – found sms.db and the manifest file must have fixed itself.
Glad you came right!