Extracting / Recovering SMS Message from Itunes Back-up

Watabou said:

I figured out how to take out the sms.db file and I can view it in Numbers.

What I want to know now is if I restored my phone and set it up as a new phone, can I inject the sms file into it?

Click to expand...

Only if you Jailbreak.

lucifiel said:

Only if you Jailbreak.

Click to expand...

Cool because I'm already jailbroken and on 4.0.1. What's the app called?

Watabou said:

Cool because I'm already jailbroken and on 4.0.1. What's the app called?

Click to expand...

No need for an app. You just need to have access to your iPhone's file structure (By OpenSSH) and copy the sms.db file to where you found it in the first place.

You can do this with something like Cyberduck.

lucifiel said:

No need for an app. You just need to have access to your iPhone's file structure (By OpenSSH) and copy the sms.db file to where you found it in the first place.

You can do this with something like Cyberduck.

Click to expand...

Oh okay. That's cool. I thought it would be more complicated. Thanks.

foranor said:

I can report success on returning OLD SMS (not deleted) to my iPhone.

Depending on your amount of old data, a lot of tedious editing could be necessary, but here's how I did it.

• I retrieved the SMS-bit from my iTunes/iPhone backup (with the date I wanted to go back to) [ Stored in 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mdbackup (or.mddata) ]
  
• I then used SQLiteBrowser to export the DB into sql from that backup into a textfile by the name sms.db_old.txt
  
• I repeated the step with the current SMS.db from my iphone into sms.db_new.txt
  
• What followed was comparing both text files in notepad++ and marking all the lines in sms.db_new.txt that I would have to change in order to fit with the message count and group ids for sms.db_old.txt
  Since I had a lot more messages in my backup than I received since my crash happened, this way around was easier. I would suggest to always edit the smaller file. Less chance for error.
  
  
• After noting all the differences, I deleted the lines that were equivalent from the sms.db_old.txt (for better visibility of what I would have to edit).
  
• Let me describe the changes I had to make:
  • There is a table called group_member. It is basically a list of phonenumbers in the order you got messages from them. Let's say you sent an sms from your phone to your best friend BOB after activating the phone, then BOBs' phone number would be #1.
    Afterwards you sent an SMS to your wife, her number is #2.
    If BOB replied now, he won't get to be #3, because his number is already in that table.
    
    Since it is unlikely that you got SMS from people in the same order now that you got them in the past, you will have to note the different indices in the table group_member because you will have to edit the messages themselves lateron to reflect that. Unless you want BOB to have sent you I love you messages.
    
    You don't have to change anything in group_member.
  • There is a table called _SqliteDatabaseProperties. It has some statistical stuff, like how many messages you got in total. You will have to do some basic math here, and edit this to be old+new.
  • There is a table called messages. These are actually your SMS messages.
    There are a couple of things you will have to change here.
    The table has these columns:
    [incrementing index] [phonenumber] [date] [text] [3 unimportant fields] [group_member_index]
  • Change incrementing index on those NEW messages when you add the lines to the file.
  • Make sure you locate [phonenumber] in group_member-table and if necessary, change [group_member_index] in message-table.
  • There is a table called sqlite_sequence. It will throw an error when you will later turn this into an sqlite3 database again. However, it has one line you must modify, and that is INSERT INTO sqlite_sequence VALUES('message',X);
    in which X is the amount of messages in total that you have.
  Now you have a funny text file full of SQL Statements, but how to turn this back into a usable sms.db file?
• I used SQLiteManager in a demo version. It allows to create a new DB and has a built-in SQL Statement manager, in which you can basically copy the entire text file.
  NOTE: In your text file with the SQL Statements, near the end, is one line : CREATE TABLE sqlite_sequence(name,seq);
  This is a technical table that is always present and cannot be created by your file, hence it will throw an error. Just remove it from your textfile before pasting.
• After you have executed all the statements, you have a sms.db file which you can simply ftp back onto your iphone. I didn't even have to reboot.

I'm pretty certain hardly anyone needs this, because, after all, who wants to get OLD SMS back onto their phone. To me, however, it was useful because I was really missing old conversations and am still far from any limits in my conversations. At least I haven't had any problems so far, that SMS were rejected.
If it helps anyone else though, I'm happy to have helped. If you have questions, send me a PM, I'll try to reply ASAP.

Click to expand...

I have merged my SMS.db files how do I get them back on my phone so tht when I restore it they will
Show up. What happens with MMS?.. Do I need to have my phone jail broken? Also would this work with contacts ? Thnks in advance

How to make sense out of the dates?

Hey everyone, first of all thanks for all the useful info about how to get back old SMS/texts. I learn a little more every time I come here I've gotten my info extracted and am slowly sifting my way through it for what I want.

But one thing that is baffling me as I am reading through this massive amount of data, how the heck am I supposed to read the dates? They come out looking like: 1307572355 and 1307627662 and so forth. What the heck format is that? Star date? Should Captain Pickard be reading this for me? LMAO...

Forgive me if this is a dumb noob question. I'm smart by no means a mac guru. Could someone tell me how to translate these kind of dates into normal form I can understand?

texasmccoy said:

But one thing that is baffling me as I am reading through this massive amount of data, how the heck am I supposed to read the dates? They come out looking like: 1307572355 and 1307627662 and so forth. What the heck format is that? Star date? Should Captain Pickard be reading this for me? LMAO...

Forgive me if this is a dumb noob question. I'm smart by no means a mac guru. Could someone tell me how to translate these kind of dates into normal form I can understand?

Click to expand...

I know it is an old thread, but actually after learning a lot from these forums, it is time to participate, this is my first post BTW

what you see as 1307572355 and 1307627662 is a unix timestamp converters are available here http://www.silisoftware.com/tools/date.php and here http://www.epochconverter.com

Hi, I need to wipe my phone and start fresh but I'd like to restore my SMS/imessages.
What's the most current way to do it (if it's even possible)?