2

#PSTip Drop all Active Connections of SQL Database

Note: This tip requires PowerShell 2.0 or above.

In an earlier tip, we looked at how we can retrieve the active connection count for a SQL database. In today’s tip we will look at how we can drop all the active connections before we can perform a detach database operation.

For this purpose, we will use the KillAllProcesses() method of the Server SMO.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME
$smo.KillAllProcesses('MyDB')

This code snippet will help you drop all active database connections of a given SQL database.

Filed in: Columns, Tips and Tricks Tags: , , ,

2 Responses to "#PSTip Drop all Active Connections of SQL Database"

  1. This piece of code was PERFECT for what I needed ! Doing a database RESTORE and I had put the database in SINGLE USER mode but that did not drop all connections. This worked. Thanks

Leave a Reply

Submit Comment

© 2017 PowerShell Magazine. All rights reserved. XHTML / CSS Valid.
Proudly designed by Theme Junkie.
%d bloggers like this: