Restore fails in single mode because exclusive access cannot be obtained

We have a script that runs every night, this is the relevant part of the script:

---- Restore DB from file system
print ''
print '----------------------------------------------'
print 'Restoring Database: $(DbName)'
print '' 
use master
alter database [$(DbName)] set single_user with rollback immediate
restore database [$(DbName)] 
    from  disk = '$(BackupFile)' 
    with replace,
    move @datalogname TO @datapath,
    move @loglogname TO @logpath,
    stats = 5
alter database [$(DbName)] set multi_user
alter database [$(DbName)] set new_broker with rollback immediate
print '----------------------------------------------'

Most of the nights it works. On some nights it fails with the following output:

[04:00:29] :     [Step 2/7] ----------------------------------------------
[04:00:29] :     [Step 2/7] Restoring Database: MYDATABASE
[04:00:29] :     [Step 2/7]  
[04:00:29] :     [Step 2/7] Msg 3101, Level 16, State 1, Server MYSERVER, Line 83
[04:00:29] :     [Step 2/7] Exclusive access could not be obtained because the database is in use.
[04:00:29] :     [Step 2/7] Msg 3013, Level 16, State 1, Server MYSERVER, Line 83
[04:00:29] :     [Step 2/7] RESTORE DATABASE is terminating abnormally.
[04:00:29] :     [Step 2/7] Msg 1205, Level 13, State 68, Server MYSERVER, Line 89
[04:00:29] :     [Step 2/7] Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
[04:00:29] :     [Step 2/7] Msg 5069, Level 16, State 1, Server MYSERVER, Line 89
[04:00:29] :     [Step 2/7] ALTER DATABASE statement failed.
[04:00:29] :     [Step 2/7] ----------------------------------------------

Line 83 is the RESTORE statement. Line 89 is the set multi_user statement.

Why is this happening and how do we make sure it succeeds every time.

UPDATE

No explicit transactions are defined in the script. The script is run with sqlcmd from a file. There are several processes outside of the SQL Server VM that are polling the database regularly, and my guess is that one of them manage to butt in between set single_user and restore when this happens. It was suggested that it can be solved by temporarily enabling a rule on local firewall that prohibits incoming connections, but I’m wondering if there is a SQL only solution.

I think the best approach here is to restore a copy (always calculating new data/log file path names with date/time embedded to avoid collisions), then you can simply kick the users out, drop the old version, and rename the copy, without any delays associated with the start of the restore being a potential cause for letting persistent connections in before it can actually try to gain exclusive access:

RESTORE DATABASE [$(DbName)_copy] 
    FROM DISK = '$(BackupFile)' 
    WITH REPLACE, -- not really necessary, unless last night failed
    MOVE @datalogname TO @datapath, -- change this calc to include date/time
    MOVE @loglogname  TO @logpath,  -- change this calc to include date/time
    STATS = 5;

ALTER DATABASE [$(DbName)_copy] SET NEW_BROKER; -- before anyone can try

ALTER DATABASE [$(DbName)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE [$(DbName)];

ALTER DATABASE [$(DbName)_copy] MODIFY NAME = [$(DbName)];

If this still doesn’t let you through, you can either:

• use a loop to continuously try to kick the users out and drop the database, e.g.

  DECLARE @i int = 1;
  WHILE @i <= 100
  BEGIN
    BEGIN TRY
      ALTER DATABASE [$(DbName)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
      DROP DATABASE [$(DbName)];
      SET @i = 100;
    END TRY
    BEGIN CATCH
      SET @i += 1;
      PRINT 'failed';
    END CATCH
  END
  

• disable the logins and/or create a logon trigger that temporarily rejects everyone but you (or at least slows them down) – but be careful, it’s easy to lock everyone out

• restart the SQL Server service and simply rename the database in a startup procedure before anyone has a chance to connect – you could have such a startup procedure always enabled and it checks a flag you set somewhere, or the time. In fact the startup procedure could do all of the above logic, but only during your nightly update process.

Usually you should connect to the database before setting it in single-user mode in order to guarantee that you are the single user. EG

use [$(DbName)]
alter database [$(DbName)] set single_user with rollback immediate

But that doesn’t work here because you can’t restore over the database if you’re connected to it. The next-best thing is to use RESTRICTED_USER instead of SINGLE_USER

eg

alter database [$(DbName)] set restricted_user  with rollback immediate

Allows for only members of the db_owner fixed database role and
dbcreator and sysadmin fixed server roles to connect to the database.
RESTRICTED_USER doesn’t limit their number. Disconnect all connections
to the database using the timeframe specified by the ALTER DATABASE
statement’s termination clause. After the database has transitioned to
the RESTRICTED_USER state, connection attempts by unqualified users
are refused.

ALTER DATABASE SET RESTRICTED_USER

And of course you could also DROP the database and recreate it instead of restoring over it.