Month: March 2013

Set Compatibility Level for User Databases via Powershell

Quick script to set the compatibility level for all user databases from 100 (sql 2008) to 110 (sql 2012) via powershell.  This uses SQLPSX.

Import-Module SqlServer

Get-SqlDatabase -sqlserver 'ServerInstance' | where{$_.IsSystemObject -eq $false} |%{
	
	if($_.CompatibilityLevel -eq [Microsoft.SqlServer.Management.Smo.CompatibilityLevel]::Version100)
	{
		$_.CompatibilityLevel = [Microsoft.SqlServer.Management.Smo.CompatibilityLevel]::Version110
		$_.Alter()
	}
}

Update Operator Email Address in Sql Server via Powershell

Quick script to update all the operators’ email addresses in sql server via powershell when passing in the old email address.  This uses SQLPSX.

param(
	[String[]]$serverInstance = @('Server1', 'Server2', 'Server3'),
	[string]$oldEmailAddress = 'oldemail@yourdomain.com',
	[string]$newEmailAddress = 'newemail@yourdomain.com'
)
Import-Module Agent

$serverInstance | %{
	Get-AgentJobServer -sqlserver $_ | %{
		Get-AgentOperator -jobserver $_ | where{$_.EmailAddress -eq $oldEmailAddress}| %{
			$_.EmailAddress = $newEmailAddress;
			$_.Alter();
		}
	}
}