I have always liked the intro of Dark Forces 2: Jedi Knight. First the well known rolling text and score, then the burning hulk of a Corellian Corvette, followed by the miles long Star Destroyer of Jerec. It was one of the first Star Wars games I played and at that point send a shiver down my spine.
I always liked FMV in a game like in the C&C and Red Alert games, it's a shame they don't get used as much any more. Recently Act of War had some in them, but they were so awfull with bad acting, silly dialogue and with a storyline so twisting and turning, it tied itself into a knot. Maybe with the recently announced C&C 3 we'll finally see some decent fmv's again.