How can I prevent a non-rechargeable backup battery from inadvertently being charged?
As well as wanting very low voltage drop in battery backup mode, in the comments you say

the mains supply's output is 9V nominal, and I want to have as much of
that available as possible

The obvious solution is to combine the power sources through 2 'ideal diodes' which have very low voltage drop. If a dedicated IC is not acceptable then you could use discrete transistors instead. The circuit is similar to the one shown here, but with higher value resistors to reduce quiescent current draw.



simulate this circuit &ndash; Schematic created using CircuitLab
R1 (R3) is made a lower value than R2 (R4) to make the Base-Emitter voltage of Q1 (Q4) slightly greater than Q2 (Q3), causing Q2 to turn on and keep the FET turned off until the 'ideal diode' drops a small 'forward' voltage. To ensure that this works reliably the transistors should be thermally coupled and well matched.
For a one-off circuit you could use unmatched transistors, but test the circuit with a small load current and swap them around to get the highest 'forward' voltage drop across the FET. This should ensure that it turns off promptly with negative bias, though in your application it may not be critical.