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.

12 thoughts on “Recover iphone contacts and SMS (text) from backup

  1. Hi, Mela

    Thank your for posting this. I am helping a friend retrieve his iphone contacts after a firmware update. I am completely lost and cannot figure out how to get his contacts to him.

    I followed your instructions:
    I imported the address book sqlitedb file to the sqlite firefox addon,
    I ran the query,
    No errors are returned
    I see all the contacts, over 1000 of them.
    How do I get the contacts back? I tried to export as .csv so i can convert to vcard but only the 9 categoriesunderwhich the cantacts were saved are shown.
    What am I doing wrong?

    Please help.

    Thanks,

    Cacy

  2. Hi Cacy,

    I copied my contacts off the screen (just select all, copy and paste elsewhere). Excel is good at understanding table formats. So if the csv export is not working for you then try copying it to Excel and save as csv from there.

  3. hi, mela.
    i did your tuts, but i can’t see any of my addressbook.
    after i click Run SQL, it returns with “not an error”.

    i choose select *from TableName, then i delete *from TableName and replace it with the line you mentioned above.

    did i miss something?
    thanks

  4. @fabulous That’s odd. So you put the sql query, run it, and you got “not an error” message? I don’t have a sqlite database at hand right now to remind myself how it all looked like. But I guess you can start by doing,

    select * from ABPerson

    and if that doesn’t work check the table ABPerson exists. Maybe you haven’t loaded the right database or maybe they have a new database schema now. Stubbing in the dark here.

  5. @Sandy I haven’t tried my approach on a Mac but after scanning the post again I don’t see why it wouldn’t work on a Mac. Download the Mac version of the backup extractor, not the windows (exe) version. That could be why you got “this program cannot be run in DOS mode”. The other one is SQLite Manager, which is installed from firefox. You will need the mac version of firefox as well if you don’t have firefox installed already.

    Having said that, if it is all too technical for you then get expert advice.

  6. every program i’ve loaded to help with this tells me “this program cannot be run in DOS mode”. I have an iMac and an iPhone4. i need to recover every text message ever sent between myself and one particular contact, for an upcoming court case. sqlite and iphonebackupextractor both come back with that message. the sqlite manager opens, but i can’t access anything from it. i’m painfully computer illiterate, any help is greatly appreciated!

  7. Thanks..worked very well for me…can you correct the case of some of the words in the command
    “select ABPerson.prefix, ABPerson.first,ABPerson.last, ABMultiValue.value from ABPerson,ABMultiValue where ABMultiValue.record_id=ABPerson.ROWID”
    to
    “Select ABPerson.Prefix, ABPerson.First,ABPerson.Last, ABMultiValue.value from ABPerson,ABMultiValue where ABMultiValue.record_id=ABPerson.ROWID”
    so that a direct copy paste will work and not confuse some of the non-technical persons into thinking that command does not work

  8. You are awesome, I manage to save all of my contacts without a cuss word out of my mouth. Thank you so much. now i need to figure out how to save my photos. Thanks again so much

  9. Thanks for posting this, it has saved me a lot of time and all my contacts are recovered – brilliant and much appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>