Thursday, July 16, 2009

Restore contacts from iPhone backup.


Problem: Got new iPhone 3Gs and iTunes inadvertently deleted my contacts from my old 3G iPhone.

Idea: Locate and restore the contacts from the last iPhone backup done before upgrading the old phone to the new 3.0 OS.

After much consternation I was finally able to locate the files that hold the contacts. As it turns out, the iTunes backup is really just a collection of thousands of SQLite databases. Below are the ones you need.

cd6702cea29fe89cf280a76794405adb17f9a0ee.mddata - Address Book Images
31bb7ba8914766d4ba40d6dfb6113c8b614be442.mddata - Address Book Contacts

Failed Attempt: Other sites suggested that all you had to do was back up your phone, replace these files in the new backup (along with their associated mdinfo files), and then restore. I tried this but it did not work. My originally backup was from the 2.2 iPhone OS and the new backup was from 3.0, so this may have been why it didn't work.

Successful Attempt: Using the SQLite Manager add-on to Firefox I was able to open the mddata files and write sql queries to join the data so I could export it.

I removed all the data and then uploaded it to my gmail account where I then synchronized it to my iPhone in iTunes.

Success at last!

You can also open mddata files and export the tables to .csv files that you can open in Excel.

Steps to open contact file:

1. Open the Address Book Contacts in SQLite Manager addon for Firefox.

2. You should see the tables in the database that contain contact data.

3. The ABPerson and ABMultiValue tables contain most of your contact data (if it was backed up).  Press the Execute SQL.
4. Enter the following SQL in the Enter SQL box and press the Run SQL button:

select ABPerson.prefix,ABPerson.suffix, ABPerson.first,ABPerson.middle,ABPerson.last, ABMultiValue.value, 
CASE WHEN ABMultiValue.LABEL = 1 THEN 'MOBILE' 
WHEN ABMultiValue.LABEL = 2 THEN 'OTHER'
WHEN ABMultiValue.LABEL = 3 THEN 'HOME'
WHEN ABMultiValue.LABEL = 4 THEN 'WORK'
WHEN ABMultiValue.LABEL = 5 THEN 'MAIN'
END AS 'Type',
ABPerson.note, ABPerson.nickname, ABPerson.organization, ABPerson.department, ABPerson.jobtitle, ABPerson.birthday from ABPerson,ABMultiValue where ABMultiValue.record_id=ABPerson.ROWID order by ABPerson.First

5. You should see your contacts listed.  Select "Save Result (CSV) to File" from the Actions dropdown and specify the location to save your file.

6. Now you have your contacts in a format you can import to other address books!




6 comments:

  1. if i send to u my 31bb7ba8914766d4ba40d6dfb6113c8b614be442.mddata - can u recover all contacts?

    ReplyDelete
  2. I could try if you place it somewhere I could download it.

    ReplyDelete
  3. hi i downloaded razairsql how to convert 31bb7ba8914766d4ba40d6dfb6113c8b614be442.mddata
    pls advise its more help for me

    Thanks

    ReplyDelete
    Replies
    1. Use the SQLite Manager add-on to Firefox instead. Once you open the file you can export all the tables to .csv files that you can open in Excel and manipulate as needed.

      Delete
  4. hi, i have download but it did not show the contacts in the database...i could not run the sql

    ReplyDelete
    Replies
    1. I added more detail that I hope answers your question.

      Delete