Tag Archives: Windows

How To: Capture PowerShell Output in Windows Scheduled Tasks

In a previous blog Capture PowerShell Output in Windows Scheduled Tasks I stated the only reliable way I found to capture PS output is by using a CMD or Bat to execute the PS script. After stumbling upon the correct  way to call PowerShell using a –Command variable, I changed my mind. I found the correct way by reading the PowerShell help in PS v3 (imagine that) which says under –Command:

To write a string that runs a Windows PowerShell command, use the format:
“& {<command>}”
where the quotation marks indicate a string and the invoke operator (&)
causes the command to be executed.

So now, I can call PowerShell from Windows Task Scheduler with the correct syntax while still supplying various parms and send the whole command to an output file. The command would look like this:

Powershell.exe -NonInteractive -NoLogo -NoProfile -Command “& {D:\Scripts\SQL-Servers\Backup-SqlServer.ps1 –Server SQLSERVER -Verbose; Return $LASTEXITCODE}” 2>&1> D:\logs\SQL-Servers\Backup_SQLServer.txt”

This will execute my script, return whatever code I emit from the script, and write any output to a file.

SQL Server 2012 Uses PowerShell V2 Even If It Is On Windows Server 2012

Wow! PS version 3 is great! A host of beneficial changes. If you’re like me, you probably wanted to start using it right away to take advantage of some of the new and wonderful changes.

But, HOLD ON! If you are wanting to implement any of those new and upgraded cmdlets in SQL Server 2012, you can’t. Even if you are running on Windows Server 2012, you still can’t use PS 3 cmdlets. Yes, that’s right. The reason is SQL 2012 uses PS Version 2 internally. Check it out. Here is a screen shot of the $PSVersionTable command in SQL Server 2012 on Windows Server 2012 (after right-clicking a database and selecting Start PowerShell):
SNAGHTML4c1c86

Here’s a screen shot of $PSVersionTable in Powershell on the same server
SNAGHTML4ee1c7

So, If you want to run PS 3 for any job steps on SQL 2012, you will need to choose the command type as Operating System (CmdExec) and not PowerShell.
SNAGHTML5aaf05

Capture Powershell Output in Windows Scheduled Task

This post is outdated after discovering new information –
See my updated blog at How To: Capture PowerShell Output in Windows Scheduled Tasks

Ever see the Geico commercial where the guy says it took three months to teach a guinea pig to say “Row”? “Such a simple word”!

Well, I’ve hunted around, tried all kinds of methods, and spent hours on what should have been a simple task! Finally, I think I’ve found the only reliable method for capturing PowerShell output when it is run as a Windows Scheduled Task.

The long and short of it is: Use a CMD or BAT program to call the PowerShell script. You may not have to use a CMD program but in some cases you will. A lot of it depends on the parameters being passed and if those parameters contain a “List” item or whether there are single quotes or double quotes and who knows what else. I tried many variations of supplying PowerShell parms in task scheduler. Some worked in some cases and not in others. The only consistent method which worked in every case was to use a CMD program.

With this method you can not only capture all the PowerShell output (stderr, error, verbose, etc.) to a file using redirection but you can also capture any return code and thereby have the Windows Task “know” the script succeeded or failed.

There are no changes necessary to your PowerShell script unless you want to capture an Exit code.  What you do need to do is create a CMD program which calls your Posh using a format such as:

Echo off
::Execute a Powershell script from a CMD
If (%1)==() GOTO Missing
Powershell.exe -NonInteractive -NoLogo -NoProfile -Command ""D:\Scripts\MyPosh.ps1"" -Server %1 -Warehouse ""%2"" 2>&1> ""D:\logs\PoshLog_%1.txt"";Return $LASTEXITCODE"

:Missing
Echo "Parms are missing"
Exit 4

The key here is the –Command syntax. I have found this does not work properly when using –File. You must use –Command and enter the entire command (including script parms, redirection and the Return $LASTEXITCODE) within double quotes. You must also double double-quote any parms containing spaces. My example Command can be broken down as:

      1. The complete path and name of the PowerShell script to run
      2. Three parms I am supplying to the script as indicated by %1-%3. These parms are entered in the Windows Scheduled Task which calls this cmd script.
      3. The redirection of stderr to stdout and all redirected to a file using 2>&1> “C:\MyPath\log.txt”
      4. Command separator “;” the semicolon
      5. Capture the $LASTEXITCODE for the previous command (the Posh script) and throw it out to Windows Scheduler

 The Task Scheduler would look something like this  WinTaskSched.png The arguments portions is like this:

LCFSQLT04 "W:\SQL Backups" "'DOTProperty','Property'"

It has three arguments of which the last is a “list”

Emitting an Error

In order to capture an error from your Powershell script, you have to emit that error by using an EXIT statement. A RETURN statement will not work.

Failover SQL Server with Powershell

To failover an SQL mirrored database is fairly easy using SSMS or T-SQL. However, in a DR situation when things are happening quickly and time is of the essence, there is not always the luxury of having the resources available to accommodate failing over a large number of databases. If at all possible it is most desirable to fail over all over your databases while the principal server is still available.

In preparation for our next DR test I developed a Powershell script which would identify all mirrored databases and failover those databases. In the event the principal server is unavailable, the script can also be used for a forced failover on the mirrored server. Our environment consists of mirroring databases on one server to another like server at our DR site. Although you could mirror various databases from one instance to a multitude of instances, we adhere to a one-to-one instance environment. The script assumes this one-to-one placement but it could be easily changed to accommodate a one-to-many or even a many-to-many environment. That’s the beauty of Powershell scripting.

The script is developed on a Windows 7 platform with SQL 2008 R2 Tools. It was tested on Windows Server 2008 and 2008 R2 with SQL 2008 R2 EE. You are free to use the script as is or to modify it as needed. However, I do ask you maintain the attribution to the author


Param(
	[CmdletBinding()]
	[Parameter(Mandatory=$true,Position=0)]
	[string]$PrincipalServer,
	[Parameter(Mandatory=$true,Position=1)]
	[string]$MirrorServer,
	[Parameter(Mandatory=$false,Position=2)]
	[String[]]$Database,
	[Parameter(Mandatory=$false,Position=3)]
	[switch]$Force
)

Function New-SMOconnection {
	[CmdletBinding()]
    Param (
		[Parameter(Mandatory=$true)]
		[string]$server,
		[int]$StatementTimeout=0
	)
	Process {
	If(!(Test-Connection -ComputerName ($server.Split('\')[0]) -Quiet -Count 1)) {
		Throw "Could not connect to Server Machine $($server.Split('\')[0])."
	}
	$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server)
	$conn.applicationName = "PowerShell SMO"
	$conn.StatementTimeout = $StatementTimeout
	Try {$conn.Connect()}
	Catch {
		Write-Exception $_.Exception
	}
	if ($conn.IsOpen -eq $false) {
		Throw "Could not connect to $server SQL Instance."
	}
	$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
	$smo
	}
}

Function Write-Exception {
	Param($exception)
	Write-Host $exception.Message
	While ($exception.InnerException) {
		$exception = $exception.InnerException
		Write-Host $exception.Message
	}
}

Function Force-FailOver {
Param(
	[CmdletBinding()]
	[Parameter(Mandatory=$true,Position=0,ValueFromPipeLine=$true)]
	$database
)
	Process {
		Try{
			$database.ChangeMirroringState([Microsoft.SqlServer.Management.Smo.MirroringOption]::ForceFailoverAndAllowDataLoss)
		}
		Catch {
			Write-Exception $_.Exception
			Return "`nUnsuccessful Failover for $($database.Name)"
		}
		Return "Database mirror $($database.Name) is failed over with possible data loss"
	}
}

Function Normal-FailOver {
<#
	Normal failover executes on the Principal database. Once the failover completes,
	we then need to adjust our Mirroring Safety Level back to OFF. We have to switch
	to the Principal database which is now on the mirror server and alter the Saftey
	level there.
#>
Param(
	[CmdletBinding()]
	[Parameter(Mandatory=$true,Position=0)]
	$smo,
	[Parameter(Mandatory=$true,Position=0,ValueFromPipeLine=$true)]
	$database
)
	Process {
		Try{
			$database.MirroringSafetyLevel = [Microsoft.SqlServer.Management.Smo.MirroringSafetyLevel]::Full
			$database.Alter()
			# We have to wait to do the actual failover. The database needs to
			# catch up after changing the safety level and altering the database
			Start-Sleep -Seconds 3
			$database.ChangeMirroringState([Microsoft.SqlServer.Management.Smo.MirroringOption]::Failover)

			# Switching to server where the Principal now resides
			$db = $smo.Databases[$($Database.Name)]
			$db.MirroringSafetyLevel = [Microsoft.SqlServer.Management.Smo.MirroringSafetyLevel]::Off
			$db.Alter()
		}
		Catch {
			Write-Exception $_.Exception
			Return "`nUnsuccessfull failover for $($database.Name)"
		}
		Return "Database mirror $($database.Name) is failed over"
	}
}
# Initialize process
Set-StrictMode -Version Latest
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum")

# Get a connection to the Mirror server
Try {
	$smoMirror = New-SMOconnection -server $MirrorServer
}
Catch {
	Write-Exception $_
	Throw "`nMirror server unavailable. Failover can not be run for $MirrorServer"
}

If($Force -eq $false) {
	# Get a connection to the Principal Server
	Try {
		$smoPrincipal = New-SMOconnection -server $PrincipalServer
	}
	Catch {
		Write-Exception $_.Exception
		Throw "Principal server $PrincipalServer is unavailable. Try running with the -Force parameter"
	}
	# Get a collection of mirrored databases from the principal server
	$databases = @($smoPrincipal.databases | Where-Object {$_.IsMirroringEnabled -and
		$_.Status -eq 'Normal' -and
		$_.MirroringStatus -eq [Microsoft.SqlServer.Management.Smo.MirroringStatus]::Synchronized})
}
Else {
	# Get a collection of mirrored databases from the mirrored server
	$databases = @($smoMirror.databases | Where-Object {$_.IsMirroringEnabled -AND ($_.Status -eq 'Restoring')})
}

# Use the database parameter list (if supplied) to limit the databases to the list
If($database) {
	$databases = @($databases | Where-Object {$database -contains $_.Name})
}

# Use a function to fail over the databases depending on the type of failover
If($Force -eq $true) {
	$databases | Force-FailOver
}
Else {
	$databases | Normal-FailOver -smo $smoMirror
}

If($smoPrincipal) {$smoPrincipal.ConnectionContext.Disconnect()}
$smoMirror.ConnectionContext.Disconnect()
Write-Host "`nScript completed for $($databases.Count) database(s)"