#sqlsat89 scripts

Here are the scripts I used during my session, Getting Started With PowerShell. I’ll have an after event post soon.

Starting Services:

#Start SQL Server
Get-Command -CommandType Cmdlet
Get-Command -CommandType Cmdlet | Format-Table name

# List all services

# Attempt to filter on the "SQL" string
Get-Service | Select-String -Pattern "SQL"

# The filter doesn't work, why not?
Get-Help Select-String

# The output of Get-Services is an array of objects!
Get-Service | Get-Member ;

# Use the Where-Object cmdlet to filter the results
Get-Service | Where-Object{$_.Name -match "SQL"}

# You can start every service on the server at once if you want. 
# Get-Service | Where-Object{$_.Name -match "SQL"} | Start-Service

# Use the -Force parameter to ignore dependencies
# Get-Service | Where-Object{$_.Name -match "SQL"} | Stop-Service -Force

# There are several ways to start the services
# Start-Service "SQLSERVERAGENT" ;
# Start-Service "MSSQLSERVER" ;


Renaming Agent Jobs:

# Declare a variable for the desired username
$job_owner = "DBAsRule" ;

# Even text is an object!
$job_owner |Get-Member

# Declare the intention to use the Microsoft.SqlServer.SMO .NET namespace
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

# The PowerShell 2.0 convention is to use Add-Type, but it is sometimes problematic.
# Add-Type -AssemblyName "Microsoft.SqlServer.SMO"
# Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\\Microsoft.SqlServer.Smo.dll”

# Store an instance of the local SQL in a variable
$sql_server_object = New-Object Microsoft.SqlServer.Management.SMO.Server('localhost')

# Get-Help Get-Member

# Get the SQL Agent SMO object
$sql_server_agent = $sql_server_object.JobServer;

# $sql_server_agent | Get-Member

# Get the JobCollection object
$jobs = $sql_server_agent.Jobs;

foreach($j in $jobs)

foreach($j in $jobs)
	if($j.OwnerLoginName -ne $job_owner )
		$j.OwnerLoginName = $job_owner;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: