I recently got around to playing (and finishing) Deus Ex: Human Revolution. What's strange is that when I think of the game's most memorable moment, what I recall is the intro video after the game's starting section. I'll stop there, lest I venture into spoiler territory, but boy, it really gets you in the mood. The same can be said of the main theme for Arcanum: Of Steamworks and Magick Obscura, a sorrowful, yet rousing piece that made me want to play even more.
You can listen to the theme in the clip above and a high-quality MP3 is available online -- it was offered free on the game's official site until it dropped off the face of the internet.
I think there's an important distinction between a piece of game music, or an in-game cutscene that's just good and one that seems so in-sync with what the game's about it stops being a perfunctory chunk of media and becomes a necessary part of the experience. Think of all the games you've played and how many throw-away tunes you've heard and then think about, say, Legend of Zelda. I'm sure the series' best instalments would still be great without the iconic theme, but with it, there's another layer of emotional depth that drives you to play, beyond gaining new equipment or seeing the next scrap of exposition.
Maybe it's partially nostalgia with the Nintendo classic, but rarely, I get that feeling with newer games, like Deus Ex: HR. How about you? What recent games do you feel perfectly set the mood with their music, cutscenes or movies?