I will share with you my experience and methods about bringing tears to my players' eyes. I find that I'm quite successful at it, and there are things the other answers have not touched on that I feel are very core to creating drama.
Conduct a Session 0
The most important bit that lays the foundation for everything else is that you must hold a Session 0 and tell your players that this campaign is going to be serious and dramatic. You need to get your players' buy-in that they want to play this kind of game.
Unless everyone is in agreement that they want to participate in a more narrative campaign with dramatic elements, and that they're supposed to participate in this story as well, you really have no guarantee that any of this is going to work.
So hold a Session 0 and get their buy-in. If you can't, then don't push through with it or find a different group that is more into your kind of game (if possible).
Warming up
First, recognize that an NPC is just like a bench. NPCs are just fictional people who are unremarkable in that they cannot affect your players in real life at the moment of their introduction. Your NPCs are just like any other NPC ever made -- except for the meaning your players give to them.
Just like the bench in the video, your NPC is given life by what the players bring to the table. Not you -- the players. You are giving them a bench, but it is just a bench like any other. The interaction your players do with that space is what gives value to that interaction, and that is what differentiates that bench from every other bench.
Why is this important? Because it is a metaphor for every real life interaction we do, everyday. The things that are important to me are important because I say they are important, and I give them value. These same things may not be important to you, but you have your own set of things that you value. Their value comes from the meaning you assign to them, rather than any intrinsic property your things have.
Once you recognize this, you should also make the leap to the conclusion that any NPC -- family member, stranger, savior, or damsel -- can be assigned this value. It doesn't matter what they are because NPCs are just like benches: they are thoughts in your head; they have no intrinsic worth; they gain importance by what your players bring to that interaction.
Introducing a valuable NPC
Appeal to player emotions
Just as you cannot force me to care about a random bench, you cannot force a player to care about a random NPC. Even if they were their character's mother, father, or child, they are still fictional people. You have to encourage your players to assign value to this otherwise unremarkable NPC, but you cannot force them to do so. So the secret is in knowing what your players -- not the characters, but the players -- value. And then, align those with the characters.
Roleplaying is human interaction. If you want to create drama, then you have to place humans first, characters second. And then you have to make sure to align the human values with the character values. In this way, when you introduce your NPC, you are appealing to the emotions of both the players and their characters.
Practice your acting
People know what other people are feeling on an intuitive level. When someone is sad or angry, it comes off as an aura you can detect. Other people are good at hiding this aura, others are more obvious. But the key is, anybody can know that someone else is feeling some kind of strong emotion.
The best way to show the players that this NPC is important is if you can show that the NPC is important to you, the narrator. This isn't to say you must treat them as a DMPC or anything like that, but if you want to optimize for drama, you must care about the fate of this NPC on an emotional level.
How to do this, you ask? Empathy. You must empathize with the plight of this NPC, and when the NPC speaks through your voice, you must feel the weight of your words. You must act, in the sense of acting in a play. I'll give examples of this later.
The scene must be set for drama
You can find touching moments in any scenario, but to optimize for drama, the moment must be geared for it. This is a difficult and non-trivial thing to do, though, because when we think of "dramatic" we think of throwing sad things at the audience. This is actually not the way to go.
See this video about how Pixar makes you cry. You don't throw sad music over a sad scene and expect tears: you throw happy music over it and let the sadness play out. Why? Because the conflict between the scene and the music creates tension, and tension pulls at the heartstrings of your players. When the tension is too much, you can't hold back your tears.
This is also why Adele's Someone Like You makes people cry, especially when it was first released. She makes use of "appoggiaturas" or "grace notes." These notes are sung in contrast to the overall melody of the song, and these departures are jarring to listen to, creating tension. However, she then returns to the "correct" melody, relieving the tension. This is when you start to cry. The release of tension is cathartic.
So how do you employ this in your games?
Play happy music. In Finding Nemo, as the linked video points out, you listen to a happy melody celebrating familial bonds while Marlin holds Nemo's egg close to him, a father hugging his last surviving child. The tension overlaid with the sadness of the moment adds gravity to the scene. Find something like this and make use of music in your games.
Even without music, find a moment of tension in your game and amplify it. I will give an example of this below, but the idea is your players must make a decision that contrasts with their beliefs and values. This dissonance will really hit them hard, and you will find your players teary-eyed.
Optional: Give the scene a purposeful resolution
Most scenes you see will follow the dramatic structure. You have a build up/exposition, then rising action, followed by the climax or the point of highest tension, rapidly falling action, and finally the dénouement or resolution. You expect the tears to fall during either the climax or falling action. But -- and this is really important -- the drama does not end there.
To achieve the satisfying, dramatic impact you are looking for, you must follow through with a resolution. And if you believe in Pixar's 22-rule approach to storytelling, you must always keep in mind that the story must have a purpose. That is, you must know the answer to this question: why must I tell this story?
The answer should not be "because it's cool, fun, or dramatic." It can be, but that makes for a very undramatic scene. Instead, try to answer it in a manner like the following:
To explore the human condition
To teach the virtues of cooperation over pridefulness
To come to terms with the death of a loved one
To overcome challenges in pursuit of your passion
Why? Because it makes the scene and the characters of the scene more sympathetic. It enables human connection, which enhances that scene even more. While having a dramatic moment for the sake of drama can be good, you also risk making it fall flat after all is said and done. But if you support it and ground it in a resolution that is propelled by a purpose -- a lesson, a value, or a message a la Pixar -- you can put a very satisfying ending to that scene or story that your players will remember.
Case Studies
Watch these sad videos (warning: tear-jerky videos ahead)
You may not cry watching all of these videos, but it's important to understand why they can evoke emotions in others. Then you should apply that knowledge to construct a sad scene.
The above three videos capitalize on vulnerability. And each of them is sad in their own way. The last one is a true tragedy. This could be something you might not be comfortable with running, because showing a true tragedy to an audience is tragic for the storyteller too.
But even tragedies have silver linings. In the case of the third video, if you are able to simulate this level of drama in your games, then you can use this to punctuate the story with a very powerful and meaningful message: one about the importance of human life.
So, do not be afraid to explore drama and tragedy to any level. So long as you know why you're telling that story and deliver that message through your storytelling, it will work out.
A story about a stormtrooper
Here's pulling an example from a more famous DM, who seems to have created a dramatic scene by accident. This is the story of a stormtrooper's death. This is an example of the party making a decision that goes against their values -- an example of internal tension, and an example where you can create dissonance without using music. The link starts at the relevant part of the video at 11:52 and the story ends at 13:58. It's a really good story to learn from.
A personal anecdote
I've recently made my players teary-eyed in the first 15 minutes of a session I ran. I gave one of the characters -- who had been stolen from his family from a young age -- a video recorder. In that recorder, he found one video per year that he had been away. And they were all recorded on the same day of each year.
They played the first video and I opened with a lighthearted scene: the father adjusting the camera, not figuring out how to do it properly; a "dad moment." This sets the happy tone, therefore letting their guards down to allow me to punch them with emotional tension.
And then his mother comes into frame. She calls him by a nickname the player has never heard yet before, telling him that they're looking for him and they won't give up until they find him. But it is his birthday today, and it didn't feel right to let it pass without a celebration. But, a party wasn't right, either, and so she wrote him a present instead.
And the mother put her fingers on the grand piano and started playing a song. This is when I played Nemo's Egg.
I also acted for this scene. It was important that they understand the loss of a mother looking for her child, and the love she had for him that she wanted to bring back that time they lost. So when I had this emotion down, I spoke with the voice of someone holding back tears. And then actual tears followed.
There was not a single dry cheek this session. I felt accomplished for pulling it off.
So how do you make an NPC valuable in the space of 2 days?
You can make him important in the space of 10 minutes if you plan it right. You must first make the NPC more life-like and human. And then introduce the dramatic elements to fully flesh out the interaction between the players and the NPC. If you do this by employing the advice I said in "Warming Up", then you should:
Remember that NPCs are benches: any NPC you make, even if it's the long-lost parents of your player's character, the final heir of the Lost Kingdom, or the most powerful Wizard in the land, will not be valuable to your player out of the box. That NPC is just like a bench: unremarkable and without intrinsic worth. Remember that their value comes from the players assigning that value to them, and it is not in the power of the DM to force players to make that value judgment.
Appeal to player emotions: give him a quirk that defines him. It can be a funny voice, a signature gesture, a dominant personality trait, etc. Write it so that it appeals to the emotions of your players and their characters. Do not be afraid to use tropes for this: the jerk with a heart of gold, the caring mother, the man with a funny laugh. Whatever it is, the trait must stand out. This is important because when the NPC dies, the loss of this trait will create a palpable void
Practice your acting: the difference between a villain and a supervillain is presentation (at least, according to a certain Megamind). You must act dramatic to create drama. You don't need to be able to cry on cue, but you should be able to personify the valuable NPC without being shy about it. And the scene must be just as dramatic for you as it is for them
The scene must be set for drama: setup the music, follow the principles of creating tension, create dissonance and then relieve that dissonance
Give the scene a purposeful resolution: as part of designing the scene, ask yourself "why does this story need to be told?" and have your resolution or follow-through be informed by this answer
Watch the Case Studies: watch sad videos, internalize the reasons what makes scenes sad, get yourself into a state where you can draw on that emotion before you run your scene
Hopefully, in following this, you will find success in any given dramatic scene you want to run. Happy gaming!
