Recover Iphone contacts from raw backup

Just as I got my new phone ( t-mobile MyTouch4G -- love it!) my 23 month old iphone completely refused to charge from either the wall or computer. So how to get my contacts off?

I have a full mirror of my iphone (3g) filesystem, created using rsync+ssh from within my jailbroken phone. It is way cooler to backup over wifi than through a tethered cable; I had no other choice as the data connector died after 14 months. so I had no other option to make backups. I was able to charge from a wall adapter but not from any USB hosts. This crippled setup worked long enough for me to escape my AT&T contract.

Useful tidbits:

1. contacts are stored in AddressBook.sqlitedb
   
2. file is stored in /private/var/mobile/Library/AddressBook/AddressBook.sqlitedb
   
3. There is a second, bare-schema database in /private/var/root/Library/AddressBook/
   
4. The database is in sqlite3 format.
   
5. Person entries are stored in ABPerson table
   
6. Phone number/email/etc entries are stored in ABMultiValue table

We can open this file in sqlite3 and export it into a usable comma-separated-file without any other external tools. The person entries are stored in ABPerson, but the phone number entries are stored in ABMultiValue. We join the two tables together in our CSV output.

The following snippet will copy the database to /tmp,open it in sqlite3 and export to contacts.csv. # copy the db to /tmp, then open it cp /private/var/mobile/Library/AddressBook/AddressBook.sqlitedb /tmp cd /tmp sqlite3 AddressBook.sqlitedb sqlite> .mode csv sqlite> .output contacts.csv sqlite> select ROWID, first, last, identifier, value, record_id from ABPerson p join ABMultiValue mv on (ROWID=record_id) sqlite > .quit

The file locations and names was surprisingly hard to find. On the bright side, I didn't need to decode any plist files.

There are some more interesting fields in ABPerson and ABMultiValue, feel free to update the select to grab more fields.

sqlite> .tables ABGroup ABPersonMultiValueDeletes ABGroupChanges ABPersonSearchKey ABGroupMembers ABPhoneLastFour ABMultiValue ABRecent ABMultiValueEntry ABStore ABMultiValueEntryKey FirstSortSectionCount ABMultiValueLabel LastSortSectionCount ABPerson _SqliteDatabaseProperties ABPersonChanges sqlite> .schema ABPerson CREATE TABLE ABPerson (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, First TEXT, Last TEXT, Middle TEXT, FirstPhonetic TEXT, MiddlePhonetic TEXT, LastPhonetic TEXT, Organization TEXT, Department TEXT, Note TEXT, Kind INTEGER, Birthday TEXT, JobTitle TEXT, Nickname TEXT, Prefix TEXT, Suffix TEXT, FirstSort TEXT, LastSort TEXT, CreationDate INTEGER, ModificationDate INTEGER, CompositeNameFallback TEXT, ExternalIdentifier TEXT, StoreID INTEGER, DisplayName TEXT, ExternalRepresentation BLOB, FirstSortSection TEXT, LastSortSection TEXT, FirstSortLanguageIndex INTEGER DEFAULT 2147483647, LastSortLanguageIndex INTEGER DEFAULT 2147483647); sqlite> .schema ABMultiValue CREATE TABLE ABMultiValue (UID INTEGER PRIMARY KEY, record_id INTEGER, property INTEGER, identifier INTEGER, label INTEGER, value TEXT);