Extract iphone sms, notes and contacts from backup to Excel or CSV – SQLiteStudio

To extract SMS messages from an iPhone backup.

Backup iPhone using iTunes

Install SQLiteStudio (free) from sourceforge.

Open SQLiteStudio

In MacOS open folder /Users/USERNAME/Library/Application Support/MobileSync/Backup/RANDOM_NAME
in Windows 10 open folder C:\Users\USERNAME\AppData\Roaming\Apple Computer\MobileSync\Backup\RANDOM_NAME

the file you want is called "3d0d7e5fb2ce288813306e4d4636395e047a3d28".

Copy this file to another folder and rename it to something like "iphone_sms.sqlite" (making it ready for SQLite)

Open this file using the SQLite browser - on the left you should see the table names. Open the messages table to have a quick look.

Then Tools -> Open SQL Editor

SMS Messages

Paste in this SQL query and run it :

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;

You can then click the Export Results button and save them to a CSV!

Contacts

The file you want is "31bb7ba8914766d4ba40d6dfb6113c8b614be442" - this has your contacts in it.

This is the SQL query to extract your contacts details :

select ABPerson.prefix, ABPerson.first,ABPerson.last, c.value as MobilePhone, h.value as HomePhone, he.value as HomeEmail, w.value as WorkPhone, we.value as WorkEmail
 from ABPerson left outer join ABMultiValue c on c.record_id = ABPerson.ROWID and c.label = 1 and c.property= 3
 left outer join ABMultiValue h on h.record_id = ABPerson.ROWID and h.label = 2 and h.property = 3
 left outer join ABMultiValue he on he.record_id = ABPerson.ROWID and he.label = 2 and he.property = 4
 left outer join ABMultiValue w on w.record_id = ABPerson.ROWID and w.label = 4 and w.property = 3
 left outer join ABMultiValue we on we.record_id = ABPerson.ROWID and we.label = 4 and we.property = 4

Notes

Open the file "ca3bc056d4da0bbf88b5fb3be254f3b7147e639c" - this has your notes in it.

Run the query (this will allow you to export them to a CSV for OpenOffice or Excel) :

SELECT a.Z_PK,ZSTORE,strftime("%d/%m/%Y %H:%M:%S",ZCREATIONDATE+978307200,'unixepoch', 'localtime') as 'CREATIONTIME',
strftime("%d/%m/%Y %H:%M:%S",ZMODIFICATIONDATE+978307200,'unixepoch', 'localtime') as 'MODIFICATIONTIME',ZTITLE,ZSUMMARY,
replace(ZCONTENT,"<br>",char(13)) as CONTENT 
FROM ZNOTE AS a,ZNOTEBODY as b WHERE b.ZOWNER=a.Z_PK
ORDER BY ZMODIFICATIONDATE,ZTITLE DESC

Note: the timestamp on an iPhone runs from 1st Jan 2001 - hence the SQL above.

Export to a CSV file!

---

Big thanks to John Lehr for the SMS SQL query.

http://linuxsleuthing.blogspot.co.uk/2012/10/whos-texting-ios6-smsdb.html

Also thanks to 'muddywaters' from the MacRumours forums.

http://forums.macrumors.com/archive/index.php/t-1350341.html

Leave a Reply