Close existing connection before deleting/restore database

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 ?

ThankHi,

see my blog entry here:

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

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Close all open connections

Before I can drop an mdf file form the server, all connections needs to be closed. how can I force to close this connection. The solution explained on this blog don't seems to work in my case http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/9/Default.aspx

I'm using SQL express, with visual studio pro 2005.

Thx for you quick responses

best regards

Luc N

please verify the code I've used

Dim svr As Server

svr = Nothing

svr = New Server(".\SQLEXPRESS")

'attach database

Dim con As New SqlConnection(svr.ConnectionContext.ConnectionString)

Dim com As New SqlCommand("sp_detach_db", con)

Dim d As Database

con.Open()

For Each d In svr.Databases

If Not d.IsSystemObject Then

'com.CommandType = Data.CommandType.StoredProcedure

'com.Parameters.Add(New SqlParameter("@.dbname", d.Name))

'com.ExecuteNonQuery()

'MsgBox(d.UserName.ToString())

d.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted

SqlConnection.ClearAllPools()

d.Drop()

'com.CommandText = "DROP DATABASE " & d.Name.ToString

'com.CommandType = CommandType.Text

'com.ExecuteNonQuery()

'End If

End If

Next

com.Connection.Close()

Why do you think that the information form the blog is not working for you ?

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

I've still got the message 'cannot drop the database because.......................'

I've modified my code , which seems to be working,

please reply your comments on this code

thx Luc

Try

Dim svr As Server

svr = Nothing

svr = New Server(".\SQLEXPRESS")

'attach database

Dim con As New SqlConnection(svr.ConnectionContext.ConnectionString)

Dim d As Database

con.Open()

For Each d In svr.Databases

If Not d.IsSystemObject Then

Exit For

End If

Next

If InStr(d.Name, "exp", CompareMethod.Text) <> 0 Then

svr.KillAllProcesses(d.Name)

d.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted

SqlConnection.ClearAllPools()

d.Drop()

con.Close()

End If

Catch ex As Exception

' MsgBox(ex.Message)

End Try

|||As I pointed out in the comment, they fixed the behaviour in the Service Pack, so KillDatabase should work for you.

Clearing out snapshot folders

They should be removed by the cleanup agent, provided all subscribers have
been initialized. Do you have anonymous subscribers? That'll force them to
hang around until the retention period is reached. So - 2 things to check:
anonymous subscribers enabled and is the cleanup agent enabled...
HTH,
Paul Ibison
Have a look at sp_helpdistributiondb.
Cheers,
Paul
|||Hi,
i'm having the same issue as Dave...I have anonymous subscriptions,
clean up agent is enabled, retention period is set to 48hrs,
subscription expiriation is set to the default 14 days. but in the
repldata folder i have snapshots that have been created from months
ago.
how come they are not being cleaned up? what else do i need to check
besides what was already mentioned?
Thanks in advance
Steve
On May 19, 1:38 am, David Gray <g...@.turpin-distribution.com> wrote:
> 48hrs... That will be why then.
> Cheers
> Dave
> On Fri, 18 May 2007 07:28:04 -0700, Paul Ibison
>
> <Paul.Ibi...@.Pygmalion.Com> wrote:
> - Show quoted text -