This sounds very similar to what I have to manage from a data standpoint — I work at my company HQ but either travel and shoot or hire out and collect footage from lots of different locations. Meanwhile frequently shooting talking head and safety videos etc. at the office.
We outsource IT and because they charge a ridiculous rate for server space, backing up there was not an option. Working with one of our in-house IT directors we came up with using a NAS RAID system for video/photo project data. I recall the conversation with him that it is sort of uncharted territory to have to deal with that volume of data, at least from a single person or department, at least within a corporate IT environment.
We ended up going with a 4-bay Synology NAS raid system, which is on the local network and I have mapped to my editing PC. My PC has about 3TB of space which I use for ‘current projects’, then the NAS is split into 3 volumes (number of volumes can be customized):
Vol 1: Current Project backup — backs up relevant folders from my PC either continuously or on the click of a button
Vol 2: Old Projects — using PP project manager, I collect & copy or transcode a project once finished and I am fairly sure no changes are coming into a folder for archival on this volume. As a side note, I subclip everything in Prelude before editing so during this archival process, sometimes I go back and export the subclips out of prelude into a raw media archive depending on what it is, because in some cases I didn’t use all of the clips in the PP project but may need them in the future.
Vol 3: Archive — this is where I save most of my offsite footage as sort of a bank, due to it very likely being used repeatedly. Although I’m starting to outgrow it and have had to put some of this in Vol 2. I also zip up any really old projects from Vol 2 and save them here, ones that I am basically sure that I will not need to access any of the assets for under normal circumstances.
All of these also get backed up onto portable HDs periodically, both taken home with me and sent with a colleague overseas occasionally to prevent major data loss during a natural disaster or fire. Synology has a pretty robust app for managing all of this, and while it was a bit of a learning curve to setup is pretty easy to work with now.
It’s not perfect but works pretty well I think. If I could start from scratch I might consider having a smaller raid on my desktop instead of using internal pc drives. But overall it takes at least some of the effort out of managing the data. Personally I would loathe to have to constantly be switching between external drives and copying data back and forth, and would be very paranoid about losing something should a drive crash (though I know you said it’s backed up).