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.

Read More »

Restoring SMS Backup from CSV to Blackberry devices

The tool in this article, CSV2IPD, reads text messages from CSV files and generates an IPD file that can be imported to Blackberry devices by Blackberry Desktop Software for devices running Blackberry OS 7 and older, or Blackberry Link for devices running Blackberry OS 10.

UPDATE (8 October 2014): I received a few comments from several readers stating that, although Blackberry Link recognized the IPD file as a legacy backup file and seemed to restore the messages successfully, no messages actually appeared on the device.
At first I suspected issues with the IPD file format generated by the CSV2IPD tool or some settings on the devices which delete old message, which turned out not to be the case.

After further research I found a hint here which pointed to a KB article by RIM describing the issue, KB34963 (SMS text messages from a BlackBerry smartphone running BlackBerry Device Software 4.5 to 5.0 or BlackBerry 6 to 7.1 are not migrated to the BlackBerry 10 smartphone). Although the English KB article has since been removed, I managed to locate a French version of the article, cached by Google. After reading the French KB, it seems that the problem is due to a Blackberry Link bug which results in all text messages not restored if an area code is not set on the OS 10 device. The solution to this is to set an area code, even if you do not use one, using the following steps:

1. On the Home screen, tap the Phone icon.
2. Slide your finger from top to bottom of the screen.
3. Select Settings > Smart Dialing.
4. Enter a three-digit area code (e.g. 214).
5. Select the Back button to save the changes.

With the area code set, the restore of the text messages should be successful and all messages in the IPD file should appear on the device. If you have a lot of messages, it may take a few minutes for the text messages application to update the message list - so be patient.

If the area code does not appear to be saved, select a country (e.g. United States) that is known to have different area codes for different cities. Blackberry OS 10 devices keep a list of countries that do not have area codes (e.g. Singapore) and will not save the area code settings if a country in the list is selected. After the SMS restore is completed, you can always set back the correct values.

With this workaround, all text messages from the IPD file generated by the CSV2IPD tool (and from the official Blackberry Desktop Software application) will be restored successfully on to the device. The format of the generated IPD file remains unchanged and compatible with Blackberry Desktop Software and other IPD utilities.

Take note that this issue only affects text messages restored from an IPD backup file while messages from a BBB backup file (newer format supported by Blackberry Link) are not affected. Hence the above workaround will not be necessary to restore messages from a backup file created by Blackberry Link, which is in the new BBB format, or for users using Blackberry OS 7 or older.

Downloads

CSV2IPD
Requires .NET Framework 3.5, which will be automatically installed on Windows 7 and above. If you are using Windows XP, you can download it here.

Change history

• 8 Feb 2015: Fix a bug where CSV2IPD would sometimes fail to generate the IPD file due to folder location issues. Fix some library issues and CSV2IPD now requires .NET framework 3.5 to run properly. Also apply some minor UI changes with better wording to make it easier to understand.
• 24 Mar 2012: Add text fields to allow users to specify a custom date format to be used for conversion. The format must be useable by .NET's DateTime.ParseExact function, refer to this for details.
• 20 Jan 2012: Better support for Unicode characters. Sample CSV files with Unicode messages in various languages are included in the download. CSV2IPD will also report the number of successfully imported messages at the end of the conversion, not just the number of invalid records.
• 18 Nov 2011: Use .NET DateTime class instead VB6 CDate function to parse date strings (which may fail on non-English systems). Also added a log file (CSV2IPD.log) that can be used for debugging. 
• 7 May 2011: Following a request from a reader, the program has been updated to handle CSV more robustly, supporting multi-line messages and messages having semicolons. It also reports the number of invalid records in the CSV file after the conversions. The source code and binary files have been updated.
• 1 Oct 2010: First release

Background

Following my previous post on a Windows Mobile tool called SMS2IPD that would run on a Windows Mobile device and creates and IPD backup of all the SMS that can be restored to a Blackberry device via Blackberry Desktop Manager, a German reader contacted me asking how he could do the same for his Nokia phone.

Being no Symbian expert who could write a similiar tool running on Symbian phone, I advised him to use Nokia PC Suite to create a CSV backup of all his SMS. After that, I have written a tool that will generate an IPD backup from the CSV files.

The tool, CSV2IPD

The program, called CSV2IPD, accepts 2 CSV input files for Incoming SMS and Outgoing SMS, as demonstrated in the following screenshot:

The CSV for Inbox looks like:

sms;deliver;"+987654321";"";"";"2010.08.25 15:14";"";"Message 1"
sms;deliver;"+123456789";"";"";"2010.08.25 15:15";"";"Message 2"
sms;deliver;"+111111111";"";"";"2010.08.25 15:17";"";"Message 3"

The CSV for Sent Items looks like:

sms;submit;"";"+0123456789";"";"2010.08.25 15:15";"";"Hello world 1."
sms;submit;"";"+0123456789";"";"2010.08.25 15:16";"";"Hello world 2."
sms;submit;"";"+0123456789";"";"2010.08.25 15:17";"";"Hello world 3."

If your exported CSV file via Nokia PC Suite does not look exactly as above, you can use a text editor tool such as Notepad++ then use Find/Replace, perhaps with the help of regular expressions, to modify it. Make sure you do not have extra quotation marks at the beginning and the end of each line. If the delimiter is comma and not semicolon, use CSVEd to change it.

Finally, if your time column is in a different format, try to change the Date Format string. The format should follow the date/time format used by .NET framework. Refer to this article for details on the specifications.

Take note that the generated IPD file has only been tested on Windows and may not work on the Mac version of Blackberry Desktop Software due to file format differences. Unicode text messages are supported, just make sure that your CSV files are saved using UTF8 encoding. As always, remember to backup all data on your phone prior to proceeding.

For those who are interested, MagicBerry is a good utility to explore, split, merge or extract information from the IPD backup file. ipddump is an open-source Java-based tool that can also do the same for IPD files. To extract data from the newer BBB file format generated by Blackberry Link, you will need a paid tool such as Blackberry Backup Extractor.

Read More »

Migrate SMS from Windows Mobile to Android

To migrate SMS from Windows Mobile to Android, you will need the SMS Exporter tool, which will export all SMS to an XML file that can be imported to Android using SMS Backup Restore

Same as my SMS2IPD tool that migrates SMS from Windows Mobile to Blackberry, SMS Exporter uses MAPIDotNet library found on codeproject, written by rwt33. The export speed on Windows Mobile is fast, but the import speed is very slow. On my HTC Dream flashed to Android 2.1, it took almost 2 hours to import 3000 messages - the process became slower towards the end and the phone would go to standby mode during the process. After the painful import , the message application is still responsive despite the huge message database.

SMS Exporter also supports backing up SMS to XML, which is useful if you want to read your SMS on a computer, or migrate your SMS to other phones.

Read More »

Transferring SMS messages from Windows Mobile to Blackberry devices

UPDATE (14 Feb 2015): Due to some outstanding issues with the SMS2IPD tool such as Unicode support and message extraction issues on some devices, which will most likely forever remain unfixed because I no longer have any Windows Mobile 6.x devices (which are no longer supported by Microsoft), I have decided to stop maintaining SMS2IPD and removed the download from this article.

For a more robust tool to restore text messages to Blackberry devices from a CSV (comma-separated values) backup, refer to my other tool, CSV2IPD.

The original article, written in 2010, is archived below for informational purposes only.

---

This may seem an easy task - one may immediately think of using the Device Switch Wizard in Blackberry Desktop Manager or do a quick search for some SMS backup/restore software which will do the job. However, while I was doing it, I came across some challenges which turns this into a difficult job:

1. The Device Switch Wizard found in the Blackberry Desktop Manager simply does not migrate the SMS messages.

2. There are plenty of applications that perform SMS backup/restore for Windows Mobile but there are none for Blackberry.

The first challenge is expected as many users simply do not need to migrate text messages to their new phones and it is therefore understandable that the Device Switch Wizard ignores SMS during the migration process but the second challenge disappoints me. I was realistic - I never expected to find a tool that transfers text messages on-the-fly, perhaps with the tool running on Windows, the Blackberry connected via Blackberry Desktop Manager and the Windows Mobile device connected via ActiveSync. BUT I was hoping to find some software that can restore Blackberry SMS from a backup, perhaps XML-formatted, which can be created by a tool running on Windows Mobile.

I was wrong - there are no such applications for Blackberry. This is explained by the fact that RIM does not provide an API for Blackberry applications to programmatically access the SMS messages store (there are, however, APIs to access email messages).

My approach

My ideas come from the fact that Blackberry Desktop Manager allows user to backup device memory (including contacts, calendar and SMS) into an IPD file, and restore from the IPD backup. Although the IPD file format is not documented by RIM, it is easy to reverse engineer (for example, by using a hex-comparison tool) due to its unencrypted and tokenized nature. There are previous attempts to do this, for example ipddump and MagicBerry. The latter is extremely useful as it allows you to view, edit, merge and split IPD files.

Keeping the above in mind, I came up with a tool that runs on Windows Mobile device and backs up all text messages into an IPD file. The file created can later be opened using Blackberry Desktop Manager and the text messages can be restored back onto the Blackberry device.

Final product: SMS2IPD

In the lack of a better name, let me call it SMS2IPD, short for SMS to IPD Converter. Here's how to use it:

1. Run the tool on your Windows Mobile device. You will need .NET Compact Framework 3.5 and above.

2. Select the correct time offset so that SMS timestamps will be correctly displayed. For example, if you are in GMT+8, select -8 for the time offset. The selection is needed as I am not sure which field to set to specify the timezone of the SMS messages.

3. Click on Start, choose the location to save the IPD file. Give the application some time to run, when completed, it will produce the IPD file.

4. Connect your Blackberry and start Desktop Manager, choose Backup/Restore, then click on Restore again. Select the IPD file created in step 1 and Desktop Manager will proceed to transfer all SMS to the device.

Note: this process may take a long time during which Desktop Manager may seem to hang. It will also overwrite all existing text messages on the Blackberry device, so back them up and merge them to the IPD first using MagicBerry. MagicBerry may also seem to hang when loading a large IPD file, so be patient.

Limitations and future improvements

I myself reverse engineer the IPD file format by using a hex editor, a hex comparison tool and MagicBerry, with some references to ipddump and MagicBerry source code, so there will be cases not handled properly. These include SMS with special characters, unicode SMS, and SMS sent from a timezone different from your device timezone.

Upon imported on the Blackberry device, all messages may appear as unread. You can mark all as read by scrolling to the top of the messages list (which displays the date), press the Blackberry key and choose Mark Prior Opened.

Some Blackberry devices by default will delete text messages older than 30 days. This behaviour can be changed by opening Options in Messages, choose General Options, and select a longer period for "Keep Messages".

I attached the source code for those interested to improve the application. You can modify and re-distribute the application and its source code as you like, but please mention that the source code was taken from my blog. You may not, however, use the source code and/or the application for any commercial purposes.

Credits

To read the SMS messages from a Windows Mobile device, I am using MAPIDotNet library found on codeproject, written by rwt33. The version I used was downloaded some time back, so it may differ significantly from the current version found in the project article.

Downloads

SMS2IPD CAB Installer for Pocket PC and Smartphone, Windows Mobile 5 and above with .NET Compact 3.5 (updated 10 Oct 2010) - removed as SMS2IPD is no longer maintained. See notice at the beginning of this article.

Read More »