Thing 27: Extracting your SMS from your iPhones

Remember in the good old days of Nokia's Symbian Phones where you could easily extract all the messages received on your phone.  Sometimes that can be pretty handy when you need details or evidence of certain messages received or sent.

Apple clearly didn't bother with those details in iTunes but some clever folks have shared ways to access your phone's SMS database.

Here's how I did it with their help

Why do it?

1. To count the messages you have received/sent for a specific period from all or specific individuals
2. Extract these records to be stored away in ASCII Text files or comma delimited files for documentation purposes
3. You can easily clean up the information above by a specific date ranges or mobile numbers.

How to do it?

Before you begin, backup your phone data into your PC using iTunes

1. Locate the backup files (Try this location C:\Documents and Settings\Your Name\Application Data\Apple Computer\MobileSync\Backup

2. Rename this file "3d0d7e5fb2ce288813306e4d4636395e047a3d28" to a shorter name e.g. "sms.db3"

 3. Start SQLite2009Pro

 4. Open the "sms.db3" file we saved earlier

5. Ignore the error messages

6. Now you can use SQLite 2009 Pro to extract records using the commands below

SELECT address, flags, datetime(date, 'unixepoch','localtime'), text
FROM [message]
order by date asc

Do note that the definition of "flags" (3= Messages Sent. 2= Messages Received)

7. click the Export button (Look just above the "Query #1| LUA")
8. Select Save as CSV
9. Click on the open folder icon
10. Enter a filename

11. Add this part to the earlier query if you want to find all messages sent after a certain date (e.g. 1st of May):

         where datetime(date, 'unixepoch','localtime') > '2012-05-01' and flags=3