Two options really, train or road. Train was the the only option when I first lived there in 2001 but now the highway is a faster option.
Train leaves at 22:00 from the Hanoi station and you will get into Lao Cai at around 05:30. There are a number of operators, but the cabins are pretty much the same. Not a great ride if you are a light sleeper, as the carriages rattle a lot. Once you arrive in Lao Cai, there re minivans, taxis, or the public bus to take you up to Sa Pa, the drive will take about 40 minutes.
The other option are the frequent buses that go directly to Sa Pa. I personally prefer the “VIP” minivans, safer and more comfortable than the sleeper buses. There are night buses as well, but being on Vietnam’s roads at night is not so great. Eco Bus leaves Hanoi around 06:30 and you will be in Sa Pa around 13:00.