Adventure stories are tried and true for videogames, cinema, books, comics, and - of course - RPGs. They're so tried and true, we frequently call our parceled stories for games 'adventures' and the characters - at least in fantasy games - are often considered to be 'adventurers'. So how do you catch that feel for an Adventure type scenario? You know what I'm talking about, the thing that happens in what would otherwise be the mushy middle of a story to help keep things going, and to keep people entertained with action and intrigue.
It seems to me, it boils down to answering three questions and in answering these questions you can setup your own scenarios:
Question 1: What Thing Do The Adventurers Need To Acquire
First off you need a maguffin (warning, TVTropes link) in order to get things going. This is the thing that the PCs need in order to continue. Are they blocked by an impenetrable gate that keeps them from getting into the Evil Overlord's kingdom undetected? Than they need the 'Wraith Key' in order to open that gate. Are they on an interstellar quest to find the hidden vault that is humanity's last hope? They need the coordinates of the vault's final location which are stored on a legendary lost ship.
There's tons of ways to make maguffins. The point is you need a thing - often mundane but dressed up with fancy words (i.e. Wraith Key is just a key with the word Wraith in front of it). That's the goal. The thing the heroes need to continue forward.
Question 2: Why/How Is Acquiring The Maguffin Inherently Dangerous?
The maguffin is never just sitting in the local k-mart waiting to be bought. It is always somewhere dangerous. Otherwise it wouldn't be a plot point item, it would just be a done thing. This is where Adventure stories play up the fantastical and make a truly dangerous scenario that is already set to be an adventure in and of itself.
The wraith key is in a desert guarded by giant sand worms. The coordinates are on a planet of endless night full of monstrosities that can eat ships. The rose needed to save the King is literally IN the afterlife and you have to die to get a chance at getting it.
It doesn't have to be the whole area or world, but a small part. Perhaps in a castle with a lot of guards, or in the actual gullet of a whale. Have fun with it, go for the spectacular and something that says "this will be a memorable episode."
Question 3: What Goes Wrong To Make It Even Worse?
So you have your maguffin, and you have why retrieving it is already going to be dangerous. Now what goes wrong that makes it even worse/harder to deal with? You can play with the when of this for variance as well.
For example, the PCs arrive to find the enemy army is here in force also looking for the maguffin plays differently than the enemy sends a team to ambush the PCs after they find the maguffin for them.
If Question 2 is about establishing the scenario as a massive powder keg, then question 3 is about throwing a whole ton of fire into the mess to get a spectacular explosion. Your choice on if it is something to do to the answer to question 2 (it's sand worm mating season!) the meta plot (the empire is already here!) or something else completely unrelated (the whale's gullet is haunted and requires fighting through an army of undead!)
The rest is just planning out how it all works out. But if you have these three questions you have the tent poles for everything you need. You can also see this in countless video games.
In Mass Effect 2 to get Doctor Mordin Solus for your team you have to go into an area under lockdown from a plague and dealing with looters. It then gets even worse as you find out to get Mordin to help you, you need to fix a facility to help cure the plague in a hostile environment.
In Raiders of the Lost Arc, Indiana Jones needs to find the location of the Ark of the Covenant. The problem is the map room is in the middle of a Nazi Camp. This gets even worse when he goes in and finds Marian is a captive there.
In Titan A.E. the crew needs to find the location of the Titan, which involves checking their 'compass' on a planet with inhabitants not known for their hospitality. This gets even worse when the enemy shows up looking to kill the crew, causing a huge chase scene in the middle of some exploding plants.
You can play with how things work for all sorts of different ways. Whether you just want to go for the high sting of adventure, or bring in more personal or character related moments. But the core of those three questions, and variants on them, can be seen in all of it.