Close existing connection before deleting/restore database

Question

I want to be able to force a restore or a delete on a database even if there still have some active connections. In SQL Server management studio there is a checkbox when you're trying to delete a database to "Close existing connection" i want to do the same thing but using SMO. How can i do this ?

Thank

Answer 1

Hi,

see my blog entry here:

http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/9/Default.aspx

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Answer 2

Hi,

It seems the link is dead...

Thanks

Answer 3

I am not sure if T-SQL script will be fine for you. You can run the following:

Alter database db_name set single_user with rollback immediate
go
drop database db_name
go
restore database ...

Answer 4

Replace DATABASE_NAME_HERE with your database name. Try this:

USE master 
GO 
 
SET NOCOUNT ON 
DECLARE @DBName varchar(50) 
DECLARE @spidstr varchar(8000) 
DECLARE @ConnKilled smallint 
SET @ConnKilled=0 
SET @spidstr = '' 
 
Set @DBName = 'DATABASE_NAME_HERE' 
IF db_id(@DBName) < 4 
BEGIN 
PRINT 'Connections to system databases cannot be killed' 
RETURN 
END 
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; ' 
FROM master..sysprocesses WHERE dbid=db_id(@DBName) 
 
IF LEN(@spidstr) > 0 
BEGIN 
EXEC(@spidstr) 
SELECT @ConnKilled = COUNT(1) 
FROM master..sysprocesses WHERE dbid=db_id(@DBName) 
END

---

Trust No one

Answer 5

To do it with SMO you can use the KillAllProcesses method.

$serverObject.KillAllProcesses('YourDatabaseNameHere')

Answer 6

Hello,

As the original poster asked in SMO, i would suggest this link :

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.killallprocesses(v=sql.105)

There is no VC## example but the VB example could be easily "translated" in VC##

//Connect to the local, default instance of SQL Server.
Server srv = New Server();
//Stop all processes running on the AdventureWorks2008R2 database.
srv.KillAllProcesses("AdventureWorks2008R2");
//Stop the AventureWorks database.
srv.KillDatabase("AdventureWorks2008R2");
//Stop the specified process with ID 52.
srv.KillProcess(52);

The reply from theKastner is using PowerShell but it is easy to translate in VC## and VB thru the previous link

The reply from switch13 is off-topic : it is not a T-SQL script which is asked

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 7

Papy, a question on etiquette here.  Since the original poster didn't specify the language used to access SMO I made an assumption they would be able to translate my Powershell to whatever was being used.  Should I not do that in the future? 
As an aside, I get a "Page Not Found" page from the link you provided.  This one still works.
Server.KillAllProcesses Method (Microsoft.SqlServer.Management.Smo)

Answer 8

Hello,

I think that i have done a little error when i copied the link address

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.killallprocesses(v=sql.105)

Usually , in this forum , SMO is implying the use of languages like VB or VC# ( VC++ is rare and F# never seen ). In the link i gave, there were only examples in VB and PowerShell. It is why i provided the "translation" in VC#.

As i am using mainly VC# and Powershell, i know it is simple to "translate" PowerShell in VC#. It is less evident to translate Powershell in VB. I recall you that i have ended my sentence with "but it is easy to translate in VC## and VB thru the previous link". I have not critisized your proposal , the only tiny reproach could be that this forum ( at least at the beginning in 2005 ) is dedicated to the use of SMO in .Net languages (VB,VC#,...) and PowerShell has appeared in this forum since the release of SQL Server 2008, but it is less current than VB and VC#.

You should compare with the reply from switch13 : his T-SQL is good , useful in the Transact-SQL forum, but not here as it is not easy to use .The direct use of KillAllProcesses(databasename) is simpler than to load the T-SQL in a string variable ( for example p_s )and to write 

svr.ConnectionContext.ExecuteNonQuery(p_s);

A last little remark : here, the posters needing an answer in Powershell tell it. When Powershell is not indicated, it means that the OP ( original poster ) is asking code in a .Net language ( since 3 years , mainly in VC#, before in VB because of the SMO 2005 documentation was not giving any example in VC# ).As the OP writes in his 1st post, he knew to do the same thing in SMO than it is possible to do in SSMS ( where i am mainly using PowerShell as pssql.exe ).But there is no comparision between a Powershell script and a T-SQL script. Especially when there is only one code line instead to do a loop to find every connection opened on the database and to use the KILL statement on each connection.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.