Blog moved!

I now have a proper wordpress blog on my own hosting.

Please goto http://blog.slinkysoftware.com

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!

First post!

Got into this whole blogging thing several years later than everyone else! Mainly due to being lazy...

What this space got some plans...