The most emotionally intense moment I've ever experienced in a video game (and I've played a lot of video games) was crossing the border into Mexico in Red Dead Revolver. The hairs on my arms stood on end. The YouTube clip [0] doesn't do it justice because it misses the context and buildup. Just magic.

That and docking with a spacestation in Elite to the Blue Danube waltz.

[0] https://www.youtube.com/watch?v=AUXGW6sWYDY

I never played Red Dead, but my most memorable video game moment I think was the end of Brothers: A Tale of Two Sons. I can't say more w/o spoiling it but the way it mixed game and narrative was inspiring.

That game is amazing and was developed by Starbreeze Studios (the demogroup formerly known as Triton). The legendary Gustaf Grefberg did the music!