To extract SMS messages from an iPhone backup.
Backup iPhone using iTunes
Install SQLiteStudio (free) from sourceforge.
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
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!
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
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.
Also thanks to 'muddywaters' from the MacRumours forums.