The only time I've felt it was significant was in Ocarina of Time - perhaps I'm alone in this, but the musical silence of the room and the soft echo of the footsteps really set the scene for me, and to this day I always slowly walk - not run - up the steps to the pedestal. That the actual drawing of the blade is met with the light show and the musical crescendo is even more spectacular.
In Wind Waker I think it was diluted in part due to the blade's depleted state. Restoring its power was neat, but it made the initial acquisition a lot less exciting. Similarly, Twilight Princess placed the blade's acquisition at the end of an intense sequence involving Link's apparently permanent wolf state, Zelda's sacrifice for Midna, and a puzzle-filled Lost Woods. Getting it was more of a relief, as Link regained human form, than it was an epic moment.
Skyward Sword... this is actually one of my largest gripes with the game. This is the very first time anyone has ever drawn the Master Sword - it was created by the player in the game. That moment should have felt incredible, especially given the blade's storied history throughout the other titles in the series. But no - it was incredibly underwhelming. There was no build up, there was no anticipation, no stirring music - just, "Oh hey, the third flame turned it into the Master Sword. How about that?" A complete let down. Needless to say, that disappointed me on a profound level.