A lot of games, not just RPGs, but in many other genres, have similar storylines. Conflict (usually you vs the bad guys) is one of the main themes in video games, and there's not really too much you can do with that. I mean, having some big menacing baddie to fight is your incentive to grind through dungeons, building up levels and finding treasures, basically defining the whole gameplay. So on that note, what makes a good RPG story is not the overall plot, but how the plot is told. Some of my favorite RPG stories are from the Dragon Quest series - Totally basic at the core and usually a coming-of-age tale. But, the collection of sub-stories that make up the whole are what gives the series its charm. The sub-stories usually have unique character personalities and their own little conflicts (usually culminating in you tackling a dungeon or two to solve the conflict), that right there is much better than your typical RPG fetchquest (oh, you got the Dawn Shard, now go trek across half of the world and get the Dusk Shard!). For example in DQ6 (bear with me since it's been a long time playing this game) - one town's water is polluted.. colored a strange blood-red color. Your party is told to find out the reason, and you trace the flow of water back into a cave. While exploring the cave (fighting monsters, getting treasure, all that good RPG gameplay stuff), you find out that a guy accidentally killed his lover, was tricked into it by the evil boss of the dungeon, and now is trying to wash the hands free of blood. So now you have to help the guy atone for his mistake, then the water will clear up and you can advance to the next town.
The combination of appealing characters and clever sub-stories, as well as good pacing (the right balance of gameplay length and plot reveals), and a couple of twists, are what makes an RPG story "good" in my opinion.
One comment in this thread stuck out for me... how he complained how RPG storylines are too "safe", but you know what, in the age when they are trying to make Sonic the fucking Hedgehog "edgy", that's a relief! "Mature" in the video game world = teenage power fantasies, excess violence, sex, dirty language... there are EXTREMELY few games with a truly mature storyline. Silent Hill 2 being the only one I can think of off the top of my head, but I can't say it's worth putting up with its poor gameplay to experience it
