Tuesday, December 8, 2009

Copy & Backup SMS, Address Book, Contacts from iPhone, iPod Touch To Your Computer !!!!

As always, struggled through this one too. Just couldn't figure out how to get all the data from iPhone to my PC and manage it or save it on my PC in case. After all the research and googling i found out that all this important information on iPhone or iPod Touch is stored as a SQLITE Database. So i found out where all these files were, got a SQLite Database Viewer and there you have it, your own data backup tool. Below are the steps for anyone who needs step by step details.
  1. Get iPhonebrowser from google code projects. Link is here iPhonebrowser
  2. You will have to Jailbreak your phone so user blackra1n
  3. This works on a non-jailbroken iPhone as well.
  4. If iPhonebrowser doesn't detect your phone as jailbroken, try the AFC2 fix mentioned here
  5. Download SQLite Database Viewer from Sourceforge Link
  6. Now connect your Jailbroken iPhone or iPod Touch to your Windows PC.
  7. Start iPhonebrowser and navigate to /var/mobile/Library directory.
  8. For SMS Database go to the SMS folder and copy sms.db to your PC
  9. For Address Book or your Contacts, go to AddressBook directory and copy AddressBook.sqlitedb to your PC
  10. Now open SQLite Database Viewer and open the sms.db file. Click on Browse Data Tab and change the Table Name to message. You will see all your messages in a tabular format.
  11. Select all the rows using Shift key and your mouse and click FILE > EXPORT > Table as CSV.
  12. This will save all the data in the table to a CSV file which you can open in XL.
  13. Similarly open the AddressBook.sqlitedb. This file is a little complicated and will require a little bit of SQL knowledge. All you need to do is write a SQL query to join ABMultiValue and ABPerson tables. A sample query is given below which you can execute on the EXECUTE SQL Tab and similarly Export the Data which the query returns. "select * from ABMultiValue"
  14. The other option is export the data from ABMultiValue and ABPerson tables as csv and open in XL and using XL macros generate a consolidated file for all your contacts.
This to me was the easiest way to backup and keep a PC copy of all your iPhone data and you don't need anyone's help. Hope this helps..

2 comments:

  1. Query to join contacts to phone numbers:

    SELECT ABPerson.First, ABPerson.Last, ABMultiValue.value FROM ABPerson, ABMultiValue WHERE (ABPerson.ROWID = ABMultiValue.record_id AND ABMultiValue.value LIKE '%(%)%')

    The LIKE condition strips out anything that isn't a typical phone number, since it will come back with junk like 'system:' and ''

    ReplyDelete