I’m working on a tool, inspired by the need to wipe my iPhone but first backup my SMS/MMS text messages for retention purposes.
I looked around the internets and found some tools out there, but not all were free. Found some methods of doing this also through the command line with some trickery but I decided to just write a small .NET utility to handle this for me because I’m sure I’ll need to do this again.
You need to have a backup of your iPhone via iTunes on your computer or this tool will not work.
Here’s a screenshot. I plan to implement image attachments, emoticons and other features (like Contact Names instead of just phone numbers). Currently, it does what I need. I can right-click on a contact and export that entire contact’s text history to CSV, RTF, XLS, or TXT.
It’s not complicated how this works. There’s actually an SQLite database that is created and stored to the backup folder (location differs for each version of Windows). Through a bit of research I was able to figure out how to extract the data I needed.
If you’re interested in trying this out, let me know. I saw some different information across the ‘net regarding some of the columns in the tables. The version of iOS I tested this is on was 6.1 on an iPhone 3GS.
Some other tools out there that I used after I wrote this. These are geared more toward browsing the complete backups of the iTunes backup of your iPhone.
- Hash: online hash value calculator (used to calculate SHA-1 hashes of MediaDomain attachments for MMS text)
- iphonebackupbrowser
Additional Notes
3d0d7e5fb2ce288813306e4d4636395e047a3d28
– This is the SMS/MMS file (SQLite)
Possible Locations of iPhone Backup File:
%APPDATA%\Apple Computer\MobileSync\Backup
Windows XP
%APPDATA% = C:\Documents and Settings\USERNAME\Application Data
Windows Vista
%APPDATA% = C:\Users\USERNAME\AppData\Roaming
Windows 7
C:\Users\USERNAME\AppData\Roaming\Apple Computer\MobileSync\Backup
Windows 8
C:\Users\USERNAME\AppData\Roaming\Apple Computer\MobileSync\Backup
This snippet is from Linux Sleuthing blog… There are some interesting things that I was wondering that I learned why the case is from this site. The datetime is 31 years (or 978307200 seconds) because Mac Epoch is 1/1/2001 whereas Unix Epoch is 1/1/1970. Cool, eh?
SELECT m.rowid as RowID, DATETIME(date + 978307200, 'unixepoch', 'localtime') as Date, h.id as "Phone Number", m.service as Service, CASE is_from_me WHEN 0 THEN "Received" WHEN 1 THEN "Sent" ELSE "Unknown" END as Type, CASE WHEN date_read > 0 THEN DATETIME(date_read + 978307200, 'unixepoch') WHEN date_delivered > 0 THEN DATETIME(date_delivered + 978307200, 'unixepoch') ELSE NULL END as "Date Read/Sent", text as Text FROM message m, handle h WHERE h.rowid = m.handle_id ORDER BY m.rowid ASC;
Example of the above SQL:
Update June 2024
Here’s another query that works with iOS 6 and 17.
select
m.rowid
,coalesce(m.cache_roomnames, h.id) ThreadId
,m.is_from_me IsFromMe
,case when m.is_from_me = 1 then m.account
else h.id end as FromPhoneNumber
,case when m.is_from_me = 0 then m.account
else coalesce(h2.id, h.id) end as ToPhoneNumber
,m.service Service
/*,datetime(m.date + 978307200, 'unixepoch', 'localtime') as TextDate -- date stored as ticks since 2001-01-01 */
,datetime((m.date / 1000000000) + 978307200, 'unixepoch', 'localtime') as TextDate /* after iOS11 date needs to be / 1000000000 */
,m.text MessageText
,c.display_name RoomName
from
message as m
left join handle as h on m.handle_id = h.rowid
left join chat as c on m.cache_roomnames = c.room_name /* note: chat.room_name is not unique, this may cause one-to-many join */
left join chat_handle_join as ch on c.rowid = ch.chat_id
left join handle as h2 on ch.handle_id = h2.rowid
where
-- try to eliminate duplicates due to non-unique message.cache_roomnames/chat.room_name
(h2.service is null or m.service = h2.service)
order by
2 -- ThreadId
,m.date
Example output of above query:
