Why Can’t I Close Existing Connections on Restore?

This morning a customer pinged me with what should be a simple issue:  While restoring a backup to a test environment, the option to Close existing connections to destination database was greyed out. I was able to reproduce the problem and the solution is a bit odd so thought I’d throw this out there. 

The Problem: Can’t Close Existing Connections

Typically when restoring a backup using the SSMS GUI, you choose the device, then change anything in files or options. That is the order the tabs are in, as you can see:  

Close Existing Connections on Restore

However, in this case the option to close existing connections is greyed out and not available.

The Solution

This turns out to be a quirk of SSMS (or at least this version). After choosing the device you are restoring from, that option becomes read-only. But it retains the value it had in it. So to solve this problem, one must choose the option first, then choose the device. Which seems a bit counter-intuitive but there you go.

 

Close Existing Connections on Restore

Changing the order up a bit was the trick in this case.

Note that this is one of the many reasons we prefer to use scripts over the GUI for most things we do but we also recognize that for many the GUI is much faster and easier. If you find you can’t close existing connections on restore, this could be what you need.

Please share this

This Post Has 2 Comments

  1. Trần Lê Dũng

    Thank a lot. This terrible problem was solved.

  2. Paul N

    Classic “update” to MS tools. Thanks very much for posting this. Much quicker than my approach on my dev box of restarting the service!

Leave a Reply

Related Articles