Extract all your Pictures, Videos, PDFs from iPhone backup!

I hate the fact that is so hard to retrieve all my files that I have on the iPhone (this is one of the reasons why I am switching from iPhone to an android device). They are all in the iPhone backup, but with their names scrambled, without extensions is impossible to sort thousands of files.

But not any longer, see how you can get access to all your files and data!

1. Create a backup of your iPhone on your computer, that is _not_ encrypted (as described here)
2. Find the files of your backup:
   
   • on Windows 8.x and 10 it is here: c:\Users\\AppData\Roaming\Apple Computer\MobileSync\Backup\
   • on Windows 7 and Vista: C:\Users\\AppData\Roaming\Apple Computer\MobileSync\Backup\
   • on Windows XP: C:\Documents and Settings\\Application Data\Apple Computer\MobileSync\Backup 
   • on OS X: ~/Library/Application Support/MobileSync/Backup/
3. Each backup name is a 40 character long SHA (seems like garbage: 00d07a612db092c28c316244cce7d9199f23da33).
   Inside of them you will find another 256 folders named 00-ff, and inside those are your files, also with the long hash (garbage) names.
4. I have created the below script, that will:
   - sort your files into target folders by type, and add their correct extensions
   - decode the Apple Binary Property Lists and SQLite databases, so that you can view and search them in clear text! (databases will also be copied there besides the dump if you want to open and query them!)
5. You will need bash, sqlite3 and plistutil to run it, either on your Linux, or for windows: use the Linux subsystem in Windows 10, Cygwin on earlier versions of Windows.
6. Please do not forget to replace the backup folder name and the target directory name with yours!

#!/bin/bash
#
# Created to compare speed and compression on a VM image
# to be run in the VM working directory e.g. /mnt/d/VMs
#

# Find the utils we will need
if ! which sqlite3 >/dev/null; then echo "Please install sqlite3 to dump database file contents!"; exit; fi
if ! which plistutil >/dev/null; then echo "Please install sqlite3 to dump database file contents!"; exit; fi

# Identify file types
# Put target directory here!
TDIR="/mnt/c/Users//Desktop/iPhone6.HU"
mkdir -p "$TDIR/Pictures/JPEGs/" "$TDIR/Pictures/PNGs/" "$TDIR/Text/" "$TDIR/XML/" "$TDIR/PDF/" "$TDIR/PlistToXML/" "$TDIR/Database/" "$TDIR/Audio/" "$TDIR/Contacts/" "$TDIR/Pictures/TIFFs/" "$TDIR/Fonts/" "$TDIR/Others/" "$TDIR/Movies/"
 
# Put your backup folder here!
file --no-pad /mnt/c/Users//AppData/Roaming/Apple\ Computer/MobileSync/Backup//*/* |

while read
do
  SRC="${REPLY%%:*}"
  FLNAME="${SRC##*/}"
  case "${REPLY#*: }" in
  (JPEG*)
    cp --preserve --no-clobber "$SRC" "$TDIR/Pictures/JPEGs/$FLNAME.jpg"
    ;;
  (PNG*)
    cp --preserve --no-clobber "$SRC" "$TDIR/Pictures/PNGs/$FLNAME.png"
    ;;
  (ASCII\ text*|*Unicode\ text*)
    cp --preserve --no-clobber "$SRC" "$TDIR/Text/$FLNAME.txt"
    ;;
  (XML\ document\ text*)
    cp --preserve --no-clobber "$SRC" "$TDIR/XML/$FLNAME.xml"
    ;;
  (PDF*)
    cp --preserve --no-clobber "$SRC" "$TDIR/PDF/$FLNAME.pdf"
    ;;
  (Apple\ binary\ property*)
    plistutil -i "$SRC" -o "$TDIR/PlistToXML/$FLNAME.xml"
    ;;
  (SQLite\ 3*)
    cp --preserve --no-clobber "$SRC" "$TDIR/Database/$FLNAME.sqlite"
    sqlite3 "$SRC" .dump >"$TDIR/Database/$FLNAME.sql"
    ;;
  (MPEG-4\ LOAS*|*AAC*)
    cp --preserve --no-clobber "$SRC" "$TDIR/Audio/$FLNAME.aac"
    ;;
  (vCard*)
    cp --preserve --no-clobber "$SRC" "$TDIR/Contacts/$FLNAME.vcard"
    ;;
  (TIFF*)
    cp --preserve --no-clobber "$SRC" "$TDIR/Pictures/TIFFs/$FLNAME.tiff"
    ;;
  (TrueType*)
    cp --preserve --no-clobber "$SRC" "$TDIR/Fonts/$FLNAME.ttf"
    ;;
  (*QuickTime\ movie*)
    cp --preserve --no-clobber "$SRC" "$TDIR/Movies/$FLNAME.mov"
    ;;
  (*)
    cp --preserve --no-clobber "$SRC" "$TDIR/Others/$FLNAME"
  esac
done
 
Sample output folder:

Enjoy!

Should you add more interesting file types, please add them in the comments section!

How to transfer iPhone (SMS) messages to Excel

It's very easy to get your messages out of your iPhone:

1. Create a backup of your iPhone on your computer, that is _not_ encrypted (as described here)
2. Find the files of your backup:
   
   • on Windows 8.x and 10 it is here: c:\Users\\AppData\Roaming\Apple Computer\MobileSync\Backup\
   • on Windows 7 and Vista: C:\Users\\AppData\Roaming\Apple Computer\MobileSync\Backup\
   • on Windows XP: C:\Documents and Settings\\Application Data\Apple Computer\MobileSync\Backup 
   • on OS X: ~/Library/Application Support/MobileSync/Backup/
3. Each backup name is a 40 character long SHA (seems like garbage: 00d07a612db092c28c316244cce7d9199f23da33).
   Inside of them you will find another 256 folders named 00-ff, and inside those are your files, also with the long hash (garbage) names.
4. Go into the folder '3d', find the file 3d0d7e5fb2ce288813306e4d4636395e047a3d28
5. Copy it for example to your desktop, rename it to: 3d0d7e5fb2ce288813306e4d4636395e047a3d28.sqlite
6. Download the free and open-source SQLite database browser: http://sqlitebrowser.org/
7. Install it, start it and open the .sqlite file from your desktop.
8. Go to the Execute SQL tab, and enter:
   select  chat.last_addressed_handle,
           chat.chat_identifier,
           datetime(message.date + strftime('%s', '2001-01-01 00:00:00'),'unixepoch', 'localtime') as date,
           message.text,
           message.service
   from chat, chat_message_join,message
   where chat_message_join.chat_id=chat.ROWID and chat_message_join.message_id=message.ROWID
   order by last_addressed_handle,chat_identifier,date;
9. Now you should get a table with 5 columns (from which phone, to which number, date, text and service) and as many rows as many messages you have saved in the backup!
10. Save them as CSV:
    
11. Now you can open that with Excel!

Extra step, if you have messages with international characters: you need to add three bytes (the UTF8-BOM) to the beginning of the file for Excel to recognize this:

• You can either open the file with Notepad++ and save it as UTF-8-BOM as you see it in the screenshot

• Or if you have bash, use this command line to add it:
  echo -ne "\xEF\xBB\xBF"|cat - iphone-messages.csv > iphone-messages-BOM.csv

 Enjoy!

Feel free to modify or extend the SQL to suit your needs, please post in the comments if you have a cool new one! 

How to disable iPhone requiring backup encyption?

Your iPhone requires you to encrypt your backup?

... and you cannot find how to turn it off? 

iTunes surprised me with the message: "This iPhone requires backup files to be encrypted"

After a lot of digging I have found that this can be caused by installed Profiles (Crashlytics Provisioning Profile in my case, but some other people reported the same with an M1 (the telecommunication company) Profile installed).
To delete it:

1. Go to Settings > General > Profile, tap on the app’s configuration profile.
2. Then tap Delete Profile. If asked, enter your device pass-code, then tap Delete.

Then:

1. Go to the Summary page for your device in iTunes
2. double check "Automatically Back Up" is set to "This computer"
3. double check "Encrypt iPhone backup" is _unchecked_
4. Press "Back Up Now"

Congratulations, your backup will be in:
c:\Users\\AppData\Roaming\Apple Computer\MobileSync\Backup\

Now you can go ahead and get your text messages from it! :)