Posts Tagged SQLite
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 22.214.171.124.
Second, download a SQLite editor so that you can open the database.
I used SQLiteSpy, available here:
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.
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
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.
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.