4

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

Community
  • 1
  • 1

2 Answers2

7

You can use the KillAllProcesses method. 

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 
$serverObject.GetActiveDBConnectionCount('YourDatabaseNameHere')

Bruce
  • 1,633
  • 1
  • 11
  • 12
  • Aha! That's what I was looking for. I tried the `Database.UserAccess` property from the comment on the OP, and that didn't seem to affect anything. Maybe I would have had to wait until all the users disconnected. Upvoted this answer until I have the chance to test and accept it. –  Feb 15 '12 at 22:04
1

Disconnect all active users is not a build functionality. You must use the ExecuteNonQuery. I found an example here: http://www.techtalkz.com/microsoft-windows-powershell/132252-run-transact-sql-using-smo.html

d_schnell
  • 614
  • 4
  • 5
  • I think the `Database` class should have a `DisconnectAll()` method or similar. There's no way to do this? –  Feb 15 '12 at 20:40
  • The problem is if your server is busy and you set the single user mode it's easy that an other process takes control and you can't restore the database. Doing it within a single script prevent this headache. Start with `ALTER DATABASE databasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE`. In the same script do your restore and finish with `ALTER DATABASE databasename SET MULTI_USER WITH ROLLBACK IMMEDIATE` – d_schnell Feb 15 '12 at 20:56