Extracting / Recovering SMS Message from Itunes Back-up

[mrrish]

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!

[suburbiton]

SMSs aren't backed-up

[dZp]

iPhone /iPod Touch Backup Extractor
MobileSync Browser

[mrrish]

Originally Posted by dZp

iPhone /iPod Touch Backup Extractor
MobileSync Browser

Thank you, I have come across both these applications before . How do you use the extracted SMS.db file from Backup Extractor? Otherwise, I may end up purchasing the other one!

[dZp]

Originally Posted by mrrish

How do you use the extracted SMS.db file from Backup Extractor?

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.

[mrrish]

Originally Posted by dZp

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.

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!

[Mr. Giver '94]

How can I do this on a Mac?

[mrrish]

Originally Posted by Mr. Giver '94

How can I do this on a Mac?

This was all done on a mac. Just follow the instructions, if that doesn't work let me know.

[dZp]

Originally Posted by mrrish

dZp, you are great.

Glad I could help.

Shame on anyone who says:

SMSs aren't backed-up

[jmorale1]

so can you put the SMS messages back on the phone?

[tonev]

Originally Posted by jmorale1

so can you put the SMS messages back on the phone?

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!!!

[medicscott]

Originally Posted by tonev

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!!!

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?

[dZp]

Originally Posted by medicscott

then when i get home i can just upload sms.db to my iphone

Yes

Originally Posted by medicscott

and all is fine?

Well, apart from the fact that you'd still be cheating on your girlfriend!

[HelpMEZ]

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

[petergwenn]

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

Originally Posted by mrrish

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!

[foranor]

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.

[-aggie-]

Originally Posted by foranor

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.

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.

[montyburns]

Originally Posted by mrrish

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!

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?

[-aggie-]

Originally Posted by montyburns

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?

Deleted SMS are gone forever, sorry.

[foranor]

Originally Posted by -aggie-

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.

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.

[foranor]

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.

[bagumyan]

Originally Posted by mrrish

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!

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?

[Exiku]

Originally Posted by foranor

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 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...

[OldSchoolMacGuy]

Originally Posted by -aggie-

Deleted SMS are gone forever, sorry.

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.

[aeron1]

Try the decipher text message tool software. My wife bought it a few months ago to recover some text messages that she deleted. It worked great and was only like $5 or $6 bucks. http://decipher-media.com/iphone-tools/