Backup your iphone SMS’s as a conversation transcript.

At the point in writing there aren’t many ways of backing up your SMS’s from your iPhone, but you do a system backup when you sync with itunes but what if you want your SMS conversions backed up as a simple non proprietary format? Well the answer is here!

Shea recently upgraded to an iPhone, and was having trouble with bluetoothing the data across from her old phone. She told me she had saved the most important SMS’s but was a shame to loose the record of our entire SMS communication history.
And she’s right, in today’s world where everything is digital a lot of important relationship related stuff was discussed and it would be a shame to loose it all. So I started googling, at first I thought it would be a feature of itunes.. i was wrong.. which is a shame. But it turns out people have done it before, and some applications where written to do just that, unfortunately though all report that they only work for the iphone OS version 2.0. Sure it would be alot easier if the phone was jail broken, but there must be an easier way.. and there is!

Step 1 – Extract the SMS database from one of your iPhone backups:

I came across this OSX app, I’m not sure if there is a windows equivalent but seeing as I sync my iPhone under OSX I don’t really care.
Anyway this app allows you to access one of your iPhone backups and extract parts of it. For this post we are only interested in the SMS’s so once you have chosen a backup from the list scroll to the bottom and extract “System Files” or “Other Files” (can’t remember the name will check when I get home).

You’ll be prompted for a location to extract to, I suggest you extract the contents to an empty folder.
Once the files have been extracted you should find a sms.db file under:

<extracted folder>/System Files/Library/SMS/sms.db


This sms.db turns out to be a sqlite file.. and for those in the know, know that this is good news! With a few lines of python we can access and extract what we need from the file, but first we need to find the structure, which leads us to step 2.

Step 2 – Determine the sms.db table internal table structure.

There are many sqlite applications, but I’ll point you to 2 of them. A OSX app and a Linux app.
For OSX there is  sqlitebrowser and for Linux I simply used sqliteman which to install is as simple as:
For Debian/Ubuntu:

apt-get install sqliteman

For Fedora:

yum install sqliteman

Now inside the sms.db file there turns out to be 5 tables:

_sqlitedatabaseproperties
group_member
message
msg_group
msg_peices

All actual SMS text are stored in the ‘message’ table, and as the conversion I needed to backup was a simple 1 on 1 conversation all I needed was to query this one table.
While we are here what’s the structure of the ‘message’ table, well there are 17 columns but the only ones that I required where address, date, text and flags.

• address – Is the number of the person you were having the SMS communication with.
• date – Date of the text in epoch format.
• text – The text itself.
• flags – numerical flags attached to the message, but just looking at the table I realised that if the flag field contained a 2 then the text was from the recipient, a 3 indicated it was send from you.

With all that information I was ready to write my simple script, which leads to step 3.

Step 3 – The basic script

This python script does need some work, I only wrote it as a once off, so adding more exception handling and passing in the main parameters into the script rather then using variables would be useful, but outside the scope.

It is also worth a mention that I am using python 2.6 and it does also require the sqlite module, under fedora it is as simple as:

yum install python-sqlite2

Note: Yes its the 2nd version of the python sqlite module, but is actually supports sqlite version 3, so inside python you ‘import sqlite3’ so it actually is the sqlite3 module.

Now for the script, don’t forget to change the <data place holders> with the data you require:

#!/usr/bin/env python                                                                                                                                        

import sqlite3
import time
import sys
import os
import codecs 

DEBUG = True
names = {'2' : "<Recipient>", '3': "<your self>"}
key = "<number>"               

SQL = "select flags, address, date, text from message where address = '%s'"

output = """%s - %s
        %s         

"""

def getDate(epoch):
        return time.strftime("%a, %d %b %Y %H:%M:%S",time.localtime(epoch))

def main(dbfile, outputfile):
        outFile = codecs.open(outputfile, encoding='utf-8', mode='w')

        conn = sqlite3.connect(dbfile)
        c = conn.cursor()
        c.execute(SQL % (key))

        count = 0
        firstDate = ""
        lastDate = ""

        for row in c:
                flags = str(row[0])
                for name in names.keys():
                        if name in flags:
                                user = names[name]
                date = getDate(row[2])
                text = unicode(row[3])

                outStr = output % (user, date, text)

                if DEBUG:
                        print outStr

                outFile.write(outStr)

                # Store the first Date
                if count == 0:
                        firstDate = date
                lastDate = date
                count += 1
        outStr = "Date Range: %s - %s" % (firstDate, lastDate)
        if DEBUG:
                print outStr

        outFile.write(outStr)
        outFile.close()

if __name__ == "__main__":
        if len(sys.argv) < 3:
                print "%s  " % (sys.argv[0])
                sys.exit(1)

        dbfile = sys.argv[1]
        outFile = sys.argv[2]

        if not os.path.exists(dbfile):
                print "%s doesn't exist" % (dbfile)
                sys.exit(1)

        main(dbfile, outFile)

This creates a transcript like:

Matt - Mon, 04 Feb 2010 08:01:38
This is a text message

Other Person - Mon, 04 Feb 2010 08:02:38
This is the response.

Anyway happy backing up!
Needless to say I believe Shea was happy 🙂