A 12V deep-discharge lead-acid battery is typically 120amp-hours. That's 1.4kWh. Suppose you only 50% discharge it for longevity, that's 700Wh. I'll assume your box of batteries has ten of them, so a usable capacity of 7kWh. 1kWh = 3.6MJ, so 7kWh = 25MJ

How high is your roof? Lets say 10 metres. Potential energy U = mgh. (m=mass in kg). h=10, g~=10, so U=100*m.

So, to equal those batteries, assuming 100% efficiency, the mass of water needs to be 250000 kg, or 250 tonnes. Probably going to need to reinforce the loft.

So 'roof' is clearly out of the question. Now let's look at basement. I install a 4x4x2 meter tank, then drill down 100 meters to create another such tank. 32 tonnes lifted 100 meters gives me 32MJ, perfect.

Now how expensive is it to build a water storage area underground? Is this a 50 thousand dollar project or a 50 million dollar project?

If the ground water is at the right depth, you would only need to drill a well. So this should be practical somewhere, at least.

Your 100 meter channel is going to get flooded.

That's your biggest objection to the idea? A 100 meter waterproof pipe is the easiest problem to solve here.

Is your house 100 feet tall or taller, and I hope your whole house is made out of structural steel.