Archive for May 2011

The Poor Mans data compare with Powershell

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 […]

Check SQL Agent Job Owners with Powershell

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 […]

Check database auto-shrink setting with Powershell

It’s very well known that auto-shrink is bad for reasons I won’t repeat here. Perhaps you’ve been meaning to check all your servers and databases but simply haven’t got around to it? A simple bit of Powershell makes this into a trivial task; ?View Code POWERSHELL# Load SMO [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;   # List of […]

Document your SQL Agent Jobs

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. […]

Audit SQL Server collation with Powershell

Here’s just a quick Powershell script I knocked up to find out the server-level and database collations on multiple servers. Just specify each SQL Server in the array called $servers and you’re good to go. ?View Code POWERSHELL# Load SMO [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null; # Specify servers here $servers = @("localhost\sqlexpress", "localhost");   foreach($server in $servers) […]