The main character of the game is Simon Belmont, returning from the first Castlevania. He's been infected with a curse, and the only cure for said curse is bringing Dracula back to life, and killing him again. To do this, Simon has to collect various relics of Dracula (“relics” in the Dark Ages saint's definition, so like little parts of his body, like his fingernail) that someone has locked into houses and preserved. Once these are collected, he can go to the castle, bring Dracula back, and kick his ass.
A note: Relics in the Dark Ages are a pretty interesting topic, as usually there was some kind of caretaker for the relic who would tour it around, usually making money off of it. Also, the presence of a relic usually meant there was some kind of minor cult behind the figure the relic originally belonged to. In this case, that would mean the relics in Simon's Quest indicate Dracula has achieved a saint-like status among some people in the region, and has a cult working to preserve him.
So yes, the basic plot is that Simon has to bring Dracula back to life just to kill him again. Simon is pretty much a badass.
The English translation of this game (and possibly the Japanese original) has been proven to be full of lying villagers. Because of this, and a lot of random insane videogame logic, the game is almost unbeatable without an FAQ.
Pyron is a total conversion, turning Castlevania II into a future-set Metrovania, with a jetpack.