Why is "close existing connections to destination database" grayed out on SQL Server 2012 Management Studio?

Question

I am normally using SQL Server 2012 Management Studio to restore a SQL Server database from a "bak" file. I do this by overwriting an existing database. In the "Options" page, there is a check-box labeled "Close existing connections to destination database", which I mostly check because the target database is always "in use", even if I just restarted the MS SQL Service and I am sure that there are no applications using it.

Anyway, on my client's computer, I saw that that checkbox to close existing connections is grayed out without any information. How and why does this happen? A google search didn't yield any results.

Suffer

Are you trying to restore a database that doesn't exist yet? Or that is currently offline? – Casein

I edited my question to be more clear. Nope, I try to restore a database over an existing database. By the way, I am able to the restore the DB if I take the target DB offline first. But I never had to do such a thing before and it is a mystery to me, why "close existing connections" checkbox is disabled. – Suffer

I think it's a bug with the UI. After playing around a bit I managed to get it greyed out but still 'checked'. Performing the restore seemed to close connections this time. – Midwifery

Answer 1

I had the same issue. I simply checked the Checkbox BEFORE choosing the source to restore from. After choosing the source the CB was grey but checked and the restore worked fine.

Sergeant

might be good advice. unfortunately my project is finished and I don't have access to my client's computer, so I cannot verify this solution. – Suffer

In my case I believe this is being caused by the DB having a different name where it is being loaded from where it was backed up from. (They're suffixing the DB to indicate it is a test DB on the test server) – Murtha

In my case it was state when database was in offline mode (but not showing on UI in tree). But choosing checkbox before restoring process also helps. – Osset

Still works two years later in Management Studio v17.9. It's so odd that it disables the checkbox when you select the file, but if the box is checked anyway, it does indeed close existing connections before the restore. Bugs like this in products like this... annoying. I'm taking a deep breath and moving on. :) – Skerry

Good! In my case I was trying to restore a database on another existing one but checkbox was grayed out. Applying this solution works for me. First, check "Close existing connections..." and then select source. I am using SQL Server 2012. Thx. – Shamrao

Works like a charm. Looks like a bug in SQL Management Studio. – Cochlea

Still the same in end 2021. :D I renamed my database before, then the problem came up. – Beardless

Same issue and solution in SSMS 20.1. Glad this solution is still working. – Handley

Answer 2

right click on database-> properties-> Options-> change Auto Update statistics Asynchronously to false. it will solve close existing connections to destination database grayed out issue

I hope it help

Tannic

This worked for me, but are there any consequences to changing this setting? – Anemochore

When I did this, the setting was re-enabled after restore, since the backup source had it enabled. – Potential

In my case, "Auto Update Statistics Asynchronously" was already false. I change the other option "Auto Update Statistics" to false and it worked. – Paddlefish

2020: Seems this may not work with current SSMS. Right-clicking take DB offline/online is one way to drop connections. +1 regardless – Illfounded

Answer 3

It can happen if you are restoring from a version less than 2012, for instance trying to restore a SQL Server 2005 database using SSMS 2012.

Crosswalk

I'm am having the same issue as @schlingel. I'm not saying your answer is wrong (I know it can be caused by restoring different versioned backups), however this is not the case for me. Both the backup and the destination server are 2012, and both databases are set to 2012 compatibility. Any ideas? – Buss

nope, I am restoring from version 2012 to version 2012. In fact, I am the one who installed them :) – Suffer

Answer 4

Same happened here with 2014 server and management studio. I could set the database to single user mode under database properties/Options/State/Restrict Access. For me it was set back to multi user mode automatically after the restore.

Ghirlandaio

Answer 5

Restart SQL Server (MSSQLSERVER) service and try to restore. Not a great solution but sometime it works.

Superfamily

The only reason why this seems to work is that it kicks everyone off your database. A better way is to issue an ALTER DATABASE <yourdb> SET OFFLINE WITH ROLLBACK IMMEDIATE, which will prevent new connections to the target database while it is being restored. – Pella

Right. as I said - not a great solution. – Superfamily

Answer 6

In SSMS 18, I went to Activity Monitor → Processes, filtered by database, and killed the processes that came up. Then the Restore stopped giving that error and it succeeded.

Doerrer

Answer 7

In my situation a database restore was failing because "the database was in use". To remedy the situation, I tried to check the box in SSMS > Restore Database dialog > Options page, "Close existing connections to destination database" but it was grayed out. I figured out how to enable it (@Ehud Grand's suggestion) however that did not solve the problem. What worked is opening Windows command prompt, typing 'net use' to view all network connections. Found a connection to the server where the conflict was then deleted that connection. I used this reference on how to use the net use command to delete a specific connection.

Quinacrine

Answer 8

Before selecting the backup file from the device . Tick close existing connection to destination database

Campbellbannerman