I have a lot of favourite gaming moments, but I'll list a few here. Knowing me, I might post a few more a bit later, if my memory throws something thought long forgotten back up at me.
I'm afraid all of my favourite moments are going to be from RPGs, as I seem to play little else these days.
First, there are quite a few from FFVII. I won't go too deeply into spoilers, but aside from the main plot highlights, I loved the motorbike getaway, trying to get the huge materia back from the Shinra train (especially playing as Cid), and finding Cloud again. Oh, and the ending. I could be here quite a while...
Another one of my favourite gaming moments was encountering/beating Freya in Star Ocean: Till the End of Time, and another would be every time I get all 108 stars/characters in the Suikoden games. The Deling City festival in FFVIII was great, and I have to say the scene with Squall and Rinoa in the cockpit of the Ragnarok (I think) was wonderful. The music was excellent, too.