1

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?

  • Is ALTER DATABASE [db] SET SINGLE_USER WITH ROLL BACK IMMEDIATE the first part of your RESTORE script, in the same transaction? I've never had this fail... – Kevin3NF Mar 21 '17 at 12:44
  • Ah no. There are several steps, the first step is to kill connections, the next step is the restore. – Luke Mar 21 '17 at 13:04
  • job steps? there is time in between those that allows a connection in. put the Alter as line one, Restore Database as line two in the same step – Kevin3NF Mar 21 '17 at 13:06
  • 2
    Does your application have sysadmin privileges? If not, why not try ALTER DATABASE [...] SET RESTRICTED_USER WITH ROLLBACK IMMDEAITE. That will only allow members of the sysadmin role to access it. I've had to do this before in production and this is how I got around it. – Kris Gruttemeyer Mar 21 '17 at 13:18
2

If you disable TCP connections to SQL Server, SQL Server Agent might also be disconnected. However, that could be mitigated, and is by default, by enabling the Shared Memory Network Protocol for the server.

Having said that, disabling TCP connectivity is a bit like using a sledgehammer to kill a fly.

Normally, altering the database with SINGLE_USER WITH ROLLBACK IMMEDIATE will suffice; however occasionally you run into an application that is especially aggressive at reconnecting, and will reconnect to the database once it is in single user mode, but prior to the RESTORE DATABASE commencing.

One work-around I've used in the past for this is to put the database into RESTRICTED_USER mode, as in:

USE master;
ALTER DATABASE [xyz] SET PRIVILEGED_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [zyx] FROM ...

I've seen this not work when the app connects as sa or some other sysadmin user. In that case, I've done the following rather more aggressive action:

USE master;
ALTER DATABASE [xyz] SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [xyz] SET ONLINE;
DROP DATABASE [xyz];
RESTORE DATATABSE [xyz] FROM ...

Having the above code in separate SQL Server Agent Job steps is problematic since each statement is executed in a different batch, which takes time to process. Keep that code in a single job step to further reduce the ability for a connection to succeed in between statements.

  • :-) Good point, though there would be no question as to the dead-status of that fly ;-) – Solomon Rutzky Mar 21 '17 at 17:42
  • it would certainly be dead! Although I'd hate to see the other damage caused by all the attempts to hit the fly. – Max Vernon Mar 21 '17 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.