Advertisements

How to read and export iPhone SMS text messages on Windows

First, you want to do a local backup so that you have the SMS database on your computer.

Open iTunes, and select your phone from the list at the left, such as mine appears below:

Next, under the summary tab, make sure the backup option is selected to “Back up to this computer” and encrypt backups is turned off, like so:

Now, perform a backup of your iPhone.

Here’s an updated screenshot from iTunes 11.0.2.26.

iTunesBackup_11.0.2.26

Second, download a SQLite editor so that you can open the database.

I used SQLiteSpy, available here:

http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index

There are other SQLite editors, but this one was the one I was able to get to work.

Next, open the database

Click Start > Run, and paste the following line to open the backup location.

%APPDATA%Apple ComputerMobileSyncBackup

You should see one directory for each phone you have synced to your iTunes.

When you open this directory, and then open the Snapshot directory within it, you should see a file named

3d0d7e5fb2ce288813306e4d4636395e047a3d28

This is your SMS backup database.Open it in SQLiteSpy (or your editor). The rest of these steps apply to SQLiteSpy. If you’re using a different viewer, adjust for yours.

Select File > Open and select the file. You should see the list of tables appear in the left pane.

Now, paste the following SQL query in the top right pane, which will query the database and return correct date/time stamps as well as the destination phone number:

SELECT datetime(message.date, 'unixepoch', '+31 years', '-6 hours'), handle.id, message.text FROM message, handle WHERE message.handle_id = handle.ROWID;

This will return the correct date and time (you may have to edit the ‘-6 hours’ statement to reflect your local timezone) as well as the other phone number and body of the text message.

Reference image:

sqlitespy_1

Now, click Execute > Execute SQL (or press F9) to run it. Your results will be displayed in the bottom right pane.

Tested on an Apple iPhone 4S and iOS 6.0.1

Comments and feedback are welcome.

Advertisements

, , , , , , ,

  1. #1 by Nancy Keating on November 20, 2012 - 8:22 pm

    Will this also work on a MAC platform? Also, my iTunes doesn’t list Devices to the left???

  2. #2 by Sybren on November 29, 2012 - 1:01 pm

    Thanks, for this info.

  3. #3 by Catalin on February 22, 2013 - 12:05 pm

    Great post.
    I was trying this with SQLite Administrator and other sqlite managers, but Spy was the only one to work.

    Thanks!

  4. #4 by James on February 25, 2013 - 10:30 am

    Great, this worked a treat. Thank you.

  5. #5 by Ja on September 3, 2013 - 9:26 pm

    finally.this work.thanks man.

  6. #6 by Philoo on April 16, 2014 - 5:43 am

    Great Job. I Really Thank You !