How to save your SMS from your iPhone



The aim of this tutorial is to extract all SMS-Dialogues with a certain Person, to put it in a nice LaTeX file.
This includes Perl, SQLite-Database-Knowledge and some LaTeX stuff too.

So first question: where are the SMS stored in your backup? Of course you need first a backup of your iPhone. If you don't know, how to do this, go to an other page. The rest will be too difficult for you. First let's navigate to the folder of the iPhone-backup:

Commands in tcsh (Shell - Terminal)
1.
2.
3.
cd ~/Library/Application Support/MobileSync/Backup/
% cd means "change directory"
ls % translates to the command "list"

After the last command ls you will see a cryptic alphanumeric folder name, which is the UDID "Unique Device Identifier".
Now change into this folder and make a new ls | less command. You will see again many of these cryptic folder names. Only four files are found, whith "normal" names:
Info.plist
Manifest.mbdb
Manifest.plist
Status.plist

So where are all these SMS? Look for the file
3d0d7e5fb2ce288813306e4d4636395e047a3d28
and open it with a SQL Browser:

The easiest way to export the sms-dialog was the Firefox plugin SQLite Manager. To make things a bit easier, you have to export the SMS-File from your hidden backup-folder ~/Library/Application Support/MobileSync/Backup/[UDID] somewhere to your working directory in your ~/Documents folder like follows, and we will rename this file same time to sms.sqlite:

Commands in tcsh (Shell - Terminal)
1.
2.
cd /Library/Application Support/MobileSync/Backup/[UDID]
cp 3d0d7e5fb2ce288813306e4d4636395e047a3d28 ~/Documents/[sms-folder]/sms.sqlite

Then you have to find the right handle_id from the person of whom you want to save all SMS, your beloved darling or your special enemy ... There are some nice tools for Mac to open SQLite Databases: I used two of them: the SQLite Manager in Firefox (a plugin you need to install). And DB Browser for SQLite (the name in the /Applications it is called sqlitebrowser). In the Firefox plugin you click on message in the left scratch window and now it becomes tricky: you need the handle_id of the person of whom you want to save the SMS. You need to find one message, which is certain to be from him/her, of whom you want to extract the sms, to get the number of handle_id. Now you issue the following command into SQLite Manager:

Commands in SQLite Manager (Firefox Plugin)
1.
2.
3.
SELECT * FROM message
SELECT handle_id,is_from_me,text FROM message
SELECT date,is_from_me,text FROM message WHERE handle_id=[Your Number];

It is tradition to write the database commands in UPPER CASE, but it is working too in lower case. Click on "Execute SQL". Issue all commands from above into the case "Enter SQL" and click on "run SQL". Now you have all the dialogues you need. We have to export this query into a CVS (Comma V S) File. To export the search result click on "Actions" go down to "Save result (CSV) to File".