Raw access to SMS/MMS database on Android phones

0.00 avg. rating (0% score) - 0 votes

My HTC HD2 running on Android from SD card has stopped working recently – the phone simply refused to power up while the battery was still fine. There was nothing I could do except to migrate to a new phone and re-sync everything from my Google account. Restoring the SMS, however, was not an easy task as my latest backup using Sms Backup and Restore is more than 3 months old.

Fortunately my Android was running from SD card, which was still working fine, so I could still access the entire Android folder on the card. Using various Windows tools (see my previous article) such as Ext2Explore, I was able to read the contents of data.img and found the location of the database storing all text messages, among other things, at /data/data/com.android.providers.telephony/databases/mmssms.db. The database is apparently in SQLite format.

Database schema

Next comes the challenge of reading the database. For this I used SQLite Database Browser, and all the tables are listed nicely:

UPDATE: As of end 2014, it seems that SQLite Database Browser has some issues handing the mmssms.db file taken from some devices and will just show a blank database. A better tool to use for this purpose is RazorSQL, which supports SQLite and should be able to open the SMS database without issues.

Reading SMS messages

Opening the SMS table and I can see all messages:

(phone numbers and message bodies have been removed by me when taking the screenshot)

The address column stores the sender/recipient of the message. Column body stores the message body (for MMS/SMS) while subject is for MMS only. The type column specifies the message type (sent, received) and can be 1 or 2. Interestingly, the service centre that sent the message, or SMSC, is also stored but not visible from the stock Messaging application. The time of the message is stored as a Unix timestamp format. To convert it to a readable date in Microsoft Excel, use the following formula, adapted from this blog, and format the cell as Date:

=(((TIMESTAMP/1000/60)/60)/24)+DATE(1970,1,1)

If you want to migrate the SMS to a new Android device, you may be able to do it by simply copying the mmssms.db database to the other device. If not, you can export the messages to a CSV file using File->Export Table as CSV File and import them to Android, Blackberry (see my CSV2IPD tool) or other platforms.

0.00 avg. rating (0% score) - 0 votes
ToughDev

ToughDev

A tough developer who likes to work on just about anything, from software development to electronics, and share his knowledge with the rest of the world.

13 thoughts on “Raw access to SMS/MMS database on Android phones

  • December 4, 2012 at 2:42 am
    Permalink

    could someone please confirm: the folder /data/data/com.android.providers.telephony/databases/ can only be accessed on a rooted phone? Thanks in advance for the answer, tom.

    Reply
  • December 9, 2012 at 1:21 am
    Permalink

    @Anonymous
    Yes, only rooted phones. If you want to backup your SMS/MMS unrooted, try an app like Go Backup.

    @MD
    Could you link me more information about how you do this on your phone: "my Android was running from SD card". I'm curious of the method you use.

    Reply
  • February 16, 2013 at 9:11 pm
    Permalink

    @MD Can you please publish the list of data types of all columns (or the table of definition) of the sms table. I need it for some android app that i'm writing and somehow i cant get the db file.. :(

    Reply
  • February 16, 2013 at 9:13 pm
    Permalink

    Hi,

    Your phone needs to be rooted for it to work. Once rooted and you can access the db, the columns data type can be select from the sql browser.

    Reply
  • February 19, 2013 at 1:06 pm
    Permalink

    @MD,
    I understand that. i was hoping if you can send me the table structure i wont have to go through all the trouble, besides i dont want to root my phone yet as its still under warranty.

    PS or you can share an empty sms db file..

    Reply
  • February 19, 2013 at 1:39 pm
    Permalink

    @MD, sorry to bother you. I've got the db file using ADT file explorer. Thanx anyways.

    Reply
  • April 3, 2013 at 8:40 am
    Permalink

    ummm i can't see any sms data when i try this. Does anyone know why?

    Reply
  • May 16, 2013 at 3:02 pm
    Permalink

    i also can't see any sms data when i try this. my phone has been rooted

    Reply
  • November 12, 2014 at 7:09 pm
    Permalink

    For the date, this formula made mine more accurate.
    =(((TIMESTAMP/1000/60)/60+GMT)/24)+DATE(1970,1,1)

    Not sure if this works for everyone, but the +GMT thing made my date and time more precise.
    Hope this helps somebody.

    Reply
  • November 13, 2014 at 2:05 pm
    Permalink

    Hi Mycael,

    Thanks for sharing :)

    Reply
  • December 1, 2014 at 5:51 am
    Permalink

    Hi,

    I'm trying to convert the time from the unix timestamp with no luck.
    When I input the code it just hashes my entire db so I guess I'm doing it wrong – Which column is it and where do I input the formula?

    Reply
  • December 1, 2014 at 12:06 pm
    Permalink

    Hi,

    The formula above is to be used when the timestamp is stored in a cell in Microsoft Excel. You will create another cell with the above formula and format it as date. It will show the correct date and time. This assumes that you exported the data to a CSV file and open it in Microsoft Excel.

    I can provide further advice if you can share what you are trying to achieve. What do you mean by "it hashes my entire db"?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>