This circuit idea is OK if the "application" can live with a momentary dropout of its supply voltage during the changeover. There are two things that cause this. First the 12V primary power must drop to less than about 2V or so before the 2N7000 N-FET will turn off. Secondly the relay contact switch over time will take a millisecond or two.
If you want uninterrupted power to the "application" during the changeover then you could consider several alternatives.
1) Add a comparator circuit that detects when the primary 12V has only dropped a small amount (such as to 11.4V) and switch the relay before the primary rail fails. Some charge storage capacitors on the "application side" can minimize the voltage droop there while the relay contacts are switching over.
2) Use a pair of power MOSFETs to switch the voltage instead of the relay. These would switch much faster than the relay and the "application side" hold up capacitors can be much smaller.
3) Use just two power Schottky diodes to OR the two power sources to the application. No switch over control logic required but you do lose some voltage from the sources to the application power rail.
NOTE: In your schematic you need to add a back biased diode across the relay coil so that at the time the the 2N7000 FET turns off the inductive kick of the coil will not take out the FET.
Tino proposed to change his circuit to use the two diode approach as follows:
He questioned as to whether the Diode D1 would still be needed. Unfortunately the diode would still be needed because of you look closely at the data sheet for the IRFU9024 you will note the body diode of the MOSFET....
You can quickly see the problem!