The Hopper will switch the the SD equivalent if the HD channel is getting brown outs or rain/cloud fade. The SD feed sends much less data and more forgiving of dropouts. I've seen it a lot during the rain popups lately. The only problem is when the overcast fade goes away, it will not automatically go back to the HD feed.
And also, if you are recording the program, you are SOL. It won't switch the recorded feed to the SD equivalent, it will just give you a lost signal message.