How do I extract and combine messages from Time Machine backups of ~/Library/Messages?

Question

I have many backups that contain versions of ~/Library/Messages. Each one contains 3 files and a directory similar to this:

    XYZ:Messages user$ tree -a -L 1
    .
    ├── Attachments
    ├── chat.db
    ├── chat.db-shm
    └── chat.db-wal

    1 directory, 3 files

Some of these backups may overlap with each other. The result is 10-20 folders of messages with no obvious way to access them. I would like to unify all of them and remove duplicates if they exist.

Are there any applications that do this or any sort of tutorial I could follow? The files are sqlite so even a specification of the database structure would be helpful.

Answer 1

Some of these backups may overlap with each other. The result is 10-20 folders of messages with no obvious way to access them. I would like to unify all of them and remove duplicates if they exist.

Yes, you are going to have many, many duplicates. This is expected behavior given the way backups work.

Time Machine does an incremental backup; meaning it only backs up what has changed since the last incremental backup. Your chats are held in a single database file (referenced in your question), so every time you chat with someone, that database gets updated and ultimately gets backed up every time Time Machine runs.

Are there any applications that do this or any sort of tutorial I could follow?

No. Just go to the most recent backup; all of the chat files will be there.

The files are sqlite so even a specification of the database structure would be helpful.

They are sqlite3. If you want to get the database structure, you can use the following commands in Terminal:

$ sqlite3 chat.db

sqlite> .schema                            # prints out the database schema
sqlite> .tables                            # lists the database tables
sqlite> PRAGMA table_info(tablename);      # lists the columns of a particular table   
sqlite> select text from message;         # raw output of all text messages