The Galaxy-class USS Enterprise NCC-1701-D is the fifth United Federation of Planets Starfleet ship to bear the name Enterprise, including the unlettered original but not including the pre-Starfleet NX-01 Enterprise. It makes a strong argument for the most famous Enterprise, albeit mostly among the under-30 crowd, as its seven-season tour of duty took it to more places where no one had gone before than the original Constitution-class Enterprise did on its three-season run.
The creative staff on Star Trek: The Next Generation clearly wanted a sleeker, more futuristic looking Enterprise for their new show. The warp nacelles are streamlined oblongs as opposed to cylinders, the stardrive section is flatter and much less round, the saucer section is much larger (almost comically so), and the deflector dish is no longer a satellite dish looking attachment but a glowing blue, sunken device integrated directly into the front of the hull. Famously, the saucer section could separate from the stardrive section in emergencies, and although this was only seen a few times (budgetary reasons, apparently), it was the only starship ever seen to have done so in any of the Star Trek shows and movies. Overall, it looks much less like the 1960s version of the future and more like the 1980s version.
The Enterprise-D spent, for all practical purposes, its entire service life under the command of Captain Jean-Luc Picard, from its first mission (Encounter at Farpoint) to its ultimate destruction (Star Trek: Generations). It was only under the command of other Starfleet personnel for brief periods of time, for example Commander Riker usually took command during Picard's infrequent absences (such as his trip to have his artificial heart replaced in Samaritan Snare or his capture by the Borg in The Best of Both Worlds, Part I). Captain Edward Jellico took command while Picard was on a secret assignment to spy on the Cardassians in Chain of Command, Part I.
The Enterprise-D, being the flagship of the United Federation of Planets, was frequently a central player during amazing scientific discoveries, first contact with new alien races, and the occasional military operation (the TNG era being one of considerably less instability and violence than that of other Star Trek shows). Notable adventures include:
The Enterprise-D was destroyed more times than any other Federation ship, but due to various time-travel issues managed to stay in service for over 7 years regardless. It was finally destroyed by a Klingon Bird of Prey commanded by the Duras sisters, who modified Geordi La Forge's VISOR to allow them to find the correct weapon settings that would allow them to bypass the Enterprise's shields. Although their ship was destroyed as well in the battle, the Enterprise's stardrive section exploded shortly after the saucer section was separated to save the crew, causing enough damage to force the saucer to make a crash landing. The Enterprise was unrecoverable.
Somehow I doubt this will be the last ship to carry the name Enterprise.
— Jean-Luc Picard, Generations
Since no convenient time-travel loopholes that could be exploited were nearby, it was replaced by the new Sovereign-class USS Enterprise-E. Most of the senior staff from the D were reassigned to the E.
Some information verified by Memory Alpha