My assumption is that the case usage of the read/only target server would be mostly for reporting purposes, having only a limited number of infrequent users who won't freak out if they occasionally get a dropped connection.

So, immediately after querying no active connections, the job could set the database to RESTRICTED_USER mode to keep it locked down while performing the restore, and then immediately set it back to MULTI_USER when the restore completes. I'm just talking off the top of my head and obviously the process would have to be tweaked to fit a specific usage scenario.

However, the following is a basic example of what I'm proposing. It would be part of a job scheduled to kick off every X minutes. I'd expect the target to get restored at irregular intervals, maybe skipping several cycles between, if that's suitable for a given scenario.

IF ( [confirmed no active connections] )

BEGIN;

ALTER DATABASE TargetDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE TargetDB ... ;

ALTER DATABASE TargetDB SET MULTI_USER;

END;