PowerShell Script Examples from DAT314

I’d like to thank everyone who attended my TechEd session today titled “Manageability Series: Microsoft SQL Server Automation on Steroids, Including PowerShell Support”. Sean McCown – SQL MVP – and all around DBA – creator of the Midnight DBA videos available at: http://midnightdba.itbookworm.com/ – was kind enough to stop by after the session to point out how the SQLPS shell doesn’t support plug-ins. Chad Miller’s SQLPSX library of useful scripts for working with SQL Server will work with the SQLPS shell – you just might not get all the functionality. Your best bet is to configure the standard PS shell as Michiel Wories describes in his blog post at: “SQL Server PowerShell is Here!”.

Here is a list of the PowerShell script examples that I demonstrated in my session that Buck Woody provided to me.

# TechEd PowerShell Overview examples for DAT314
# Originally from Buck Woody http://blogs.msdn.com/buckwoody/default.aspx

# Show Databases not backed up in a day
DIR "SQLSERVER:\SQL\SQL1\SQL2K8\Databases" |
where-Object {((get-date)-($_.LastBackupDate)).days -gt 1} |
sort-Object -Property LastBackupDate |
select-Object Name, RecoveryModel, LastBackupDate

# Server Discovery
$machine_name = "SQL1"
get-wmiobject -class win32_service -computer $machine_name  |
where { $_.name -like 'MSSQL*' -and $_.Description -like '*transaction*'} |
select name |
ConvertTo-HTML -title "Services" -head "<link rel='stylesheet' href='styles.css' type='text/css' />" |
Out-File C:\ServiceNames.html

# Show all services and their states using WMI
$strComputer = "."
$tab = [char]9
$colItems = get-wmiobject -class "Win32_Service" -namespace "root\cimv2" -computername $strComputer | sort "State"
foreach ($objItem in $colItems) {write-host $objItem.State $tab $objItem.Name}

#Write to the log:
$log = New-Object System.Diagnostics.EventLog
$log_type = "Application"
$source = "ApplicationName"
$message = "Message To Enter Go TechEd"
$eventid = 12347
$type = "Information"
$log.set_log($log_type)
$log.set_source($source)
$log.WriteEntry($message,$type,$eventid)
#Read the log:
get-eventlog application | Where-Object {$_.EventID -eq 12347}
get-eventlog application | Where-Object {$_.source -eq "ApplicationName"}
#Clear the event log
Get-EventLog -list | % {$_.Clear()}

# Accout Group names using WMI
$groups = Get-WmiObject –computerName SQL1 Win32_Group
$groups | %{$_.Name}

# Simple Yes and No
$s = 'Welcome to Powershell - You Like?'
[System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
$d = [Windows.Forms.MessageBox]::Show($s, "Windows Form", [Windows.Forms.MessageBoxButtons]::YesNo, [Windows.Forms.MessageBoxIcon]::Question)
$d -eq [Windows.Forms.DialogResult]::Yes

#--------------------------------------------
# SQLPSX - SQL Server PowerShell Extensions
# By Chad Miller
# http://sqlpsx.codeplex.com/
# Set of useful CMDLETs that use SMO objects
#--------------------------------------------
# View some of the commands
Get-Command *et-Sql* | Select Name

# With SQLPSX
$server = Get-SqlServer 'SQL1\DEMO1'

# With SMO
#Connect and run a command using SMO
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "SQL1\DEMO1"

# Get server processes
$server.EnumProcesses() | Format-Table

$db = Get-SqlDatabase 'SQL1\SQL2K8' AdventureWorks
Get-SqlUser $db | Format-Table

Get-SqlLogin $server | select members