How to query data from iTunes backup SQLite and Plist files

Aidan Fitzpatrick

By Aidan Fitzpatrick

Updated

See how I'm qualified to write this article

Users of iPhone Backup Extractor are able to automatically export their data to PDF, HTML, VCard, ICAL, VCF or CSV formats for easy access.

Some users may wish to directly manipulate the raw files stored in their backups using expert mode. These are often comprised of SQLite database (.sqlite3, .sqllitedb and .db) or Plist (usually .plist) files.

iPhone Backup Extractor includes a built-in Plist editor, but users wishing to extract data from these files will need a SQLite database client.

How can I work with SQLite databases?

Users might want to try either of these two applications:

  • DB Browser for SQLite. DB Browser used to be named the SQLite Database Browser. It's our recommended tool for Windows, and it's free. They also provide a version for macOS.
  • Base 2. Base is our preferred SQLite tool for macOS, and is available as a direct download and App Store app.

How can I work with Apple's database files directly?

As an example, to manually extract contact data, users should restore "Libray/AddressBook/AddressBook.sqllitedb", and then open the restored file with the SQLite Browser. Choosing the "Execute SQL" tab, paste this into the "SQL string" field:

SELECT ABPerson.first, ABPerson.last, ABMultiValue.value
FROM ABPerson, ABMultiValue
WHERE ABMultiValue.record_id = ABPerson.ROWID

Pressing the "Execute Query" button should return the full contact list shown in the "Data returned" field.

To extract SMS data, users should restore the file Libray/SMS/SMS.db, open the restored file with the SQLite Browser browser, choose the "Browse data" tab, and then select "message" from the "Table" drop-down. On clicking the magnifying glass button, users should see their full SMS history shown in the "Data returned" field.

The date and time fields just come back as numbers, how can I interpret them?

You may struggle to interpret a number of fields in the databases. Don't forget, iPhone Backup Extractor can extract the data automatically for you! However, if you're going it alone, you should find the dates are represented as either the number of seconds since 2001-01-01, or 1970-01-01, depending on the position of the date field. In Excel, you could use a formula such as =1/1/2001 + A1/60/60/24 where A1 is your time data. You may also need to adjust the value to account for your local timezone.

About the author

Aidan Fitzpatrick founded Reincubate in 2008 after building the world's first iPhone data recovery tool, iPhone Backup Extractor. He's spoken at Google on entrepreneurship, and is a graduate of the Entrepreneurs' Organisation's Leadership Academy.

Reincubate's CEO at Buckingham Palace

Pictured above are members of Reincubate’s team meeting HM Queen Elizabeth Ⅱ at Buckingham Palace, after being awarded the UK’s highest business award for our work with Apple technology. Read our position on privacy, safety and security.

Can we improve this article?

We love hearing from users: why not drop us an email, leave a comment, or tweet @reincubate?

How can we help?

Our support team are here to help!

Our office hours are Monday to Friday, 9am to 5pm GMT.

We aim to reply to all messages within one working day.

Get in touch › Our awesome support team

© 2008 - 2019 Reincubate Ltd. All rights reserved. Registered in England and Wales #5189175, VAT GB151788978. Reincubate® is a registered trademark. Privacy & terms. We recommend 2FA. Built with in London.