With the Get-Hotfix cmdlet you can query the list of hotfixes that have been applied to computers. ?View Code POWERSHELLGet-Hotfix | Format-Table -AutoSize; This will display the list of hotfixes installed on the local computer. Source Description HotFixID InstalledBy InstalledOn —— ———– ——– ———– ———– SO0590 Update 982861 NT AUTHORITY\SYSTEM 07/07/2011 00:00:00 SO0590 Update KB958830 [...]

The Get-EventLog cmdlet is great for working with the Windows Event Logs on local and remote computers. It includes lots of parameters that make life much easier than using the Event Viewer GUI. To list the available logs on the local computer just execute; ?View Code POWERSHELLGet-EventLog -List | Format-Table -AutoSize; Max(K) Retain OverflowAction Entries [...]

I wanted to find a way of programatically identifying the active node of a SQL Server Cluster. I found this post that demonstrated how to do it with TSQL. As I love Powershell so much here’s another method to do it; ?View Code POWERSHELL# Set cluster name $cluster_name = "ClusterName"; # Load SMO extension [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") [...]

Here’s a simple example showing how to manage the failover process with Powershell, making sure all resources are running on one node. First, execute the below command to show which node owns the resources. ?View Code POWERSHELLGet-ClusterGroup -Cluster ClusterName | Format-Table -AutoSize; Name OwnerNode State —- ——— —– SoStagSQL20Dtc Node1 Online SQL Server Node1 Online [...]

It’s considered a bad practice  Not using Primary Keys and Clustered Indexes here’s a Powershell script that can make checking a database for this very easy. Just set the $server to the sql instance you want to check and $database as appropriate. ?View Code POWERSHELL# variables $server = "sqlinstance"; $database = "badDB"; # Load SMO [...]

I’ve been looking at using Powershell in our release process to automate various things. I’ve used it to compare table data between databases and I’m now thinking of using it to validate our schema upgrades. I want to be easily alerted to any missing tables, columns, stored procedures and other objects. We have TFS at [...]

I was looking for a way to grab a list of processes running inside Sql Server but wasn’t having much luck. Essentially I wanted something like the Get-Process cmdlet but for Sql Server. Shortly after tweeting for help I stumbled across the EnumProcesses SMO method. Using this is quite simple. To list all Sql Server [...]

Each new cmdlet I discover makes me fall in love with Powershell a little bit more. A while ago I discovered the Compare-Object cmdlet. The examples given in the documentation demonstrate how to compare computer processes and text files but I was interested to see if this would work with a dataset. So I tried [...]

You may have a standard within your organisation for ownership of SQL Agents Jobs. Here’s quick Powershell snippet that you can use to check your server for compliance against your policy. Change the $servers array to contain the names of the SQL Servers you want to query. Change the value for $default_user to the user [...]

And I don’t mean writing it down in a word document, leaving it somewhere on the network, and then forgetting about it. How about keeping the documentation with the job? Microsoft provides us with a space for it… In this description field ideally I’d like to see A brief description of what the job does. [...]