If I line in the sound from a video game console, can I detect when a sound is played?
Theoretically you could separate your desired sound by ICA method or other blind source separation methods from the background music, then design a matched filter by your separated sound. The matched filter have high response when the desired pattern (which is your desired sound) exist in the input even when it buried in noise (background music).

The matched filter has acceptable performance but i'm not sure about the ICA and other source separation methods and highly depend on your input signals (desired and background sound).