There are many games that have managed to do this : notably Chrono Trigger and Final Fantasy 6 for the super Nintendo system.
The most important thing is writing a story that has themes and situations that players can relate to, and writing characters that players can empathize with.
Experiences of loss are usually the easiest to use, such as loss of a loved one. Other examples can be characters who overcome great difficulties. The real issue though is writing a way that makes the problems relatable, believable and engaging. Secondly, the player has to be emotionally invested in the characters involved.
It's hard to say exactly what to do, but you could start with some of these:
- Don't put the supposed emotional event early in the game before the player has had a chance to connect.
If the event has to be early on for story reasons, consider having the scene related later in the game through flashbacks etc.
- Allow the player a chance to shape the character with dialogue choice, as this can help the player feel more invested in the characters.
- Don't write characters that are hard to empathize with if you plan to have the player feel sorry for them down the line.
A common rule is that "the bigger the douchebag, the more horrible things has to happen to him or her, before anyone cares".
- Use tragedy. Most people empathize with people who have bad **** happen to them which they are powerless to do anything about.
The more self-sufficient a character is seen to be, the less we're going to feel sorry for them, and the more likely we are to feel that they had it coming, or should have been able to prevent said **** from happening.
- Consequences. When bad stuff happens, it influences many people. Showing how the world changes, how many characters are bummed out by what happens, gives a sense scope to the tragedy and makes it seem even sadder.
These are just some off of the top of my head though.