Sunday, January 17, 2010

Restore address book from iTunes backup

I've recently had my last iPhone stolen and went ahead and made the switch to Verizon with the Droooiiiiiid (I love that ringtone). My problem was that I had no way of retrieving contacts since I didn't sync the contacts with any mail program or contacts because I strictly use gmail and their web interface for mail. Perhaps there was a way to sync those contacts with gmail, but I was unable to find the process.

So now, the only chance I have of retrieving my address book is through the backup files made by iTunes. I was using Windows 7 Home Premium at home (only because I had the opportunity to buy an advanced copy for $50). iTunes stored the backups in C:\Users\<username>\AppData\Roaming\Apple Computer\MobileSync\Backup. I found a very useful tool called iphonebackupextractor. I ran it from the windows machine and was able to export the Address book sqlite database with it. After that, it was clear sailing. I suppose I should mention that the only reason this tool worked was because I never told iTunes to encrypt my backups. If you do have them encrypted, you will have more issues.

I copied the sqlite database file to my linux computer and went at it from the CLI that i'm comfortable with.

From here on out, I'll be posting actual commands with notes:

sqlite3 /path/to/sqlite/file
.headers on
.mode csv
.output addressbook.csv
select ABPerson.first as 'First Name',ABPerson.last as 'Last Name',ABMultiValue.value from ABPerson,ABMultiValue where ABMultiValue.record_id=ABPerson.ROWID;

Now I had addressbook.csv on my drive and imported that into my Google contacts . I had to do quite a bit of cleanup after this. With a little more research and some tricky SQL queries, I could have fixed most of the issues.

Basically the ABMultiValue.value field is a lot of things such as: Notes, Email addresses, Phone numbers... all related to the contact. I could have identified each value type and labeled them as such, but 90% of my contacts only had phone numbers. Google contacts gave me a quick and easy way to merge duplicates and such so it wasn't too bad dealing with my 200+ contacts.

No comments:

Post a Comment