I've been playing Sims since it came out in 2000. I've probably made thousands of game saves and characters, but I remember very few. I remember my last Sims 3 family because I was very into them and ...
Interesting question : ) Also I am so sorry about your TS3 save. I've had plenty of my saves corrupt in the past, to the point where TS3 became my 'just mess about and build things' game instead of me trying to raise a multi-generational family. I don't remember a lot of my TS3 sims, sadly, although I remember causing a lot of chaos in that game. It's sad about Golly too, but from a storytelling point-of-view, also an interesting way for the challenge to go.
From the POV of someone who's often reading others' challenges etc- what makes others' sims memorable for me, is the little ways the players show off their personalities, especially challenge authors who add humorous bits of dialogue as well. One thing I particularly love is when challenge writers/storytellers breathe life into the pre-mades as well. I love seeing everyone's different interpretations of the same character.
As for my own sims, I think one of my more 'memorable' Sims was Gen 3 of one of my challenges, Tyler. Not memorable for any deep reasons or anything, but: The 'story' I had going for previous characters in the family got quite dark, so Ty's side of the family story ended up more light-hearted and fun, and I just imagine them in my head to be very loud and chaotic almost all the time. Ty rolled the Cat Lover trait, but their eldest son rolled Dog Lover. So I imagined the son constantly asking for a dog all of the time to the point where they probably got fed up of hearing it; in game, I left it a while before Ty gave in and eventually adopted one, and eventually warmed up to the dog. Also I had no trouble getting the other gens to find a partner, but poor Ty got rejected by about six different people before finally finding the love of their life. (I put the screenshot of them and their son under the Spoiler cut for neatness's sake)