Question:
Is there a way to use SMO to disconnect all active users from a SQL database, so I can perform a database restore?
The question linked to below is similar, but does not discuss the use of SMO.
When restoring a backup, how do I disconnect all active connections?
This link also is a similar question, but remains unanswered:
http://us.generation-nt.com/answer/smo-detach-fails-due-active-connection-help-122972951.html
Answer:
You can use the KillAllProcesses method.
|
1 2 3 |
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" $serverObject = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -argumentList 'YourServerNameHere' $serverObject.KillAllProcesses('YourDatabaseNameHere') |
If you want to check you can use the GetActiveDBConnectionCount method. But you might want to wait a few second for rollbacks and such
|
1 |
$serverObject.GetActiveDBConnectionCount('YourDatabaseNameHere') |