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:
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.
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.
This Post Has 2 Comments
Thank a lot. This terrible problem was solved.
Classic “update” to MS tools. Thanks very much for posting this. Much quicker than my approach on my dev box of restarting the service!