Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

Extracting / Recovering SMS Message from Itunes Back-up

Status
Not open for further replies.
mrrish

mrrish

macrumors regular
Original poster
Feb 14, 2008
203
0
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!
 
mrrish

mrrish

macrumors regular
Original poster
Feb 14, 2008
203
0
dZp said:
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.
Click to expand...

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

tonev

macrumors newbie
Jul 31, 2009
3
0
jmorale1 said:
so can you put the SMS messages back on the phone?
Click to expand...
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

medicscott

macrumors 6502a
Aug 9, 2009
690
0
san diego
tonev said:
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!!!
Click to expand...

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?
 
H

HelpMEZ

macrumors newbie
Dec 8, 2009
1
0
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
 
P

petergwenn

macrumors newbie
Apr 29, 2010
1
0
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


mrrish said:
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!
Click to expand...
 
F

foranor

macrumors regular
Jun 10, 2009
111
0
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-

-aggie-

macrumors P6
Jun 19, 2009
16,793
51
Where bunnies are welcome.
foranor said:
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.
Click to expand...

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

montyburns

macrumors newbie
May 21, 2010
2
0
mrrish said:
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!
Click to expand...

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?
 
F

foranor

macrumors regular
Jun 10, 2009
111
0
-aggie- said:
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.
Click to expand...

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

foranor

macrumors regular
Jun 10, 2009
111
0
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.
 
B

bagumyan

macrumors newbie
May 27, 2010
1
0
Im confused here

mrrish said:
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!
Click to expand...

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?
 

Attachments

  • Screen shot 2010-05-27 at 2.39.15 PM.JPG
    Screen shot 2010-05-27 at 2.39.15 PM.JPG
    472 KB · Views: 2,314
E

Exiku

macrumors newbie
Aug 17, 2010
4
0
foranor said:
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.
Click to expand...

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

OldSchoolMacGuy

Suspended
Jul 10, 2008
4,197
9,050
-aggie- said:
Deleted SMS are gone forever, sorry.
Click to expand...

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

Watabou

macrumors 68040
Feb 10, 2008
3,425
755
United States
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?
 
Status
Not open for further replies.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom