Sql Server Powershell Snapin

Might as well show you the code to load the Sql Server snapin for powershell as well.  I have this in my $profile, as I use the snapin constantly.

function LoadSQLSnapin()
{
 
#
# Add the SQL Server Provider.
#
 
$ErrorActionPreference = "Stop"
 
$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"
 
if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
   throw "SQL Server Provider for Windows PowerShell is not installed."
}
else
{
   $item = Get-ItemProperty $sqlpsreg
   $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}
 
 
#
# Set mandatory variables for the SQL Server provider
#
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000
 
#
# Load the snapins, type data, format data
#
Push-Location
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml
update-FormatData -prependpath SQLProvider.Format.ps1xml
Pop-Location
 
}
 
LoadSQLSnapin
 
 

Get ServerNames from SQL Server in PowerShell

I see a lot of posts on monitoring sql server that use a “Servers.txt” to iterate the server names.  We (and I imagine a lot of other places as well) store our server information in sql server and not a text file.  Here is how you get said list of servers directly from sql server instead of a text file:

Set-Location SQLSERVER:\SQL\SERVERNAME\DEFAULT
$Servers = Invoke-Sqlcmd -Query "SELECT
       ServerName      
   FROM dbo.[Servers];"
   
foreach($server in $Servers)
{
       Write-Host $server.ServerName
}

You’ll need to load the sql server snapin for this to work correctly.

Compare jobs with PowerShell

I’m more amazed every day at how much easier powershell makes administering sql server.  We currently have this big messy home-grown monitoring system to compare and track changes in sql server.  It’s complicated, fragmented and difficult to debug.  I just duplicated the whole job-compare section of the monitoring system with 9 lines of code.  This will tell you which jobs were added and which ones were removed.  I’ve not tested it thoroughly (nay, at all), like what happens when you change a schedule on a job or whatnot, but this is a good start:

sl SQLSERVER:\SQL\SERVERNAME\DEFAULT\JobServer\Jobs
$jobs = Get-ChildItem
$jobsPrevious = Import-Clixml -Path C:\JobsPrevious.xml
$difference = Compare-Object -ReferenceObject $jobsPrevious -DifferenceObject $jobs
$Removed = @($difference | Where {$_.SideIndicator -eq "<="} | foreach {$_.InputObject}) -join ", "
$Added = @($difference | Where {$_.SideIndicator -eq "=>"} | foreach {$_.InputObject}) -join ", "

Write-Host "Removed:  $Removed"
Write-Host ""
Write-Host "Added:  $Added"

This just does the jobs, but there’s no reason why you couldn’t apply this code to other nodes in the SMO object model.

Powershell is amazing.  If you’re not using powershell to administer SQL Server, you’re really doing yourself a HUGE disservice.  Use at your own risk.

Update:  The xml that returns from get-childitem will be huge.  Also note, that you should actually write the $jobs out to file and re-load it using the Import-Clixml in order for the compare-object to compare properly.  I’m STILL working on getting this up to snuff, but I’m currently stretched too thin to dedicate any time to this at the moment.  I’ll put up a better post once I’m done with it, but this is a good starting point.