Home > iPhone, Mac OS X > Recover iPhone Contacts from iTunes Backup on a Mac

Recover iPhone Contacts from iTunes Backup on a Mac

In the event that someone has lost some or all of their contacts from Address Book or Outlook, but had them on their iPhone/iPad, there’s a way to extract the contacts data from the iTunes-hosted backup of their iDevice.

Since the procedure to do it for free can be a little tricky (lots of outfits are willing to sell you a way to do iPhone contact recovery for $24.95 and up), see below the jump for the way I did it.

1. Download iPhone Backup Extractor for Mac.

2. Create a new folder on the user’s desktop. For this example, I’ve named it recover.

Screen shot 2011-09-13 at 2.11.55 PM

3. Double click on the iPhone Backup Extractor application to launch it and click on the Read Backups button to automatically access the stored backups on your hard drive.

Screen shot 2011-09-13 at 2.12.31 PM

4. iPhone Backup Extractor will show you the contents of selected backup. Choose iOS Files and click on the Extract button.

Screen shot 2011-09-13 at 1.53.15 PM

5. iPhone Backup Extractor will prompt you to select the folder where it will extract the backup files. Choose the recover folder you created in step 2.

Screen shot 2011-09-13 at 3.06.47 PM

6. Full backup extraction will take 3 to 15 seconds, depending on the amount of data.

Screen shot 2011-09-13 at 1.54.12 PM

7. Once the extraction is done, navigate to /Users/username/Desktop/recover/iOS Files/Library. Here you will find all important data you need to restore including notes, SMS and the address book files which contain your iPhone contacts.

8. Open Terminal and run the following command:


cd "/Users/username/Desktop/recover/iOS Files/Library/AddressBook"

(Replace username with the user’s login.)

Screen shot 2011-09-13 at 2.43.16 PM

9. Once you’re in the AddressBook directory, you should see two files:

AddressBook.sqlitedb

AddressBookImages.sqlitedb

AddressBook.sqlitedb has the contact data that you’re looking to export out.

Screen shot 2011-09-13 at 3.10.37 PM

10. Next, run the following command:


sqlite3 AddressBook.sqlitedb

Screen shot 2011-09-13 at 2.44.18 PM

11. The prompt should change to sqlite>.

Screen shot 2011-09-13 at 2.44.57 PM

12. Now run the following commands in the order given:


sqlite> .mode csv
sqlite> .output contacts.csv
sqlite> SELECT ROWID, First, Last, ABMultiValue.value, record_id FROM ABPerson, ABMultiValue WHERE ROWID=record_id;
sqlite > .quit

A new file named contacts.csv should appear in the AddressBook directory.

Note: If the user’s backup is encrypted, you’ll get an error when running the following command. The backup will need to be decrypted from iTunes before trying step 12 again.


sqlite> SELECT ROWID, First, Last, ABMultiValue.value, record_id FROM ABPerson, ABMultiValue WHERE ROWID=record_id;

Screen shot 2011-09-13 at 2.49.52 PM

13. If there were no errors, open up the contacts.csv file and check the data inside. If necessary, remove any duplicated items and re-save the document as a .csv file.

14. Import the contacts.csv file into your contacts list.

Categories: iPhone, Mac OS X
  1. BoBoDuck
    October 21, 2011 at 11:04 am

    Thank you so much!
    You saved my life!!

  2. Vanessa
    October 23, 2011 at 11:53 am

    that’s a great how-to! would you by any chance know how to restore notes?

  3. Elan Gibb
    October 24, 2011 at 2:54 am

    Thank You so much. I’ve literally been looking for a week. Worked PERFECTLY!

  4. Ali Gary
    December 17, 2011 at 4:19 pm

    For recovering iPhones files on Mac OS X, this iTunes data recovery software also works great. See here for more: http://www.squidoo.com/data-recovery-itunes

    Merry Christmas!

  5. December 23, 2011 at 6:03 pm

    I would like to see the manual way of doing this, as my company wouldn’t spend the $$$ for the application. Anyone knkow of a site or forum that has it written up?

    • December 23, 2011 at 8:02 pm

      William,

      For recovering your contacts, the free/manual way of doing it is covered in this post. Do you need something else recovered besides contacts?

      • Will
        December 24, 2011 at 11:48 pm

        I see using the Backup Extractor, and the following steps. But I don’t see the manual process for what Backup Extractor does.

  6. Momi
    January 13, 2012 at 12:08 pm

    Hi after I enter this command :

    sqlite> SELECT ROWID, First, Last, ABMultiValue.value, record_id FROM ABPerson, ABMultiValue WHERE ROWID=record_id;

    I faced with this error :

    Error: near “sqlite”: syntax error

  7. Momi
    January 13, 2012 at 12:11 pm

    THANK YOU GOT WORK !

  8. Sam
    January 27, 2012 at 8:18 am

    Hey after entering,

    sqlite> SELECT ROWID, First, Last, ABMultiValue.value, record_id FROM ABPerson, ABMultiValue WHERE ROWID=record_id;

    I faced with this error :

    SQL error: no such table: abperson

    • January 27, 2012 at 8:29 am

      Sam,

      The commands are case-sensitive. Are you entering “….record_id FROM ABPerson,” or “….record_id FROM abperson,”?

  9. Stan
    January 31, 2012 at 7:56 pm

    Hi,

    After entering
    sqlite> .mode csv
    …> .output contacts.csv
    …> SELECT ROWID, First, Last, ABMultiValue.value, record_id FROM ABPerson, ABMultiValue WHERE ROWID=record_id;

    I got the following

    Error: near “.”: syntax error

    What did I do wrong?

    Thanks

  10. Luke
    February 3, 2012 at 11:42 pm

    You are an absolute legend. Trying to recover my girlfriends contacts after an update. Now it’s time to setup her google contacts to make sure I don’t need to find this page again. Thanks heaps

  11. EM
    March 25, 2012 at 10:32 am

    Hi,
    I extracted the IOS files into a recovery folder – but when I opened the IOS files folder there was nothing in there??

    HELP!

  12. david
    March 29, 2012 at 1:50 am

    Hi there,

    I extracted the “AddressBook.sqlitedb” file on my PC.
    I do have a mac. So If I just copy the “AddressBook.sqlitedb” to my Mac, how would I do this?
    I assume I start at step 9, but I’m confused on how to?

  13. April 11, 2012 at 8:00 pm

    LIFE SAVER!!! Yes…That is what you are sir! Thanks a million!!!

  14. Leah
    April 26, 2012 at 3:03 am

    Hey after entering,
    sqlite> SELECT ROWID, First, Last, ABMultiValue.value, record_id FROM ABPerson, ABMultiValue WHERE ROWID=record_id;
    I faced with this error :
    SQL error: no such table: ABPerson

    • CK
      May 14, 2012 at 7:28 am

      I got the same error because you left out a step which is to change directory to Library of the recovered files:

      cd “/Users/username/Desktop/recovery/iOS Files/Library/AddressBook”

    • Anders
      August 28, 2013 at 6:09 pm

      I am getting the same error, did you figure it out? I do not understand CK’s reply…I did not forget that step but am still getting the no such table:ABPerson

  15. Casper Kreffer
    May 10, 2012 at 9:32 pm

    I get the same error as Leah as well.. :-(
    Any way to fix this?

  16. CK
    May 14, 2012 at 4:17 am

    This has been a life-save for me. I recently updated the Office for Mac 2011 and IOS5.1.1 over the air and it all my contacts and calendar disappeared! Now with this tip and utility, I managed to recover my contacts. How do I recover my calendar? Thanks a million!!

  17. Mary
    June 6, 2012 at 3:35 pm

    Hello. This worked great after hours of research and failed options. However, each contact came through several times – for instance one person has one record for email, one for the work phone, one for the home phone, etc. How do we ask terminal to pull each of those fields as they are labeled in the sqlite file?

    Thank you so much for your time on this!

  18. doug555
    June 7, 2012 at 6:31 pm

    Hi! I get the same error after entering:

    …> sqlite> SELECT ROWID, First, Last, ABMultiValue.value, record_id FROM ABPerson, ABMultiValue WHERE ROWID=record_id;
    Error: near “sqlite”: syntax error

    What’s wrong?

  19. Morgan
    June 9, 2012 at 7:16 am

    Finally a free and simple way to do this!

  20. Jieni
    June 30, 2012 at 9:57 pm

    Step number 2 is named wrongly..should be “recovery” folder. That’s why directory go hay-wire?

    • July 1, 2012 at 12:26 am

      Jieni,

      Good catch! I’ve now fixed that, so that the commands and other references now consistently say “recover”.

  21. Astek
    July 16, 2012 at 11:41 am

    Hi Everybody,

    Does anybody know a simple way to decrypt Itunes BackUp? It seems like my back is encrypted even thought I never ask for it =(

    Thanks in advance for your support.

    SIncerely,

    Astek

  22. Tina
    July 18, 2012 at 6:53 pm

    Thank you for all of this information — It seems like I have completed all the steps correctly. I see the contacts.csv file in my AddressBook directory. When I imported it into my address book they all came up blank. I clicked the contacts.csv file to see if it had any information in it and it opens in the Numbers program as a spreadsheet where all of the names and contacts I’m trying to recover are displayed. Any advice on how I can get them back on my phone?

  23. Mariela
    July 21, 2012 at 3:06 am

    contacts.csv appears but it’s empty! What did I do wrong?

  24. July 21, 2012 at 10:30 pm

    i deleted the contacts by mistake from address book on mac and Icloud deleted them from my iphone. Any chance i can retrieve them?

  25. July 28, 2012 at 6:44 pm

    I’m trying to do this and get the same issue as multiple users above… and my back up is NOT encypted, 100% certain, and I didn’t change the case on any of the commands – once I get to this command: “sqlite> SELECT ROWID, First, Last, ABMultiValue.value, record_id FROM ABPerson, ABMultiValue WHERE ROWID=record_id;”

    It tells me I have a syntax error… like many others above.
    Last login: Sat Jul 28 14:25:26 on ttys000
    ic:~ ic$ “/Users/ic/Desktop/recover/iOS Files/Library/AddressBook”
    -bash: /Users/ic/Desktop/recover/iOS Files/Library/AddressBook: is a directory
    ic:~ ic$ sqlite3 AddressBook.sqlitedb
    SQLite version 3.6.12
    Enter “.help” for instructions
    Enter SQL statements terminated with a “;”
    sqlite> sqlite> .mode csv
    …> sqlite> .output contacts.csv
    …> sqlite> SELECT ROWID, First, Last, ABMultiValue.value, record_id FROM ABPerson, ABMultiValue WHERE ROWID=record_id;
    SQL error: near “sqlite”: syntax error
    sqlite>

    Thoughts on what’s going on?

  26. Joseph
    August 1, 2012 at 1:25 pm

    how to decrypt iTunes

  27. venkatesh
    August 4, 2012 at 12:45 pm

    Here is the command i used to navigate through folders:

    cd ~/desktop/recover/kos/library/addressbook after logging into my username(which will be default i think)

    then i used this command sqlite3 AddressBook.sqlitedb which changed the command line to sqlite>

    Next, i ran the following commands one by one in the order given:

    sqlite> .mode csv
    sqlite> .output contacts.csv
    sqlite> SELECT ROWID, First, Last, ABMultiValue.value, record_id FROM ABPerson, ABMultiValue WHERE ROWID=record_id;
    sqlite > .quit

    This gave me a CSV file which i imported to contacts app. However, i can only see blank names with no contact information in it.

    Any solution appreciated. Thanks in advance..!!!

  28. August 7, 2012 at 1:13 pm

    This thing is so complicates. I have given up. I am currently backing up my iPhone contacts via Copy-Trans Contacts.

    iTunes has to seriously think of letting people have access to their contact backups!

  29. Zinc
    August 27, 2012 at 11:03 am

    my phone’s backup is not encrypted but when I followed the instructions, it returns me with the following:

    SQL error: file is encrypted or is not a database

    Prior to this, this is the whole chain of command that I entered into Terminal:

    cd “/Users/zincteo/Desktop/recover/iOS Files/Library/AddressBook”mbp:AddressBook zincteo$ sqlite3 AddressBook.sqlitedb
    SQLite version 3.6.12
    Enter “.help” for instructions
    Enter SQL statements terminated with a “;”
    sqlite> .mode csv
    sqlite> .output contacts.csv
    sqlite> SELECT ROWID, First, Last, ABMultiValue.Value, record_id FROM ABPerson, ABMultiValue WHERE ROWID=record_id;
    SQL error: file is encrypted or is not a database

    I need my contacts badly cos some idiot stole my iphone 4. So I’m using my iphone 3g currently and apparently I am unable to perform the restoration due to version issues.

    Any help is greatly appreciated, and thanks in advance!

  30. Amy
    September 7, 2012 at 4:04 pm

    The whole thing worked except I don’t see a .csv file… :(

  31. Mahir
    September 9, 2012 at 7:11 pm

    Zinc :
    my phone’s backup is not encrypted but when I followed the instructions, it returns me with the following:
    SQL error: file is encrypted or is not a database
    Prior to this, this is the whole chain of command that I entered into Terminal:
    cd “/Users/zincteo/Desktop/recover/iOS Files/Library/AddressBook”mbp:AddressBook zincteo$ sqlite3 AddressBook.sqlitedb
    SQLite version 3.6.12
    Enter “.help” for instructions
    Enter SQL statements terminated with a “;”
    sqlite> .mode csv
    sqlite> .output contacts.csv
    sqlite> SELECT ROWID, First, Last, ABMultiValue.Value, record_id FROM ABPerson, ABMultiValue WHERE ROWID=record_id;
    SQL error: file is encrypted or is not a database
    I need my contacts badly cos some idiot stole my iphone 4. So I’m using my iphone 3g currently and apparently I am unable to perform the restoration due to version issues.
    Any help is greatly appreciated, and thanks in advance!

  32. Trevor
    September 11, 2012 at 8:29 pm

    thanks .. great set of instructions.. worked for me first time although it would be great to add the email addresses etc…

  33. Omar G.
    September 13, 2012 at 8:49 pm

    Worked like a charm… Thanks for the this Tut…. Just one ? where do I put the csv? into iPhone(Where)….

    Thanks in advance….

  34. Mary
    September 27, 2012 at 2:11 am

    I wasn’t aware that deleting contacts from address book on macbook would wipe out all my contacts on my iphone 5…now i have no contacts and have done a factory reset and restore from icloud with no luck. followed your instructions and imported into addressbook but all contacts show up as no name with no data…please tell me how i can fix this.

  35. Ron
    October 25, 2012 at 11:25 am

    Thanks for the clear and flawless instruction.

  36. Tracey
    November 10, 2012 at 8:56 am

    Worked great, but the contacts.csv file is empty. any suggestions?

  37. jenni
    January 9, 2013 at 5:31 am

    hello can somebody help me i stood in that part extracting iOS files???

  38. matias
    February 7, 2013 at 3:49 am

    Hi after I enter this command :
    sqlite> SELECT ROWID, First, Last, ABMultiValue.value, record_id FROM ABPerson, ABMultiValue WHERE ROWID=record_id;
    I faced with this error :
    Error: near “sqlite”: syntax error

  39. April 2, 2013 at 4:17 am

    This mac Tool can recover the deleted fiels in iphone database. I use it.

    http://www.datarecoveryforiphone.com/ios-data-recovery.html

  40. April 6, 2013 at 4:51 pm

    Thank you very much.
    You help me solve a great problem.

  41. Lelliot Peters
    April 20, 2013 at 6:56 am

    I agree with William, Stellar Phoenix is better among all data recovery tools for Mac. There is no free software for data recovery in Mac. All softwares are free upto some extent, for complete recovery you need to pay. Stellar Phoenix also provide just free scan, recovery is paid.
    http://www.ipadconverter-mac.com/iphone-recovery/

  42. Danny v
    June 2, 2013 at 8:17 pm

    For email addresses, physical addresses, etc. I found this SELECT statement to be useful, if a bit verbose:

    SELECT ROWID, First, Middle, Last, Organization, Note, Birthday, JobTitle, ABMultiValue.value, UID, ABMultiValueEntry.value FROM ABPerson LEFT OUTER JOIN ABMultiValue on ROWID=record_id LEFT OUTER JOIN ABMultiValueEntry on parent_id=UID ORDER BY rowid, parent_id;

  43. John
    June 16, 2013 at 10:07 pm

    THIS IS THE ONLY THING THAT WORKED TO GET MY CONTACTS BACK!!
    A very big thank you to you :)
    I’ve been searching google all night for a method that would work.
    Brilliant

  44. Chris
    July 11, 2013 at 11:57 pm

    Thank you so, so, so, much!!! Very helpful and detailed. Worked perfectly the first time through. :) Exactly what you need if you’ve lost your iPhone

  45. Brigitte
    September 22, 2013 at 5:43 pm

    matias :
    Hi after I enter this command :
    sqlite> SELECT ROWID, First, Last, ABMultiValue.value, record_id FROM ABPerson, ABMultiValue WHERE ROWID=record_id;
    I faced with this error :
    Error: near “sqlite”: syntax error

    Have you figured out how to fix this? I cannot seem to find the answer

  46. angeloftheprairie@msn.com
    September 27, 2013 at 5:34 am

    I am struggling with this because my contacts.csv comes up with an empty Excel file. I see questions here with the same problem…but no answers. Any help to be had?

  47. Den
    December 9, 2013 at 11:55 am

    For those who ask why the file is empty; it’s because there is nothing in your backup. Your contacts were never on the phone to back up. Try this instead: Settings / Mail, Contacts, Calendars / go to each of you accounts and set Contacts to green (on). If your contacts are in ‘the cloud’ they will re-appear once the phone has completed syncing over wifi.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 160 other followers

%d bloggers like this: