Close existing connection before deleting/restore database

_{Sunday, October 8, 2006 3:52 PM}

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

_{Sunday, October 8, 2006 7:09 PM}

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

_{Tuesday, July 28, 2009 9:02 AM}

Hi,

It seems the link is dead...

Thanks

_{Wednesday, July 29, 2009 1:39 AM}

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 ...

_{Thursday, September 6, 2012 12:43 PM | 1 vote}

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

_{Thursday, September 13, 2012 5:10 PM | 3 votes}

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

$serverObject.KillAllProcesses('YourDatabaseNameHere')

_{Thursday, September 13, 2012 7:39 PM}

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 switch43 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.

_{Thursday, September 13, 2012 8:55 PM}

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)

_{Thursday, September 13, 2012 10:02 PM}

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 switch43 : 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.