How to Backup iPhone SMS Messages for Free (No Jailbreak Needed)

Posted by on Nov 5, 2009 in Geek, How To, iPhone | 102 Comments

Recently, I did a full restore on my iPhone to try and fix some of the many problems I’ve been having (we’ll save that list for another day). While SMS messages are generally temporary in nature, I decided to find out if I could export them somehow. There are a few programs out there that claim to be able to do this for you, such as MobileSyncBrowser (Mac/PC, free trial/$10/$20), iPhone / iPod Touch Backup Extractor (Mac, free), or an online tool like iphone backup sms extractor. I didn’t try any of these because I don’t want to pay, I am not on a Mac, and I don’t want to upload my private messages to some random site. Luckily, I discovered that it’s not too hard to do it yourself. Grab your iPhone, put on your techie hat, and follow the 3 easy steps after the break!

Step 0 – Before You Start:

A couple things you need to take care of before diving in to the tutorial:

  • Make sure to do a sync/backup of your iPhone in iTunes before starting to ensure all messages will be extracted.
  • Download SQLite Database Browser as you will need this to browse/export the database once finding it.

Step 1 – Find the Backup File:

The SMS messages are stored in an SQLite database file that is located in a backup folder somewhere on your computer. The file is (most likely) named

3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata

On Windows, the path to the folder should resemble something like the following:

C:Documents and Settings[username]Application DataApple ComputerMobileSyncBackup[some random id]

On a Mac, you should find it at:

home/Library/MobileSync/backup/[some random id]/

Or just search your computer for 3d0d7e5fb2ce288813306e4d4636395e047a3d28.

Once you find the file, you can verify that it is an SQLite database by opening it in a text editor and making sure the first characters of the file are “SQLite format 3″.

Just to be safe and, copy the file to your desktop or another folder and rename it something shorter like sms.sqlite.

Step 2 – Browsing the SMS SQLite Database

You will need the SQLite Database Browser as mentioned above, so go download it now if you haven’t already!

Once you have it downloaded, extract the program and run it! Click File > Open and navigate to where you saved the copy of the backup file. When you open this file you will see an interface that should be familiar if you’ve ever used any sort of database management tool.

From the Table drop-down menu, choose the message table. This is the main table we’re interested in and you don’t really need to use the other tables at all but go ahead and take a look if you’re interested!

There are a few fields that we are interested in:

  • address: This holds the phone number of the person that sent you or you sent the message to.
  • date: This is a Unix timestamp of when the message was sent.
  • text: The actual message.
  • flags: This should be either 2 or 3. The messages flagged 3 are messages that you sent (outgoing), while the messages flagged 2 are incoming messages.

Step 3 – Exporting:

Still using SQLite Database Browser program, click File > Export > Table as CSV File. Choose the messages table and click Export. Choose a filename like sms.csv, hit Save, and you’re done!

Now you can open the CSV using Excel (or equivalent) and laugh/cry/look at all the funny/angry/crazy messages you have sent and received over the years/weeks/days!

To convert the datestamp to a human readable format in Excel, use the following formula (from SpreadsheetPage.com):

=(((A1/60)/60)/24)+DATE(1970,1,1)+(-5/24)

where A1 is the cell holding the datestamp, and -5 is your timezone GMT offset.

You can also duplicate (copy/paste) the address column and then do a find and replace of the phone number with the person’s actual name (useful at least for the people you text the most with).

Notes:

This will not export emoticons/emoji/MMS messages. There may be a way to grab these (table msg_pieces may give some hints), but I’m not really worried about losing this info.

You will only get the phone numbers of the messages, not the names (though it’s easy enough to do a find and replace for your most used phone numbers).

That’s it! I hope this will be useful to someone, and if you have any questions or comments, or find any errors in this post just leave a comment!

  • C. Royal

    Hello, and thanks for presenting this tip! Unfortunately, it did not work for me… SQLite Database Browser shows error message “File is not SQLite 3 Database”. Am I doing something wrong???

  • C. Royal

    Hello, and thanks for presenting this tip! Unfortunately, it did not work for me… SQLite Database Browser shows error message “File is not SQLite 3 Database”. Am I doing something wrong???

    • Matthew Ruten

      Hey,

      A couple things to check:
      – Are you sure the file you are using is “3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata”? Make sure your file has the .mddata file extension as there is another file with the same name but different extension. Check closely to make sure you have the correct file, and this will probably solve your problem!
      – Second, in iTunes, do you have the “Encrypt my iPhone backup..” option checked? If so, you won’t be able to access your SMS messages as described in the post. You could uncheck this option, backup your phone again, and then you should have an unencrypted version.

      Hope this helps! Let me know how it goes…

      Matt

  • Matthew Ruten

    Hey,

    A couple things to check:
    – Are you sure the file you are using is “3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata”? Make sure your file has the .mddata file extension as there is another file with the same name but different extension. Check closely to make sure you have the correct file, and this will probably solve your problem!
    – Second, in iTunes, do you have the “Encrypt my iPhone backup..” option checked? If so, you won't be able to access your SMS messages as described in the post. You could uncheck this option, backup your phone again, and then you should have an unencrypted version.

    Hope this helps! Let me know how it goes…

    Matt

  • Tim

    Thanks for your help on this. Question: The date column in my .csv file lists a string of numbers instead of dates — is there a way to convert these? Thanks.

  • Tim

    Thanks for your help on this. Question: The date column in my .csv file lists a string of numbers instead of dates — is there a way to convert these? Thanks.

  • Matthew Ruten

    Hey,

    Check Step 3 of the tutorial for a way to convert the timestamps into a more readable format (using Excel):

    “To convert the datestamp to a human readable format in Excel, use the following formula:
    =(((A1/60)/60)/24)+DATE(1970,1,1)+(-5/24)
    where A1 is the cell holding the datestamp, and -5 is your timezone GMT offset.”

    Hope that helps.

    Matt

  • Gab

    Everything works perfectly, except when i open my excel document, all the information appears in the first column… what am i doing wrong?

  • Gab

    Everything works perfectly, except when i open my excel document, all the information appears in the first column… what am i doing wrong?

  • melissjs

    I am having the same issue as C. Royal ;( Did you find a fix? I really appreciate any advice!

  • Pekka Riihonen

    Hello!

    This works very well, except that all the data gets sorted into a single column, with commas and speechmarksk as the separators of the data per SMS. Am i doing something wrong? The .csv file appears to just work as a text file without tabular format.

    Thank you!
    -Pekka

  • Matthew Ruten

    You may have to use Excel's “Text to Columns” feature. Try that and
    let me know if it helps!

    If you're still having problems, you could send me a few lines of your
    csv file. Just make sure you don't include any texts with private info!

  • Pearl

    This works great, got all the info. The one problem I'm having is the date conversion. It doesn't seem to be working. It coverts the date field, but not into a human readable date. Any help appreciated! :-)

    Thanks so much for posting this! :-)

    • Pearl

      Nevermind! I had to covert the row to a date field and it worked great! Thanks so much! You rock! :-)

  • Pearl

    Nevermind! I had to covert the row to a date field and it worked great! Thanks so much! You rock! :-)

  • Pingback: Tweets that mention How to Backup iPhone SMS Messages for Free (No Jailbreak Needed) | Mighty Matty -- Topsy.com

  • Matthew Ruten

    Great! Glad you got it working.

  • Arnoz

    Is it possible to then reimport messages on a freshly restored iPhone? I’m looking to do a full restore but would like to backup SMS only.

  • Arnoz

    Is it possible to then reimport messages on a freshly restored iPhone? I'm looking to do a full restore but would like to backup SMS only.

  • Matthew Ruten

    I'm not sure if this is possible, and unfortunately can't test it at the moment.

    Though I would guess that this may be possible:
    – Save the 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata and 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mdinfo files from your phone before restoring.
    – Restore the phone, and backup at least once (so that the new backup folder will be created).
    – Next, copy the above two files into the new backup directory and then do a “Restore from Backup…”; you should be left with a restored phone but with your old SMS messages.

    If you do try this out and it works, let me know! Make a copy of your original backup just in case something goes wrong.

    Matt

  • Arnoz

    Thanks! Should be able to try out that over the weekend, I'll keep you posted

  • Arnoz

    Unfortunately the backup process in iOS 4 has been changed so I can't simply replace the files.
    I'll check more thoroughly in a few days…

  • Parker

    Just got the iPhone4. I want to keep the old texts but essentially start anew with everything else (since I was jailbroken before). If you figure it out please post here! I am very interested.

  • bottle

    when Itey to open the file its saying that that an error has occurred: file is not a sqlite 3 database

  • bottle

    when I try to open……(sorry)

  • Donut

    Donut here, Hi matthew
    backed up, downloaded sqlite, and can't find the file 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata or anything resembling this.

    got some important texts i need to save, i am jailbroken but not housebroken

    Regards, Donut

  • Matthew Ruten

    Did you try doing a file search on your computer?

    Also, are you using “encrypted backups” (you can check to see if you have this checked in iTunes)? If so, you will need to turn off encrypted backups and do a non-encrypted backup to be able to extract your texts using this method.

  • donut

    Yes done a file search already, encrypts in itunes was already turned off.
    Whilst waiting for you very swift reply, i went the open ssh way, and winscp. I'm in, but no idea what to do next as im a noob. I just wanna get these messages copied and viewable in some way. Cheers

  • Matthew Ruten

    Are you on a Mac? If so, this program should work:
    http://supercrazyawesome.com/

  • Game_freak_418

    it works well, but at the end of the excel document, i seem to run out of my more recent messages, is there a limit on how much it can open?

    • Matthew Ruten

      Some versions of Excel have a row limit of ~65,000, but most people won’t even get close to that many messages!

      A more likely explanation is the file you are extracting from is an older version. You should sync your phone and let it complete the whole process, especially the backup part. Once you’ve synced it, follow the steps above again and you should have the latest messages (up till the point that you synced)!

  • Jr42

    how about export and import into new phone ?

  • Josh

    I had a hell of a time until I realized there are TWO files with the same name. Nothing was working for me because I was using the wrong one.

    Also, Spotlight might not find the file… You’ll need to navigate through the folder directories by hand to find the right one (the right one for me was 1.9MB. The wrong one was 50k). And there were no extensions, so ignore that. (iPhone 4 32GB iOS 4.2.1 Snow Leopard 10.6.6 iTunes 10.1.2 (17) – 2-11-2011.)

  • Chelsea

    No matter how many times I go through this process, I am never able to get an XSL or .sqlite file with messages on it. I have opened 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata with sqlite browser after saving it as a .sqlite file on my desktop. Then I open that .sqlite file using the SQLite Browser, but in the browser it seems like there is no data at all. Even after saving as a .csv and exporting to Excel, the only thing that shows up is each column having titles like “Date,” “Address,” “message” etc. etc. When I open up 3d0d7e5fb2ce288813306e4d4636395e047a3d28 as a .txt file, I can search through and find each of my text messages, separated by gibberish interspersed throughout the file.

    I am running Mac OS X Leopard and have an iPhone 3GS with MMS messaging enabled. Is the MMS the problem?

  • Anja

    I recommend checking out http://iphone-backup.net if you want all of this automated!

    • GizaDog

      Yeah for a cost!

  • Bill Rockers

    There are so many people looking for an easy way to do this across the internet, and this is the answer. I seriously just accessed my text messages in less than five minutes, and I used to think that this was an impossible task without paying or jailbreaking. Excellent. Search for a file, download a freeware browser, open a file and browse. Matthew Ruten you are brilliant.

  • Kelldel

    Thank you so much for this article! Followed each step and worked perfectly :-)

  • Regine Tan

    Thanks for your step-by-step solutions, it is really clear. However, I have a problem converting the time column in the excel file to one that is viewable.. Now it is ######## for the dates. I tried typing in the formula you provided but it does not work for me. 
    Any solutions for this? Thanks so much for your time in advance. :)

  • Sam

    First of all, thanks a lot Matt.  I tried this and it worked perfectly with just one problem – the only SMS messages that appear in the SQ-lite database are either from or too my wife.  No other messages either deleted from the phone or in the PC backup can be seen and these are the ones I had hoped to be able to look at.  Do you know anyway that I can look at sms messages from other phone numbers.
     
    Thanks a lot

  • Gd

    is there any way to tell if the message was incoming or outgoing?

  • Craig

    Simple instructions and very easy to use method.  Thanks heaps

    Craig

  • Monique

    I located this file successfully a couple months ago, and backed up my sms’s then. However, now I can locate the “3d0d7e5fb2ce288813306e4d4636395e047a3d28″ file but there are no extensions.  I have searched for *.mddata and *.mdbackup on my computer, and there are none. Has something changed?  I cannot access the sms’s in this file with no extensions.  fyi, I use http://www.iphone-sms.com/ backup tool which worked for me a few months ago. Thanks

  • Monique

    I located this file successfully a couple months ago, and backed up my sms’s then. However, now I can locate the “3d0d7e5fb2ce288813306e4d4636395e047a3d28″ file but there are no extensions.  I have searched for *.mddata and *.mdbackup on my computer, and there are none. Has something changed?  I cannot access the sms’s in this file with no extensions.  fyi, I use http://www.iphone-sms.com/ backup tool which worked for me a few months ago. Thanks

  • Monique

    I located this file successfully a couple months ago, and backed up my sms’s then. However, now I can locate the “3d0d7e5fb2ce288813306e4d4636395e047a3d28″ file but there are no extensions.  I have searched for *.mddata and *.mdbackup on my computer, and there are none. Has something changed?  I cannot access the sms’s in this file with no extensions.  fyi, I use http://www.iphone-sms.com/ backup tool which worked for me a few months ago. Thanks

  • Kunal

    If possible, could you PLEASE tell me a similar way to get the MMS’s? I really want to save all those on my pc but they are buried beneath at least a thousand SMS’s

  • Jimmy

    Hi,

    Thanks for this. I was able to recover some messages but I noticed that certain deleted messages which are readable when I open the the raw SQLite file “3d0d7e5fb2ce288813306e4d4636395e047a3d28″ do not show up after conversion to the .CSV file. I notice that these messages start with a “[” and my suspicion is that this denotes that the messages have been deleted and this is somehow preventing SQLite from converting these items into Excel data. I tried deleting the “[“s but it doesn’t seem to work

    Any ideas?

    Many thanks

  • http://pulse.yahoo.com/_UJOT4Y6AYGYXDQ3W5UGCWJB5FI Courtney

    I have tried everything suggested on this thread as to finding the 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata file. I’ve found where it should be, in the Backup folder, but it just doesn’t exist.

    Any other tips someone can give me?!

  • jane

    when i opened the csv file its empty. help? i backed up on itunes and followed everything. i use mac, i found the file.

  • Michelle McClellan

    Thank you so so much.  

  • Biigjiim

    HI Matthew,

    First of all, thanks for providing this. Effort appreciated.

    However I have a problem: Using Win XP, I have found the file no problem (the last modified date even tallies with the Last Backed Up date in iTunes for my phone. If I open the file in Notepad, it starts with SQLite format 3 and I can see my messages in amongst a while load of gibberish.

    However, when I open the file in SQLite Database Browser (v2.0b1) there are no tables and no database structure whatsoever.

    Am I doing something wrong? I am using iTunes 10.5 and ios 5 with an iphone 3GS

    Thanks,
    Jim

    • Elvar

      I am having the same problem. I see no data.

    • d00d on the internet

      I too have the same problem.
      Could it have anything to do with iOS 5 on a 3GS?

    • Strawberry7146

      I’m having the same problem. Is there a file size limit? I have two phones on my computer and one, whose SMS file size is 1.2 MB works, but my more recent phone, size 14.6 MB, won’t :(

    • http://www.facebook.com/apepelis Aaron Pepelis

      I believe with iOS5, it uses sqlite 3 and not 2.

    • http://twitter.com/brinke80 Kim Brink

      Same problem here. Opening the file in Notepad++ does tell “SQLite format 3″, so why won’t it open in SQLite Database Browser? Actually, SQLite Database Browser seems to open it, because it doesn’t give any errors. If you open any other (non db) file it WILL give error… Maybe there is a way to query via raw SQL…??

  • Mainstreetmc

    how do i copy sms messages from iphone 3 to computer

  • Biigjiim

    Managed to find a site that offers the service for free:
    http://www.iphone-sms.com/

    You do have to upload your file, so I guess there is the risk it could be misused, but it worked fine for me.

  • 1017952337

    With Tansee iPhone Transfer SMS,you can transfer the iPhone SMS to PC as a txt file or a ANTS file, so that you can view and manage your old iphone SMS in your computer freely. Further more,you can transfer your lover’s  sweet messages that she/he sent to you into computer and protect the file with a password to keep your privacy. Before the transfer, you can choose to transfer from all contacts or a single contact, easily to transfer your iphone SMS to computer only by 1,2,3 step!

    http://www.mustsoftware.com/Tansee-iPhone-Transfer-SMS-guide.html

  • Rober

    Hi my friends…

    My iPhone has IOS 5 and now I can´t open this file whit SQL Lite Browser, it appears as empty…

    Could somebody help me???

    Thanx for your help!!!

    • Shirley

      i got the same empty worksheet as well. could be becos of iOS5. anyone knows how to solve this?

  • Keimier

    I just tried it today (12-30-2011) and I also get an empty file. I used it on an older backup (12-23-2011) and it works fine….. That backup was the last one I made before installing ios5 (to remove jailbreak). So it does appear to be an ios5 issue.

    If anyone has a workaround, I would appreciate it.

    PS Wish I would have found the above instructions a while ago. I had to search everywhere for the date/time calculation.

  • Ttguy1

    Looks to me that apple have broken this for nearly every one. http://www.iphone-sms.com/ at the moment sends back  zero byte files.  And the SQL lite browser reports an empty datbase structure. I have found that a file created some time ago by my iphone works but one created recently does not (and there was probably an upgrad of some description between these dates).  I can report that http://www.insend.de has fixed this issue because they sent me a preview of the first 7 messages from the file that fails to read in SQL lite/and iphone-sms.com. However, they want to charge me $7 dollars to get the rest of of the data. And I am a cheapskate !!!

  • Pa

    It works perfectly, thanks for details.

    Are you an idea to convert into iphone sms standard lecture ?

  • Pa

    Hi
    i have this trouble.
    when i open sms.csv file, excel show me the content per line and not per colon. In cells A1 for example :
    ROWID,”address”,”date”,”text”,”flags”,”replace”, etc.

    A2 :
    96,”+86xxxx4244673″,”1298550860″,”At 6:30pm is better”,”2″,”0″,”",”31″,”0″,”0″,”4″

    It is really frustrating
    May i miss a step ?

  • Hushadog

    Hi! this is really helpful. I found the right file, but because I have added a new iphone to itunes even thought It says my new phone is backed up to computer the sms file shows all my old text messages from my old phone. Where would the new phone be backed up?

  • Hushadog

    I can find the sms file. I have been able to read this file on my old iphone but with the iphone 4s when I try and open it with SQlitebrowser it wont open.
    Can someone tell me how to read the sms iphone4s file why doesn’t it open. I have checked and it is the correct file. I just cant work out why it wont open. Can someone help. I want to open it myself and not have to use these other random sites to open it. Thanks

  • Hutzelmann12

    Hello together :o ) ..this discription is very easy and understandable, but the mentioned .mddata File (3d0d7e5fb2ce288813306e4d4636395e047a3d28) I’ll find always only without the extension “.mddata”. Opened in a text editor it shows me it’s the right file (“SQLite format 3″, with partly viewable text messages). If try to open it with the SQLite Database Browser it doesn’t show anything in the browser tabs. Can’t find out what I’m doing wrong?! …do you have any suggestions?

    • AdamTrask

      Same problem, using iPhone 4s iOS 5; no data shows in the SQLite dB browser. Any suggestions would be greatly appreciated.

  • Banjo

    HI, I have managed to locate the file but for some reason when I open it in SQlite- not all the messages appear (some deleted ones)
    I have tried everything but just can’t figure it out! Do you know a site or someone I could send the file to do this for me?

    Thanks

  • Guest

    How do you find the code after you download it?

  • Sman217

    doesnt work with ios 5

  • http://twitter.com/stellahstella Stella Robinson

    Useful article.  For folks that aren’t that tech savvy, I recommend checking out Decipher TextMessage http://deciphertools.com   Simple to use to backup your iPhone SMS/iMessage/MMS

  • Anon

    Thanks a lot! I’m using Lion and the backups are in a different location now:

    ~/Library/Application Support/MobileSync/Backup/

  • Jonathan

    http://www.iphone-sms.com works great now!

  • rpg

    why there is nothing when i open..3d0d7e5fb2ce288813306e4d4636395e047a3d28 by SQLite…anyone who can help me please?… Thank you….

    • veronica

      same is happening for me. 

      • ushug

        Find a program called Backuptrans iPhone SMS Backup & Restore to help. Easy to use.

      • Brittanyblack

        same thing to me….and i dont’ want to pay for backuptrans…help anyone??

    • George

      Same

    • bob

       I believe it is an issue with how IOS5 handles the file.  It used to work for me, but now I get the same error that you do.

    • http://twitter.com/RobHull2 Rob Hull

      I used this instead in order to back up my iphone sms text messages:

      http://www.copytrans.net/copytranscontacts-notes-sms.php

      It is not free but the trial version allows to back up up to 150 messages which was more than enough for me. Otherwise, I am computer illiterate and really don’t have the means of dealing with incrypted backup files. Hope to have been of help to someone feeling the same way…

  • Sdsds

    hi

  • Helpful

    Hi, i had the same ios5 blank problem.

    using this sql browser worked for me, I was able to quickly export

  • Helpful
  • Mignon171

    Brilliant, had a few problems finding the backup file on Windows 7, but finally, miraculously it worked. Thank you!

  • Matt, from Canada

    Took a little time to find the backup file using Windows 7, but once I found it the guide was perfect. And your help is going to be amazing in making an anniversary gift for my special lady!

    Thanks for doing this!

    • Matthew Ruten

      Glad it could help! 

      And hello to another Matt in Canada! (Not that we’re that rare…)

  • Cak

    This is great – it worked for one of my phones (iPhone3), but I have an old iPhone 4 which I wanted to extract SMS’ from also. With this phone I noticed not all my messages were contained in the downloaded CSV file? I backed up/synced my phone prior, but they still weren’t there. Can you explain this? Does the software need to be updated on the iPhone4?

  • Cak

     solved it – you need to check the other Database Structures too (like “msg_pieces”)

  • Baron Green

    It’s a good share.

  • Philippe

    Hello.
    Thank you very much, I always searched for the file that store the SMS in the backup files!
    But I have another question : is it possible to restore an iPhone from scratch, backup it, replace just the SMS file with our old ones and restore the backup, so we get a fresh brand new restore of the iphone but with our old SMS?

  • Rentareck

    Table And message table don’t even exist on this app. Now what?

  • Carlos Nilton

    Thank you!! Your post was very helpful to someone in Brazil!

  • John

    Thanks very much!  Worked very well for me.  I really appreciate the DIY approach.  Just what I was looking for.

  • Pravmars Kachra

    That’s great and really helpful, Thank you for sharing

  • Crosta

    Perfect! thanks :D

  • Amanda Tracy

    This worked for me:

    http://youtu.be/uO6YUme7kG4

  • lookin

    Same problem with ios5 and SQLite Database Browser showing file as blank. May be attributable to updates to SQLite format, because these instructions worked:

    http://www.mysysadmintips.com/other/mobile-devices/263-export-iphone-sms-messages

  • yizeno

    Sometimes it is worthy to pay a little for those developer, who made quit a lot effors on a small software.
    And if you want to understand the principle of IOS, your way is recommend. But if you just want to export your SMS pay for tool with suitable price, it can save you quit a lot time. I am software engineer, and I develop some software that is more complicate than those tool. But I have bought one on how-transfer(http://www.how-transfer.com), just for enjoying the great tool and it does save me a lot of time.