Once it's over-written by the operating system, to all intents and purposes that file can be considered to be 'gone'. I say 'to all intents and purposes' because technically there is the slimmest of chances, but to discover whether that is the case and to recover the file if it is, can be non-trivial, to say the least.
The recently worked file (let's call it 'new') sits somewhere on the hard disk. Then the backup file (let's call it 'old') is copied to the hard disk. The directory entry for that file is updated by the OS to point to the location of the old file, and the OS marks the space taken up by the new file as empty (available) space, so you can't see your new file anymore.
Typically, if the old file is smaller than the new file, it is dropped right on top of the place where the new file used to be. So the new file is obliterated. But if for some reason the OS has put the old backup file somewhere else on the hard disk and nothing has yet been written into the space that the OS now treats as free and empty (where the new file used to be) then you can sometimes use recovery software to get that file back.
This free tool has had some good recommendations from users of this forum (I've never used it though): ADRC Data Recovery