I've had never any problems with upgrades and never lost any files.
You should make a backup — or better, make a bootable clone of your current hard drive:
- Buy an external hard drive
- Install Snow Leopard on the external drive.
- Reboot into the clean external Snow Leopard drive, migrate over users and apps and run all updates. Run from that external to be sure it's a workable copy of your internal drive.
- Disconnect the external drive and put it on the shelf.
- Reboot back into your regular internal hard drive and proceed with the upgrade to Lion.
- Download Lion and run the installer!
Until you're certain that Lion works perfectly, keep the clone around - several months is usually enough time. If you ever need any files from it, just attach the drive and drag-and-drop them. If something goes really horribly wrong, you can boot into the clone, and then clone it back to the internal drive (you'll lose any changes made since you installed Lion). This second drive is really valuable for when you discover three months down the road that one seldom used piece of software won't work on Lion. You can reboot into Snow Leopard where everything still works and get that one task done. It's a great lifesaver and means you can start using Lion rather than checking that every little thing works perfectly right after the upgrade.
EDIT:
Thanks Trombone for editing my broken-english post, but please, never add your own ideas into my post. My solution uses pure apple tools, so why bother with SuperDuper, when you can do it with nice clean install and Migration Assistant.
I never recommend any third-party products when things can be done with default Apple SW. Of course, you can prefer SuperDuper, it is your way - but not mine. Please add another answer explaining how to use SuperDuper or other awesome tools so that people can have a choice. Let's keep this solution needing no additional tools.
Yes. As long as you're running the most up-to-date Snow Leopard version (10.6.8), you can directly upgrade to Mountain Lion, as detailed on Apple's 10.8 Upgrade Page. Just download from the App Store and you should be good to go.
Best Answer
If the Mac Pro is old enough, then it cannot be upgraded beyond Lion. Check the listed requirements... My old Mac Pro 1,1 was in this category.
See the chart at the bottom, "latest supported OS" HERE