I was skimming questions on the Salesforce Trailblazer Community the other day when a post caught my eye. The admin posting the question detailed how she was unable to find a case record she absolutely knew existed. In fact, no one could find it. No matter which search terms were used, it refused to surface. It also wasn’t in the recycle bin. Where did it go? Could they find it again? Was it gone for good?
And if permanently deleted, could any trace of it still be found?
Well, if you know me, nothing makes me want to write a SOQL query more than a missing record. I mean, if SOQL can’t find it, nothing can. Right?
Maybe.
To replicate her issue (as best I could), I opened up a dev org and wrote a quick SOQL query in the dev console to find all cases. The query looked something like this:
SELECT Id, Subject, CreatedDate, IsDeleted FROM Case
All the cases returned false for the IsDeleted field. So, I selected one, navigated to its record detail page, and deleted it.
I ran the query again. And once again, all the cases returned false for the IsDeleted field. But this time the case I had just deleted was missing. Huh.
I checked the recycle bin. It was there. So I went back to the query and added a filter (WHERE isDelted = true) just in case I had somehow missed the record:
SELECT Id, Subject, CreatedDate, IsDeleted FROM Case WHERE IsDeleted = true
Nothing. No results. Null.
I did a Google search on finding deleted records using SOQL. And, as luck would have it, a blog post (credit: Jeff Ballard) showed up that re-introduced me to the SOQL keywords ALL ROWS.
Adding ALL ROWS to a query is like checking the Export All Records box in Data Loader. If you do that, even deleted records sitting in the recycle bin show up (more on Data Loader below).
But here’s the rub: ALL ROWS doesn’t work in a SOQL query written in a query editor. It only works in a query used in Apex.
Conveniently, the same blog post shared a snippet of Apex code to use in the Execute Anonymous Window found in the Dev Console. I manhandled it a bit to meet my requirements. I also changed it up to ensure only one case record was returned per debug line. In the end, it looked like this:
List<Case> deletedCases = [SELECT Id, Subject, OwnerId, CreatedDate, IsClosed, IsDeleted
FROM Case
WHERE IsDeleted = true
ALL ROWS];
IF(!deletedCases.isEmpty()){
for(Case nextCase : deletedCases){
System.debug(nextCase);
}
}
The important part here is that the query includes ALL ROWS. And once I ran it, it returned the case sitting in the recycle bin. Whoop!
But it gets even better!
I thought to myself, if ALL ROWS can find a record in the recycle bin, could it find one that was permanently deleted? Could it find a digital remnant? Could it find a ghost?
To test that hypothesis, I deleted another case record so that I now had two case records in the recycle bin. I ran the code again, and as you may have guessed, both showed up. Next, I selected one of the cases in the recycle bin and permanently deleted it. Now there was no way to recover the record. It was simply gone. Forever gone. Or was it?
I ran the query again.
Boom. Both cases showed up. Why was that? (And is that even a good thing?)
After a bit of research, it appears that permanently deleted files sit around in a purgatory-like state for an unknown (but short) period of time before Salesforce truly purges them. It could be hours, days, or possibly weeks before they no longer show up in an ALL ROWS query.
The next day, I ran the code again. Once more, the ghost record appeared. The following day I ran it again. Poof. It was gone.
The Good: While ghost records are not recoverable, as long as they still exist in their suspended state you can query them. This will help you determine when the deleted record was last modified (LastModifiedDate) and by who (LastModifiedById). At least that way you can locate a suspect for questioning. Ha.
The Bad: Time is of the essence. These ghosts don’t sit around for very long (and that’s a good thing). So, if you’re looking for a file that was deleted several weeks ago, you may still be able to query it (or maybe not). Remember, a file sits in the recycle bin for two weeks before being permanently deleted. After that, you’re relying on a bit of luck.
As mentioned above, Data Loader (as well as dataloader.io) has the Export All Records advanced feature which can be used to export these ghost records as well. To do that, you’ll want to filter on the Deleted (IsDeleted) field and set it to true. Be sure to check the Export All Records box prior to running the query. Remember, as long as the files are sitting in the recycle bin or in purgatory, they will still be searchable. But when they’re gone, they’re gone.
If you’re reading this post because you’re searching for a deleted record, good luck!
(Hey, since you’re still here, if you want to learn more about SOQL queries, check out my 3-part series called SOQL 101 for Admins. Start here with Part 1. It’s fun!)