- Dan Lister
Holy crap. A bunch of my Photos albums just vanished. Not deleted by me (as far as I know), not in the trash, not in any folder I could see. Just gone. The photos themselves were still there but they weren't showing up in my "not in any album" smart album either, which meant they were definitely in some album somewhere. I just couldn't find which one.
This post is the story of digging them out, with the SQL and the recovery script. If you've ever lost albums or folders in macOS Photos and iCloud Photos is making things weird, hopefully this saves you some pain.
The Symptom
Two top-level folders, 2025 and 2026, were missing from my Photos sidebar. They'd previously contained loads of date-prefixed albums (250101_running, 260419_manchester_marathon_official, that sort of thing). The albums themselves seemed to be gone too.
But here's the weird bit. If I right-clicked a photo I knew belonged to one of those albums and picked Show in Album, Photos would happily open the album. Title bar showed the name, all the photos were there. Clicking the back arrow took me straight to the top-level Albums view, where the album wasn't visible. So the album existed, had photos, but Photos had no idea where to put it.
AppleScript couldn't see them either. count of every album returned 2. I had hundreds.
The Photos Library is a SQLite Database
If you didn't know already, the Photos Library.photoslibrary package on your Mac is just a folder, and inside it at database/Photos.sqlite is a SQLite file holding the entire album/folder structure. You can query it with the standard sqlite3command line tool.
Quit Photos first so the database isn't locked, copy it somewhere safe, and open it up.
The table you want is ZGENERICALBUM. It holds both albums and folders. The columns that matter:
Z_PK- primary keyZTITLE- the nameZKIND- 4000 means folder, 2 means albumZPARENTFOLDER- foreign key back toZGENERICALBUM.Z_PK, defining the treeZTRASHEDSTATE- 1 if trashed, 0 if live
Finding the Trash
First useful query - what's currently in the trash?
(Apple stores timestamps as seconds since 2001-01-01, hence the + 978307200 to convert to Unix epoch.)
For me this returned the smoking gun. Two folder rows for "2025" and "2026" with ZKIND = 4000 and ZTRASHEDSTATE = 1, trashed a few weeks back. A couple of child albums were trashed too, but only a couple. Which seemed odd given how many were missing.
The Orphan Problem
Turns out when a folder gets deleted in Photos, the folder row gets ZTRASHEDSTATE = 1 and so do its child albums - but only the ones deleted in that specific moment. If anything got separated from the deletion path (and apparently it can happen all by itself, somehow), you end up with the folder marked trashed but live albums underneath, still pointing at the trashed folder via ZPARENTFOLDER.
Those albums are in a kind of limbo. ZTRASHEDSTATE = 0 so they're not in the trash. But their parent is trashed, so Photos has nowhere to render them. The UI can't show them and AppleScript can't enumerate them. They exist only as rows in a database.
This query confirmed mine:
One note - the join table for album/asset membership is named Z_33ASSETS on my macOS version. It changes between versions. Find yours with:
The result was 57 orphaned albums, totalling over 1,000 photos. All hanging off two trashed parent folders. All invisible from the UI.
The Fix
I'd assumed I'd need to recreate the folders and somehow repoint 57 child albums at the new parents. That works, but it's risky. 57 row updates is a lot of surface area for iCloud to disagree with.
Then it occurred to me - the children already point at the parents. If I just untrash the parents, the children should reappear automatically. Two row updates.
Done.
The iCloud Bit
Directly modifying the SQLite file when iCloud Photos is on is genuinely risky. iCloud considers the cloud-side state authoritative for most things, and there's no documented contract for what happens when local and cloud disagree on a record's trashed state. Three possible outcomes:
- Local wins - the untrash propagates up, your other devices see the folders reappear, you're sorted.
- Cloud wins - Photos syncs the trashed state back down, your change reverts, you're back to square one.
- Conflict - partial state, weirdness, possibly a library that needs repairing.
No way to know in advance. Mitigation is to back up Photos.sqlite first so the local side is recoverable, and make the change as minimal as possible so the cloud diff is as simple as it can be.
In my case it worked cleanly. Folders reappeared with all 57 child albums intact, and a minute or two later they'd synced to my iPhone too.
The Script
The full recovery script does four things: quit Photos, back up the SQLite file with a timestamp, show the current state of the two rows you're about to modify, and apply the UPDATE inside a transaction after a confirmation prompt. It prints the rollback command on the way out in case you need it.
You'll need to swap in your own folder primary keys - get them from the trashed-list query above.
A Few Tips If You're Trying This
Run the orphan query first. Confirm your missing albums are actually orphaned (live albums with trashed parents) rather than just trashed themselves. The fix only works for the orphan case. If ZTRASHEDSTATE = 1 on the albums, the untrash on the parent won't bring them back.
There's a time limit. Photos hard-purges trashed records after about 30 days on a background schedule. Once a folder row is hard-deleted, the orphan links break for good. I was at day 15 when I noticed.
Back up before you write. The local backup won't undo cloud sync, but you absolutely want it if iCloud rejects your change.
Don't open Photos between the backup and the UPDATE. Photos will start syncing and writing as soon as it opens, which can make your backup stale.
The bigger lesson though - the Photos SQLite schema is well-named, stable across recent macOS versions, and entirely queryable with standard tools. If the UI is letting you down, the database almost certainly still knows where everything is.