Allright, it's time to post a resolution to this as I've just gotten past the point of now rediscovering all my old drives via my new HBA card. I can't really say why my LSI 9300-8i didn't work and so my theories may be of little worth. A stab in the dark would be assuming SAS3 type devices are not as widely compatible with consumer grade motherboards and so no matter which board I used the card was not recognized at all on the hardware level.
To the happy news!
I've recently installed my newly arrived LSI SAS 9207-8i HBA card into a PCIe Gen4 slot on my ASUS PRIME Z790M-PLUS and I can happily confirm the drives are now discovered and the disk array is complete and ready to start up again. I have far from tested this setup to any extent but intend to in the coming days. Should anyone however be in the same predicament I was in I can now at least offer my step forward from that. Fingers crossed it's smooth sailing from here!
