Extracting / Recovering SMS Message from Itunes Back-up

Discussion in 'iPhone Tips, Help and Troubleshooting' started by mrrish, Mar 13, 2009.

Thread Status:
Not open for further replies.
  1. macrumors regular

    mrrish

    Joined:
    Feb 14, 2008
    #1
    Hello All,

    I am a mac user on 10.5.6 with an iPhone 3G. Recently, and by pure accident, I deleted about SMS 3,000 messages between me and my special someone. Is there anyway to recover the message by injecting it into an active backup file and performing a restore? If not, is there a way to extract it into a readable format (i.e. text file) so that I may record the memories?

    I appreciate any and all assistance with the matter. The backup files are located in "Users/*USERNAME*/Library/Application Support/MobileSync."

    Mucho thanks in advance!
     
  2. macrumors member

    Joined:
    Mar 9, 2009
  3. dZp
    macrumors 6502a

    dZp

    Joined:
    Mar 29, 2006
  4. thread starter macrumors regular

    mrrish

    Joined:
    Feb 14, 2008
    #4
  5. dZp
    macrumors 6502a

    dZp

    Joined:
    Mar 29, 2006
    #5
    You can open the .db file with SQLite Database Browser and export the data.

    If you're feeling brave, there's a free online solution: iPhone Online SMS Exporter. I never tried it myself.

    If you wanted to view/export the current SMS messages that are on your iPhone (not from backup), then PhoneView can do that.
     
  6. thread starter macrumors regular

    mrrish

    Joined:
    Feb 14, 2008
    #6
    dZp, you are great. Google failed me, but you did not! Here is what I did:

    1. I downloaded and installed iPhone / iPod Touch Back Up Extractor (see link above).
    2. Installed, and selected the appropriate backup date that I wanted to restore.
    3. Selected "Other files" and selected "export" and chose to place the files on my desktop.
    4. Of the exported files I went into the Library folder, SMS folder, and found "SMS.db."
    5. Downloaded and used SQLite Database Browser (see link above)
    6. In "Browse Data" tab, I selected "messages."
    7. Click File > export and exported everything it as .csv file.
    8. Opened Microsoft Excel
    9. File > Import
    10. Select Import CSV
    11. Between "delimited" and "fixed width" options, I chose delimited
    12. Click next to preview the way it will look.
    13. Clicked "finish" and ended up with a spreadsheet that had phone nunbers, coded dates, and the actual text message.

    This may sound confusing, so if anyone future needs help, please feel free to PM me! This all didn't cost me anything on top of already owning excel!

    Thanks again!
     
  7. macrumors 68000

    Mr. Giver '94

    Joined:
    Jun 2, 2008
    Location:
    London
  8. thread starter macrumors regular

    mrrish

    Joined:
    Feb 14, 2008
    #8
    This was all done on a mac. :) Just follow the instructions, if that doesn't work let me know.
     
  9. dZp
    macrumors 6502a

    dZp

    Joined:
    Mar 29, 2006
    #9
    Glad I could help. :cool:

    Shame on anyone who says:
    :p
     
  10. macrumors newbie

    Joined:
    Mar 20, 2009
    #10
    so can you put the SMS messages back on the phone?
     
  11. macrumors newbie

    Joined:
    Jul 31, 2009
    #11
    If you can extract the sms.db from your backup i would assume if you jailbreak your iphone you could ssh the sms.db onto the phone in place of your old one.

    I will be trying this in a few hours when i get home and will let you know how it goes.

    EDIT: IT WORKS!!!
     
  12. macrumors 6502a

    medicscott

    Joined:
    Aug 9, 2009
    Location:
    san diego
    #12
    so your saying...lets say i have a girlfriend...but gotta hot date. i would ssh my sms.db to my macbook, then continue to delete all my current sms on my iphone. so there is nothing if she decides to look...then when i get home i can just upload sms.db to my iphone and all is fine?
     
  13. dZp
    macrumors 6502a

    dZp

    Joined:
    Mar 29, 2006
    #13
    Yes

    Well, apart from the fact that you'd still be cheating on your girlfriend! :p
     
  14. macrumors newbie

    Joined:
    Dec 8, 2009
    #14
    ok so the $100,000,000 question is how the HELL do I get these files OFF my computer for good? is the only option getting a new hard drive and transferring? someone explained that deleting would be like just taking pretzels of a box of chex mix. thanks in advance
     
  15. macrumors newbie

    Joined:
    Apr 29, 2010
    #15
    SMS Recovery

    This looks good, however when I ran the program only apps showed up and not the sms's this mentions? How do I do this?

    The free download is also great, however it does not pull deleted texts, is this possible?

    Peter


     
  16. macrumors regular

    Joined:
    Jun 10, 2009
    #16
    Okay, so I was wondering something a little more complicated.

    I had to restore and for some reason, the backup was corrupted and I couldn't restore the old backup and had to set the phone up as a new one.

    Of course that lost me all the SMS messages, but I have an old backup which still has them on my PC, and I can SQLiteBrowser that file and see the messages.

    It should be possible to
    1) export into csv
    2) make a new backup to safe SMS since restore
    3) sqlitebrowse new backup
    3.1) export into csv
    4) combine the two csv files into one by appending the second to the first (while editing the indices)
    5) store as sms.db
    6) upload to phone

    Right?

    Worst case I should be stuck on having to restore from the recent backup and be back where I started, or am I having any wry ideas that are bound to leave me in a mess?

    Thanks in advance for opinions.
     
  17. macrumors P6

    -aggie-

    Joined:
    Jun 19, 2009
    Location:
    Where bunnies are welcome.
    #17
    It sounds like a reasonable approach to me, unless the backup has some way of seeing the .db file size/parameters and will think it’s corrupted if it isn’t the same. I’ve only used IBE and SDB for normal backups, so I can’t say for sure. Try it and report back, okay, since this will a lot of help for other users, if it works.
     
  18. macrumors newbie

    Joined:
    May 21, 2010
    #18
    I have tried this succesfully many times. Except diff path, on a PC. However....I can only view sms messages that are still currently in the phone. Can anyone advise on deleted sms. Is there another mddata file that I'm missing?
     
  19. macrumors P6

    -aggie-

    Joined:
    Jun 19, 2009
    Location:
    Where bunnies are welcome.
    #19
    Deleted SMS are gone forever, sorry.
     
  20. macrumors regular

    Joined:
    Jun 10, 2009
    #20
    Well, so far it didn't work. The SMS App started with the modified sms.db, but showed no messages whatsoever (neither old nor new) and when I went back and forth between contacts which it showed, it eventually crashed.

    Luckily, restoring the backup I made of the (only new) sms.db worked fine again.


    I did, however, only modify the message table, none of the others. I think this bears more looking into and more changing, possibly at least to add the required recipents of conversations, if not some hash values that I probably won't know how to modify.

    I'll keep trying stuff. I really don't get too many SMS, but I want those from before the crash back on my phone.
     
  21. macrumors regular

    Joined:
    Jun 10, 2009
    #21
    Wohoo, Success.

    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.
     
  22. macrumors newbie

    Joined:
    May 27, 2010
    #22
    Im confused here

    Hello, Im having some problems seeing ALL the SMS texts from a specific phone number.

    I accidentally deleted all my girlfriends SMS text conversations and I am trying to recover them. I did a backup with iTunes this morning (I'm under the impression that even though those messages were deleted, there kinda lingering in a state where I can recover them) and I followed the directions on iphonesmsexport.com to locate the .mddata and uploaded that file. I also purchased a code so I can see all SMS conversations. Now the issue Im having, is that I only see text messages leading to 07/10/2009 10:57:13.

    Why am I not able to see her messages up to the point I deleted the conversation? Am I uploading a older backup file? How can I be sure that I have the correct backup file?
     

    Attached Files:

  23. macrumors newbie

    Joined:
    Aug 17, 2010
    #23
    I don't know why I didn't do this earlier and just ask my question here (and just when I thought I had used up my 'stupid points' for the month -_-)

    What does the field CPRecord hold? Where does that value come from? I get that it's the 00CPRecord + 01CPRecord = new CPRecord for the new file combining two old sms.db's into one...but what does the value represent?

    Thanks and regards,

    -Exiku

    P.S. I'm also thinking that this might make an interesting programming project...
     
  24. macrumors 65816

    OldSchoolMacGuy

    Joined:
    Jul 10, 2008
    #24
    Not quite the case. You can delete the messages from your phone but if you have backed up the phone before the message was deleted, the message will still remain in the older backup files. I still have SMS messages from my first iPhone on the first day of the iPhone launch hanging around in the backups.
     
  25. macrumors 68040

    Watabou

    Joined:
    Feb 10, 2008
    Location:
    United States
    #25
    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?
     
Thread Status:
Not open for further replies.

Share This Page