Is it painful? Sure. But the pain doesn't last long and you'll probably be given some decent painkillers. It's just a few tiny incisions plus a bigger one for the port (my port incision is about 1-1/2" long).
Everybody's pain tolerance varies. I'm a real baby about pain but I found my band surgery to be far less painful than other surgeries I've had (dental surgery, a hernia repair in my groin, an abdominal hysterectomy, a breast reduction, shoulder surgery). The gas pain (from the gas they pump in during surgery) lasted longer than the incisional pain. My port area was tender for 2-3 weeks (it's hard to turn over in bed, or get in/out of a chair, without using your abdominal muscles), but it wasn't crippling. My husband took time off from work to "help" me after my band surgery and by the time I'd been home for about 9 hours, I felt like he was underfoot and needed to go back to work.
If you're a woman who's delivered a child (whether naturally or by C-section), you've probably experienced worse pain than band surgery.