Raw access to SMS/MMS database on Android phones
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.
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.
@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.
Hi Sepero,
The phone mentioned is an HTC HD2, which is originally a Windows Mobile phone. There are many methods to run Android on this phone, either by flashing a NAND image of the Android OS, or by running from the SD card and use a bootloader started from Windows Mobile to load the Android OS. As an example, see this http://www.addictivetips.com/mobile/how-to-boot-android-from-sd-card-of-htc-hd2-with-magldr/
@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..![:(]( "Sad")
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.
@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..
@MD, sorry to bother you. I've got the db file using ADT file explorer. Thanx anyways.
ummm i can't see any sms data when i try this. Does anyone know why?
i also can't see any sms data when i try this. my phone has been rooted
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.
Hi Mycael,
Thanks for sharing![:)]( "Smile")
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?
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"?