CODEWITHSUNDEEP
sql-serverdatabasesql-server-2012ssmsdatabase-restore
schlingel
schlingel

Reputation: 1694

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

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.

Upvotes: 72

Views: 38372

Answers (8)

user22683500
user22683500

Reputation: 1

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.

Upvotes: 0

eyelesscactus54
eyelesscactus54

Reputation: 116

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.

Upvotes: 0

Akhil M
Akhil M

Reputation: 11

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

Upvotes: -1

Mohamed Elmofty
Mohamed Elmofty

Reputation: 171

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

Upvotes: 17

RoyBS
RoyBS

Reputation: 1281

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

Upvotes: 1

Ehud Grand
Ehud Grand

Reputation: 3693

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.

Upvotes: 155

Perrier
Perrier

Reputation: 2827

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.

Upvotes: 1

FAA
FAA

Reputation: 179

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.

Upvotes: 3

Related Questions