We have a SQL Server Agent job for loading dumps in case we need to restore from our daily backups. The first step in this job is supposed to kill all connections to the database. However, we frequently encounter a problem where somebody on the network has left an application running which will try to reconnect to the database if disconnected, and so after this first step has completed, this application will reconnect. Therefore, when the restore step comes it fails because it was unable to gain exclusive access to the database.
Usually I would use
ALTER DATABASE [db] SET SINGLE_USER WITH ROLL BACK IMMEDIATE to ensure exclusive access, but unfortunately in this case the application reconnects sooner than the restore begins, and so the restore script is unable to access the database at all.
In order to get around this, I thought that it might be possible to disable all TCP connections during the restore so that only the locally running restore script could access the database. So, my question is is there any way in T-SQL to disable/block TCP connections? Or perhaps is there a way to configure the SQL Server Agent to do this when it starts the job?