My sister lost her iPhone a couple of days back and with that all her contacts, notes and calender entries that she had painstakingly stored on the device.
Her only hope of getting all that important data back was the backup that she had taken a couple of days earlier and she asked me to help her out.
Well, truth be told. The iPhone is not the most open among gadgets. And it isn’t easy to get your data off it. Apple has tried it’s best to hide any iPhone related information from the user’s view. And they have succeeded to an extent, atleast the primary consumers of Apple’s goods don’t really care where (or more importantly, how) the data is stored, as long as Apple provides an application to access it.
In the iPhone’s case, the application is iTunes and the only way to put this data back on to the device is to use iTunes … unless you know where to look for it.
On a computer running windows vista, the data backup is stored in the following location:
C:\Documents and Settings\USERNAME\Application Data\Apple Computer\MobileSync\Backup
Under the backup directory, you will see a directory which looks like a unique ID. This is the directory in which the backup is created, everytime you backup your iPhone using iTunes.
In this directory, you will see a number of files with the *.mdbackup extension.
The .mdbackup files are the actual backups of all the applications and settings of your iPhone. So, the first thing you should be doing is, make a backup of these files :-)
If you use an editor to view these files right now, all you’ll see is garbage, cause these are not text files. They are what Apple calls Binary plists. And to decrypt them, you ideally need a Mac and this utility.
I used the command line version of the utility since I only had console access to a mac and that worked for me.
I first copied all the *.mdbackup files, that I had collected from the iTunes backup folder, to the Mac and ran the following command to zero in on the files that actually had any useful data. I was the most interested in the Contacts data, so that’s what I searched for first.
grep ‘AddressBook’ *.mdbackup
AddressBook is the name of the application which stores, well, address book data on an iPhone. It is only named Contacts in the interface. The actual application name is AddressBook and when I searched for that string in the backup files, I got three files as a result.
I opened the first file using a text editor and it turned out to be the one I was interested in. The word AddressBook is in the beginning of this binary file and it also mentions the database name, where all the data is stored. Yay !
So, now to get to that data base file, we need to use the decoding utility that we downloaded earlier.
So, we’ve got our database file. The decode iphone utility will put it in the following folder structure
/Users/<username>/MobileSyncExport/Library/AddressBook/
Open the database with the following command:
sqlite3 AddressBook.sqlite
and you’ll be dropped to the sqlite prompt. At this point, you need to understand the schema of this database a bit before you can proceed further. Give “.tables” command and you should see the following output.
At this point, I had to use a bit of trial and error to figure out which table holds what data. To view the schema of a particular table you can use the following command.
.schema <table name>
Using this command on the ABPerson table gave me the following result.
There was other information also, but this is basically what I was interested in. So, this particular table was all the information about the a Person, except the phone number. To look for the phone number, I had to look at another table – ABMultiValue. And the two tables are linked through the ROWID and record_id fields in the ABPerson and ABMultiValue tables respectively.
It turns out that the ABMultiValue tables lists all the phone numbers and a quick “select * from ABMultiValue” gave me the answer I was looking for. So, now I finally know where all the data is. To collect all the data in a single place, I used the following SQL query.
select ABPerson.first,ABPerson.last,ABMultiValue.value from ABPerson,ABMultiValue where ABMultiValue.record_id=ABPerson.ROWID
Once I’d confirmed that this was indeed the correct data, I gave the following command on the sqlite prompt
.output backup.txt
That made sqlite put all output into the backup.txt text file rather than the console. And I was set. Ran the above select statement once again and I had all the phone numbers in a nice text file.
How was that for a weekend project, eh ?
In a future post, we’ll talk about taking out the notes and Calender entries from a backup file. The procedure is almost the same, so if you guys can figure it out yourself till then, post the solution in the comments.
|
About Sharninder: Programmer, blogger and a geek making a living shifting bits around the Internet. Sharninder is the owner of Geeky Ninja |
51 Comments
interesting post.. tell me a way to doing it in Windows.. then it will be more cool ..hehe
I did find a couple of tools to decrypt the binary plist in windows, but none of them worked for me. That part I had to do on a Mac. The rest is pretty standard stuff. sqlite is available for Windows, so you might want to try that out.
I am the one who lost the Iphone.. but bhaiya… NO CLUE it was sooo freakin hard.. i mean.. i got it only till the time u said u need something in mac.. heheh..
i stopped there.. i dont have one..
hehehe
but thanks tho.. i really needed those contacts.. :)
All I need to do in my phone is to copy the PIM file.
@abhilash: Lucky you :-)
interesting, but any way to do this in winDOZE
Except for the first part, of decrypting the .mdbackup file, rest everything can be done on Windows. sqlite tools are available for windows. Actually I did find a script to decrypt the binary plist file in windows also, but for some reason it didn’t work for me, so I used a mac.
Dude I just used iphone backup decoder on a Mac which u have linked here and its just point and click to get the stuff back but it only gives you sqldb’s so from there onwards have to follow your tip. Works great. thanks.
@Raj: Glad you found the post useful.
If you have multiple phone numbers it lists it as another contact with the 2nd/3rd number… is there are a way to have it consolidated into one row?
Thanks
@Bradley: Yes, the multiple phone numbers come as seperate entries but this is just a text file that you’re getting. I don’t think there is anything that can be done about this.
Look on the brighter side though, you don’t lose any data :-)
Hey man, great article, I’ve used this procedure numerous times to help friends with recovering phone numbers from smashed iPhones, making retraceable backups that wouldn’t restore their entire system software (don’t ask why), etc. Anyway, I just wanted to clarify that last part on exporting to .txt. Is there a specific DBMS that does that? I’m on SQLite Database Browser 1.3 for Mac OS X Leopard and it doesn’t seem to cooperate with that last command, could I just be doing it wrong? If you have any idea what’s going on, would be great to hear it straight from you.
Thx in advance…
@Vikesh: Is the SQLite database browser a GUI tool ? I’m asking because I didn’t use any GUI tools for this purpose. I opened the database using the sqlite3 command line utility. Just drop into Terminal.app and follow my instructions.
Hmm, this almost works for me, working in terminal in 10.5.5.
Neither the “.select” nor “,output” seem to perform any function. They just bring up a …> prompt.
Thanks for this article, BTW… it’s been very helpful.
@sktz: The prompt that you’re getting is because sqlite is expecting you to close the last statement. Did you give the full command as -
“output backup.txt”
(Without the quotes)
If that didn’t work, try adding a semi colon (;) at the end of the statement.
Or else, try googling for some gui browsers for sqlite3. I have one which works for windows:
http://sqlitebrowser.sourceforge.net/screenshots.html
Try this on a windows machine if it works for you.
Sharninder,
Thank a lot. I appreciate your help. It turns out the issue was, as you suggested, closing with a “;”
I’m still unable to get it outputted into a text file, but the results do come up in the terminal, and I can just copy-paste them.
This is extremely helpful. Hats off to you.
Thanks for the great article! I’ve been extracting SMS messages from an IPHONE backup, and am struggling to understand the DATE format. See sample below. I think the actual message date in this example is around 12/30/2009. Any idea of the exact format meaning?
DATE MESSAGE
1230221228 Hello
1230223075 What up
1230223125 Txting you on my new phone
1230223243 Sweet… How was your christmas?
1230223294 Merry Christmas girl!!! I got a new phone!!
Thanks much!!
1230223349 Delightful when can I see you
John,
I also haven’t been able to figure out the date format although, I admit I haven’t looked too hard. Since, I was mainly concerned with the data, once I had that, I didn’t bother with the dates. I’ll do some more research on this and if I find something interesting, I’ll update this post.
Those date numbers are “Unix time” i.e. seconds since 1970-01-01T00:00:00Z.
They range from 2008-12-25T16:07:08Z to 16:42:29 on the same day.
Unlikely to be December 30, 2009 because (1) that’s in the future (2) one doesn’t wish one’s gf “Merry Christmas” 5 days late and hope to retain the relationship :-)
HTH
John
Yes, I also thought that was Unix time, and converted some sample dates from my phone but couldn’t still get the actual date. I could figure out the date but the year was off by a couple of decades :-) I think I need to look at this again to get a fresh perspective.
Hmm, i’ve opened an sms.db file, but didn’t find there DELETED messages. Only same as on my iphone.
If the messages have been deleted from the phone, I don’t think there is any way to get them back.
@john: “i’ve opened an sms.db file, but didn’t find there DELETED messages. Only same as on my iphone.” Where did you get the sms.db from? opened it with what? what did you do to try to find deleted messages? what is the same as on your iPhone?
@Sharninder: depends on how they were deleted. I have helped dig out almost all of an address book db which was not backed up and whose contents had been thrown away by syncing it with an empty outlook db. If you can get the sqlite db off the phone and examine it with a hex editor and see lots of names and “c u l8r” etc etc then there is a good chance of dragging out most of the contents into a text file or into another db where you can play with sql and put it all back together.
To John Machin: I need a message that I’ve deleted 24 hours ago by “clear conversation”. I don’t have this message in last itunes backup, so my only chance is a new backup. I extracted OS files via “iPhone Backup Extractor” and found an sms.db file there. Than I’ve opened it via sqlitebrowser-1.3.
And I didn’t find there any deleted messages, only same messages which are currently available on iPhone. There are missed ID’s in the db table. So I suppose there’s no chance to recover deleted messages. Sorry for my English.
@john: sqlitebrowser makes no pretence of being able to show the remains (if any) of deleted database rows. Of course you would only see the same as you can see via the iPhone. Use a hex editor or a simple text editor or the Unix strings command — anything that does NOT understand sqlite structures and will thus show you ALL of what is in the file. Depending on what else has happened to the database after the sqlite deletion, you have a reasonable chance of seeing the contents of your deleted message there.
@John Machin: Aah ! I get it now. You’re using a hex editor to actually get text out of the db file. That might or might not work depending on how *old* the data is that one wants to recover, but yes that’s a good method to use if one is really desperate :-)
@Sharninder: No, I’m NOT “using a hex editor to actually get text out of the db file”. I’m talking about using a hex editor or somesuch to INSPECT the file to SEE if there is text there that is not visible when you use a program that interprets the file as a SQLite database. In john’s case, his one missing SMS message may be visible in the file, and that’s all he needs. For any quantity of data (like the several hundred contacts covering many columns and several tables that were recovered from the iPhone contacts db) attempting to recover it using a hex editor would be extremely futile.
@John Machin: Yes, I agree this technique is quite good for getting atleast something out of the db file. Recovering the full contents would be quite tedious.
@John Machin: Yeah, the first I did I opened .db file with text edit on mac. The whole text I found was same, nothing hided.
@john: Bad luck, it must have been over-written; SQLite is rather aggressive about using free space instead of extending the file — a very good idea in /normal/ operations.
@Sharninder: Recovering the full contents (more likely: /near/ full) of a TINY db would be extremely tedious if done with a hex editor. Have you ever looked into the SQLite file format (http://www.sqlite.org/fileformat.html) ? However it’s not tedious at all when you have a /program/ to do it.
Hi:
i backed up my sms from my iphone to a “text file” with phoneview (wrong idea)!
now, i have the 3.0 SW and i want to recover those messages!
ther is a way to do that??
thank yo very much!
I haven’t used phone view so I can’t help with that. I don’t think there is a way to directly recover sms messages from a text file to the iphone.
Hi, thanks for ur guide, it really helped!
Can you make a guide for calendar and text messages and notes? Thanks
Jeobob,
The last time I tried, I was more interested in the contacts so only worked on getting those. I’ll try write up a guide for recovering text messages and notes too later sometime.
@harninder: I have read your article but I don’t a mac. Could you please tell me which tool did you use on Windows? You said they didn’t work but I would like give them a try.
I’m so desperate to recover my contacts! really really.
Thanks for your time.
@Mirle: Try this link:
http://discussions.apple.com/thread.jspa?messageID=8614248
http://tech.kateva.org/2008/08/iphone-backups-are-stored-in.html
http://code.google.com/p/mobilesync-inspect/source/browse/trunk/USAGE.txt
Not sure if they’ll be of any help to you. Don’t you have any friend with access to a Mac ?
Hey!
you don’t necessarily need a mac, any kind of *nix is good and as various people already said, windows is good aswell. Well I was hoping for a gui but then again I finished recovering my data with sqlite3 as you nicely described on a linux centos server, and all is nice now :D yaay!
Thank you!
@Ciprian: Yes, any windows or unix computer is fine for reading the sqlite files. I used the mac to decode the binary plist file. If that can be done on windows, nothing like it.
i guess you can say i am a noob, but i want to get my contact list to and i dont really know much about how the decoder works.. could you help me alittle?
Just run the decoder with the name of the .mdbackup file and it’ll produce the sqlite files as output.
decode_backup… MdbackupFileName.mdbackup
You can use the iPhone Backup Extractor at http://reincubate.com/labs/iphonebe to extract this data — there’s a free version. Hope this helps.
I have updated my iphone in my friend’s computer.Now I am unable to get my old contacts telephone no. and notes.Here after I have done syncing several time s in my computer after that.Now can I get that contacts list from my computer again?can I restore contacts from 1 month old backup so I can get full contacts details?
Unless your friend has removed iTunes and the backups on his computer and/or uses another iPhone on his computer also, you should be able connect your iPhone to the other computer and just select restore from backup.
On your friend’s computer, go to Edit->Preferences and look under the Devices tab. This’ll show you the backups that are still available on the computer. If your backup is still there, you can restore to it. If it’s gone, then you can’d do much about it.
Thanks for the tip. I was to use sqlite3 command line utility in windows to access my contacts data from backup to a text file, following your steps above. The instructions were very clear once I read them :))
This is an amazing tutorial, thank you so much for sharing! I just have one questions – I am using SQlite browse & I am able to execute the query & see the contacts nicely but the problem is how do I export it? When I click on export it is only giving me the option of exporting the “AB Person” or “AB multi value” but I need to have it export the combination just the way your query is.
Pamela,
My article is useful for those who want to understand some of the inner workings of iPhone’s backup files. If you’re an end user only interested in getting your data out, I’d suggest that you take a look at the iPhone Backup Extractor application from reincubate.
If you can’t get it to work, let me know and I’ll try and help you.
Hey i’ve been looking all over for a solution to my creativity…I was @ a friend’s house out of town (windows Vista) and decided to update my iphone software ( i know…i should’ve known better!!!). Any way when i plugged the phone to update the software, it automatically backed up it. In the process of synching it failed and left my phone in recovery mode.
I had a previous backup on my Mac at home and had to restore my phone with that one meanwhile because it was on recovery mode and i need a functioning phone.
I know where the backup is stored on a vista pc, the question is if he can send it to me by email, cd, etc and can i copy it onto my MAC (library/app support/mobilesync/backup) and then restore my phone with it????
I don’t want to loose 1 year since my last backup . Please help…..
Hi Alex,
I’ve not really tried this but it might just work. The only way for you to find out is to actually try copying the backup to the backups folder and see if iTunes recognizes it. You’ve got nothing to loose. If iTunes recognizes the backup then it’ll work, or else you’ll have to get the data out of the files manually as described in my post.
Let me know if it works or not.
Yes, you can use a backup file from iTunes on any other PC: the iPhone Backup Extractor (iphonebackupextractor.com) will be able to read it on Mac or Windows.
I rescued the mysterious loss of some calendar appointments using the following steps:
(From an iPhone running OS 3.1.2)
* Ran iPhoneBackupExtractor on my iTunes backup file
* Extracted Library/Calendar/Calendar.sqlitedb
* Opened using free Firefox Addon SQL Lite Manager
* Browsed the Event Table
* Selected the records I wanted to recover, right-clicked and copied as CSV (Excel compatible)
* Pasted into MS Excel
Recovering the original dates and times was quite confusing. I thought the times should have been seconds since 1/1/1970, but it seems mine were seconds since 1/1/2001. So I created a new column with this formula:
=”1/1/2001″ + F1/60/60/24 + 11/24
This assumes your number is in cell F1 (e.g. 281577600). The middle part converts the seconds to days (required for Excel) and the last bit adds 11 hours (because I’m in Eastern Australia timezone). This should give you the original dates and times back. You can then import those dates into Outlook, or your other calendar app.
Hope that helps someone.
Simon.
Hello!
Great tutorial!
I was wondering…
Since i’m not the only one with this problem, maybe you could help a lot of people with that.
I have an iPhone 3GS with the new bootrom (tethered jailbreak) I rebooted it and now i can’t get it back to work with blackra1n. So im stuck with either an jailbreaked and unlocked 3.1.2 or i upgrade to 3.1.3 and wait what will come…
Anyway! I do have an backup of my entire phone. And since i didn’t backup my ECID SHSH i was wondering, if it’s possible to get those informations out of the backup?
My second question…
Is it possible to access the phone while it is in recovery mode? I want to get in and delete some files i installed, which may be the cause of my problem.
Thanks in advance for your help!
2 Trackbacks
[...] Recover your data from an iPhone backup [...]
[...] Once you have made copies of these files, I would suggest you use this fantastic program to extract the data from the backup files. Note: this program only runs on a Mac. If you are running Windows, it is a slightly complicated task that is best outlined in this blog post. [...]