Recover iphone contacts and SMS (text) from backup

I just broke my iPhone. I need to wait a while before I could get a replacement. In the meanwhile, I needed to recover my contacts (address book) from a backup file. Thanks to this discussion where I got most of the info I needed. There were a few turns though. So I’ll write the steps if it helps somebody.

I used: Windows XP, iTunes 8.2, Firefox.

1. Back backup

I needed to use a tool to recover my data. I first backed up my backup files because I wasn’t sure if I could trust the tool. So I simply made a copy of the folder C:\Documents and Settings\MyUserName\Application Data\Apple Computer\MobileSync\Backup in another folder. I finally realized that I didn’t need this step because everything worked ok. But if you want to be comfortable then go for it. It took my machine about 4 minutes to copy the whole folder.

2. Extract address book

To extract the address book from the backup file, I used the tool Backup Extractor for iPhone (see update at the end of this post for a more recentl URL). I downloaded the free version which turned out to satisfy my needs allright. I run the program and I got a list of backup files to restore. Some were iPhone files and the rest were iPod files. I chose the most recent iPhone backup. The second step of the restore wizard brought up two options. I had to choose the second option (manual). The first one is not available in the free version of the software anyway. On the third step, I navigated to Library->AddressBook and checked AddressBook.sqlitedb. At the last step I chose to put the restored file in C:\restored. When the process finished I got the file C:\restored\AddressBook.sqlitedb. I then had to rename the extension of this file from .sqlitedb to .sqlite. This will become important shortly.

3. Open AddressBook database

I opened firefox and installed the SQLite Manager add-on for Firefox, restarted firefox. I then went to Tools->SQLite Manager. On SQLite Manager window I clicked the open icon and selected C:\restored\AddressBook.sqlite.

4. Retrieve contacts from database

The discussion I referred to earlier gives a nice query to select contacts from the database,

select ABPerson.first,ABPerson.last, ABMultiValue.value from ABPerson,ABMultiValue where ABMultiValue.record_id=ABPerson.ROWID

I went to “Execute SQL” tab in SQLite Manager window in Firefox, pasted the above query and run it. That gave all my contacts. Some of the numbers were missing first and last names so I couldn’t tell who they belonged to. The trick was to change the query to select the ABPerson.prefix column too,

select ABPerson.prefix, ABPerson.first,ABPerson.last, ABMultiValue.value from ABPerson,ABMultiValue where ABMultiValue.record_id=ABPerson.ROWID

Now I need to call my insurance…

EDIT

I just recovered my SMS as well. The process is similar except in step 2 extract Library->SMS->check sms.db and rename sms.db to sms.sqlite. In step 4 use the query,

SELECT rowid, address, date, text FROM message ORDER BY date

UPDATE

iPhone Backup Extractor has moved to a new address now. Thanks to Matt Collins for the update.