When the water shuts off, you open a faucet upstairs,
then go down into the basement with a bucket, and collect
the water you need from the sill-cock on the pressure tank.

I was hoping for a solution that could be automated the way a transfer
switch and generator work for the electrical supply.

There are human/behavioral problems with that, in conjunction
with stored water. If you don't make getting to the water
at least a little bit of a pain, people will continue their
normal use-patterns, which will run through your stored capacity
in a hurry.