Probably a slight bias because I recently played it, but in Advance Wars: DoR, the mission as Brenner vs Forscythe was pretty great, being the last battle of a war between two rival nations with their top commanders in a wasteland of a world. Even when everything turned to shit, old hatred still simmered and the soldiers were willing to die to defeat the other side.
Frostpunk, a recent city builder, only has six actual missions, but of them, Fall of Winterhome and Last Autumn perfectly strike their notes. In Fall of Winterhome, you assume the role of commander after an insurrection due to poor management by the previous commander and have to somehow manage your broken city back to a livable condition while taking care of 600 people, and when you finally get things back on track, you find a critical flaw in your generator, which provides the only respite against the freezing cold, is eventually going to compound and cause it to break down entirely, prompting a frenzied effort to make enough steel, rations, and coal to repair a broken dreadnought and set out to find another city that might or might not exist
. In Last Autumn, you're responsible for constructing a generator before the great winter sets in and have to balance how hard to work your men, how safe the construction site is, and deal with discontent over poor working conditions all the while no one really believes the imminent threat