This is going to be fast. I just got tired of doing the following step on my SSMS (SQL Server Management Studio):
1. Click on SQL Server Activity Monitor.
2. Expand “Processes”.
3. Click on the “Database” column.
4. In the “Database” column drop-down list, look for my database to kill all the connections.
5. Here’s the fun part… I have to right-click and select “Kill Process” on each of my database I want to be clear.
Well, I finally got PowerShell to get it done quickly for me. With a simple using 2 lines of code as long as you are using SQLPS.exe and/or loading the SQL Server 2008 provider and snapins. Here’s how:
If you don’t have SQL Server 2008 installed, then you could use SMO to accomplished the same thing but, unfortunately, you have to add one move line of code:
Now, script is a very simple script, and you can explore what other objects you use from “$SQLSvr1” variable. Make sure be familiar and use Get-Member cmdlet. “$VariableYouJustCreated | Get-Member” – to display all the methods and properties from that object.
Sample Get-member result… by the way you can use the alias and us “gm”.