TLDR: there really isn't any atm if all you want is "make and forget" (as in: create a cold storage type backup and throw it into the basement or whatever for 40 years untouched).
Current technology is crap and pretty much anything requires maintanance at least every few years (that is unless you have enough money to afford your own archiving datacenter which I am sure you don't).

Tape drives aren't JUST for enterprise market. They are INTENDED to be used there. But some individuals use them too.
Tapes themselves aren't THAT expensive. What is expensive is a drive for them most of the time (and I mean - you cannot buy just ANY - you have to squeeze some money to get a good one).
But there is quite A LOT of issues with tapes.
TLDR: If you want to even consider tapes then you have to make EXTENSIVE research in regards to if you would even BE ABLE to utilise them (things such as SPECIAL CARE STORAGE come into play).

Using just any HDD for a backup is a very sh*tty idea.
I could go into great lengths on explanations of that.
Drives fail. You can even be unfortunate enough to have your triple redundant backup to fail all at once.

I said some stuff in regards to backups in this thread
https://www.gog.com/forum/general/gog_library_offline_backup
tho when I read it now my language got broken here and there :P
Also, that was a very glorified TLDR, a tip of an iceberg if you will.
Data archiving is very complex case. I have a lot more to say on the matter. But I would have to fill up multiple pages of a thread to spit it out so I don't know how to handle that.

I hv been in both (posted in one) and all I can say is that they are by all means incomplete and don't paint a good picture for not data-archivist kind of people (ergo for average person).

Normal discs (non archival grade - archival grade is a special type - they are far more expensive and generally hard to come by on general market) last anything between 5 - 20 years depending on quality and storage considerations.
The M-disc was specificly designed for exceptionally long storage. They are specsheeted for 1k years longetivity based on synthetic testing (before someone makes a fuss: humanity makes almost no "1:1 time passed" tests these days, synthetic tests are a thing for a long time, they are designed to simulate longer periods of time).
They can survive harsh conditions for longer.
They generally are a good thing but there are some caveats:
- first of all company originally responsible for them basically bancrupted - that led to technology being ALMOST abandoned
- due to above there is close to no companys manufacturing those now, mostly Verbatim, I think also Milleniata and some other name I don't remember
- even when this didn't look like that they were already pretty expensive - and now it's even worse. DVD M-disc is COMPLETELY not cost effective. BD 100 is absurd price
- availability NOW is a PROBLEM
It's a good storage media. But unless something changes that technology may become abandoned due to not big enough adoption (which was because of formats competition and some industry slack).

(to all people I quoted here):
I can TLDR this (I could write multiple essays on this case):
- don't use pendrives - they don't last and are completely worthless, used controllers are complete sh*t in 99% cases (they fail randomly), they have also no shielding (prone to bit flips, degausing and demagnetisation), also ridiculously overpriced
- SSDs - more immune to G shocks than HDD (but only in specific ways, ironically) - a burden unless you connect them to power every so often (I would suggest not risking it and not waiting more than 10 months) due to demagnetisation (industry tends to be silent about this major problem), expensive (still too expensive), no shielding in 99% models (prone to bit flips)
- HDDs - physically fragile (platters can get stuck, scratch, also heat prone), prone to degausing (after all it's one of THE methods for secure data wiping specificly HDDs), if not connected to power for long read/write heads can get physically stuck on powerup (don't think you can just toss a HDD into the closet for years and it will be just fine, it may be, but it may very well NOT BE), heavy, CHEAP, can corrode, problems with spare parts if it fails for example 15 years later, generally don't live above 10 years (they can but it's a very heavy risk, generally should be replaced at max every 8 years, ergo recurring cost)
- holographic discs ( https://en.wikipedia.org/wiki/Holographic_Versatile_Disc ) - a technology that never got addopted
- CD - not even remotely suitable for data archiving, and before someone goes "but my audio CDs last over a decade" - it's a FACT that blank discs are of COMPLETELY different grade and quality than what is used for pressing CD albums. Contains organic layer (ergo: trash in less than 15 years in case of recordable discs).
- DVD - low density (ergo LOTS of DVDs used for even small backup), low cost (but still too high), low quality, better than CD but still organic layer (ergo doesn't really last long), TLDR - don't use unless you can get archival (literal gold reflective layer) or "close to archival" (Verbatim AZO *or better*) grade
- tapes - hard to obtain, expensive drives, prone to humidity, heat, physical damage, cumbersome on filesystem level unless you use *NIX with special tooling, unknown maintanance required
- BD LTH - TRASH. Period.
- BD HTL "random manufacteur" - lottery, not very high quality, needs replacing max every 15 years (median hard to calculate, probably around 6-9 years to be safe), cost is random, more durable physically than DVD
- BD HTL "normal" quality (chinese Verbatim, other chinsese manufacteured, lower quality taiwaneese, etc) - average quality, cost is a lottery, needs replacing max every 15 years (a guess), more durable physically than DVD
- BD archival grade - rip wallet, good quality, hard to come by, may require VERY expensive archival grade drive to burn, unknown amount of storage time and maintanance
- BD 1 TiB prototype - "density effective", still in prototyping stage, this is if you want to rip your wallet some more :P

The best would be some sort of 3 dimensional data media that is non magnetic and non organic. That would last even few k years if properly made.
Some attempts at that are being made https://en.wikipedia.org/wiki/3D_optical_data_storage .
Most notably
https://en.wikipedia.org/wiki/5D_optical_data_storage ( more of a "meh" for me personally, this would be ridiculously expensive considering who is involved )
https://en.wikipedia.org/wiki/5D_DVD ( seems like a promising standard, but will take years to show up ).

Right now for average person the best idea may be to try to get:
[*] BD HTL specificly JAPANESE MADE - very high quality, cost is in check (at least for 25 GiB media), would probably last for 20 years or more, problems: exceedingly hard to get (some companies making them shifted production to offshore thus lowered quality drastically, others are for JP market only and there are issues exporting them). Examples: *some* Verbatim models, *some* Mitsubishi models (JP market only), Taiyo Yuden JP made batches, JP made Sharp batches, some others.
If you want them you have to really crawl forums for specific disc IDs and VERIFY THE BATCHES for authenticity (you need to read discs debug data to uncover real manufactuer AND model - it's a norm that A.There are fakes around B.Various models are sold under same packaging with no visual difference even on disc itself - remember, ONLY debug data can tell you REAL info).