1. What version of Retrospect are you using? Have you tried applying the latest
Driver Update for your version?
2. In Retrospect 5.x/6.x's Device configuration list (Configure Tab > Devices), it should list the available devices in your SCSI chain. Assuming your SCSI connection is setup properly (unique ID, properly terminated, powered on), Retrospect should list what it detects and inform you if its internal driver supports it or not (denoted by bold typeface).
3. Retrospect's
device compatibility guide doesn't explicitly state whether the STD224000N is supported or unsupported. However, doesn't that also go by the Scorpion 24 moniker (which is listed as
supported in Retrospect Desktop for Mac 4.0)?
IIRC, Seagate's DAT/Tape products were acquired by Cetrance / Quantum, so you might want to check for support from them.
Grab a copy of Peripheral View (from VersionTracker or MacUpdate, if it's still there) to show what devices are connected to your SCSI/ATA buses. At the very least, Mac OS 9 should list all of the devices in the chain, otherwise, you may have a SCSI connection issue.