Tuesday 28 July 2009

Killing database connections from powershell

Recently I have been setting up a PowerShell script to automatically setup our PY (usually aka SIT) environment. One of the tasks in the script is to Kill all the connections to a database so I can restore a backup. I managed to find a function that met my needs using the SMO libraries. Unfortunately as we are still on SQL2000 (I know, I know! Its not for much longer now tho! :D ) the SMO Libraries were not available on our database server so I had to come up with an alternative. Basically I decided to go the route that required the least dependancies and this is what I came up with!

function KillDBConnections([string]$serverName, [string]$DBName)
{

$ConnectionString = "Data Source=$serverName;Initial Catalog=master;Integrated Security=SSPI"

$connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
$command = New-Object System.Data.SqlClient.SqlCommand;

$command.Connection = $connection;
$command.CommandType = [System.Data.CommandType]::Text;
$command.CommandText = "SELECT spid FROM master..sysprocesses WHERE dbid=db_id('$DBName')";

$connection.open();
$reader = $command.ExecuteReader();
$stringBuilder = New-Object System.Text.StringBuilder

while ($reader.Read())
{
$stringBuilder.AppendFormat("kill {0};", $reader.GetValue(0));
}

$reader.Close();

$command.CommandText = $stringBuilder.ToString();

if($command.CommandText)
{
$command.ExecuteNonQuery();
}

$connection.Dispose();
}



I'm still fairly new to PowerShell scripting so if anyone can see any glaring problems please let me know!