As a former wedding photographer, I typically went by the number of pictures I was willing to trust to a single card. Since file sizes went up at roughly the same time as storage sizes, I ratcheted up the maximum size SD card I'd use.
It used to be "won't use larger than 2GB". Right now it's around 8, but will likely become 16 in a couple of years.
Higher end DSLRs (including mid-line ones like the Nikon D7000) support two SD card slots and "backup" mode where both cards are written to with every picture. That's probably enough for most paranoia.
This isn't targeted at the DSLR crowd. It's meant for videographers shooting 1080p raw or 4k video. This card will only hold around 5 hours of raw 1080p video for me.
I am using at least two fast 64GB SD Cards in copy/mirror mode on my Nikon, and back up immediately to Blu-Ray after a photoshoot/hyperlapse/video. I would be absolutely happy to use 2x512GB!
Its not about backup. Its about environmental stress. Photos are taken all over the place - yards, beaches, malls, in good weather and bad. SD cards are fragile. Its about, If you spend a day shooting and one of them doesn't make it back to the studio, how much have you lost?
Are they, though? Because I use SD cards for photography and have for quite a few years and I take them not only to yards and beaches, but mountain peaks, spelunking in mud caves out in the desert, etc, and I have never had an SD card break on me either physically or in terms of data loss.
To be fair, though, for photos only (no video) even with high-res uncompressed RAW photos I can't imagine anyone practically needing SD cards anywhere near the 512 GB size. That's a lot of photos...
YMMV, I guess, maybe I'm totally lucky, but I have yet to personally have an incident which makes me worry about the robustness of decent quality SD cards.
I mean it does cost $35, but it's pretty much exactly a magic remote backup machine (that can also charge your gear!). Or you can spend more cash and get one with an integrated hard drive and need no other devices.
It used to be "won't use larger than 2GB". Right now it's around 8, but will likely become 16 in a couple of years.