Spray foam insulation for large areas with a closed-cell foam tends to be a job performed by professionals. They will mask off the area being sprayed, and bring in a bunch of specialized equipment to do the job fast. The result doesn't require a separate vapor barrier since closed cell foam is a barrier. It does have the downside that any future repairs or renovations will require a significant effort to remove the foam.
The main reason to spray foam an attic space is to make the area a living space and eliminate the need to ventilate under the roof (typically done to prevent ice dams). To do this, they would spray directly under the roof, between the rafters, rather than between the joists. Otherwise, the traditional solutions are blown and fiberglass insulation.
Blown insulation requires the vapor barrier to be put down first, soffits to be protected so they aren't covered over, and then a machine can be rented to blow in as much insulation as you like. The advantage of blown insulation is speed, fewer gaps in the insulation, and it's easy to add a few extra inches. If you ever need to remove a ceiling under blown insulation, it becomes difficult since you first need to remove all the insulation unless you want it falling into the room below.
With fiberglass insulation, the vapor barrier is often attached, but it needs to be stapled to the joists, which is difficult to do from above. It's much easier to temporarily move out of the way for a repair or renovation, but it's also easy to leave small cracks where moisture and heat can escape.
If your problem was that you had wind blown rain entering from your soffits, then you may need to add a baffle or some other storm resistant vent that blocks the wind driven rain while still providing fresh air all other times.