Tag Archives: script

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.

Powershell SQL Backup with Event Handler

A persistent problem I’ve had is capturing the output messages when executing SQL procedures. That is, until I recently learned how to capture those messages for output using an event handler.

The example function, provided below, executes an SQL Backup, uses an event handler to capture the output messages, and passes the information back to the calling routine.

Function Backup-SqlDatabase
<#
.SYNOPSIS
    Backs up an sql database to local or remote disk.

.DESCRIPTION
    This script will perform a full back up of the specified database to a local or network disk.

.PARAMETER  Server
    Specifies the sqlserver or instance name you wish to connect to and restore.

.PARAMETER Database
    Specifies the Database name you wish to connect to and backup.

.PARAMETER Path
    Specifies the path to the back up file.

.EXAMPLE
    Run-SqlBackup -Server "Server\MySql" -Database "MyDb" `
		-Path "\\server\D$\Backups\MySql.bak"
    Backs up database "MyDb" on SQL Server instance "Server\MySql" to a file on
	a network share.
.NOTES
	When backing up to a network share, permissions must be set appropriately 
	for the SQL Service Account.
#>
{
	[CmdletBinding()]
    param(
        [Parameter(Mandatory=$true)]
        [string]$Server,
        [Parameter(Mandatory=$true)]
        [string]$Database,
        [parameter(Mandatory=$true)]
        [string]$Path
    )
	$output = ""
	$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {
		param($sender, $event) 
	   	$output += $event.Message;
	} 
    $SQLConn = New-Object System.Data.SQLClient.SQLConnection
    $SQLConn.ConnectionString = "Server=$Server; Trusted_Connection=True"
    Try 
	{
		$SQLConn.add_InfoMessage($handler); 
		$SQLConn.FireInfoMessageEventOnUserErrors = $false;
        $SQLConn.Open()
    }
    Catch 
	{
		Throw $_
    }

    Write-Verbose "Connected to server $server"
    $SQLCmd = New-Object System.Data.SQLClient.SQLCommand
    $SQLcmd = $SQLconn.CreateCommand()
    $sqlcmd.commandtimeout=0
    $SQLcmd.CommandText="BACKUP DATABASE [$database] 
		TO  DISK = N'$path' 
		WITH  DESCRIPTION = N'$database Full Backup', 
		NOFORMAT, NOINIT,  
		NAME = N'$database-Full Database Backup', 
		SKIP, NOREWIND, NOUNLOAD"
    Try
	{
        $SQLcmd.Executenonquery() | Out-Null
  	}
    Catch
	{
		Throw $_
    }
    Finally
	{
        $SQLconn.close()
    }

    $backup = new-object PSObject -Property @{
        Server = $Server;
        Database = $Database;
		BackupFile = $path;
		Messages = $output}
	Return $backup
}

Calling the Function

$path = "\\MyServer\G$\SQL Backups\MyDB.bak"
Backup-SqlDatabase -Server ThisServer -Database "MyDB" -Verbose -Path $path |
	Format-List Server,Database,Path,Messages

The output

Event Messages Captured

EventMessages Captured

Convenient Way of Restarting Powershell at a Functional Step

If you happen to have a Powershell script which contains several or more major functional processes, you may find your self having to restart your process at a particular process. With scripting, it is usually relatively easy to “rip” out the steps you need to rerun after a crash and then just run those steps. In fact, you may even end up creating a new script for each functional process.

I have a multi-step process which must run daily. The entire process is very fragile and can experience problems in any of the steps it performs. I’ve been able to identify and circumvent many of the problems but it can still break in various sections. Because each functional process is long running, I need to be able to restart the process at any of the functional steps and continue from there.

The easiest way for me to accomplish this is by using a Param in conjunction with a ‘Switch’ statement. The Param identifies which function you want to start with, a Hashtable is used to convert the  identifier to a number, and a Switch statement wraps all the functions. Because the nature of a Switch statement is to continue testing values after finding the first one (unless you include a Break), you can effectively begin at a point and continue processing each function.

My example is seriously scaled down version but I believe it conveys the idea. The functional areas of my process are:

  1. Download – I have to download a file from a vendor FTP site
  2. Decrypt – the downloaded file needs to be decrypted
  3. Extract – the decrypted file is a Zip and I need to extract the contents
  4. Format – I have to reconstruct an entire Database using SQL files contained in the Zip extraction. The existing database is deleted, created, and structures added.
  5. Import – BCP is used to load the database tables with data from files in the Zip extract.

Here is the example

[CmdletBinding()]
Param(
	[Parameter(Position=0, Mandatory=$false)]
	[ValidateSet("Download","Decrypt","Extract","Format","Import")]
	[string]$StartAt = "Download"
)
#requires –Version 2.0
Set-StrictMode -Version "Latest"
# Equate our StartWith parameter with a hashtable
$step = @{Download = 1; Decrypt = 2; Extract = 3; Format = 4; Import = 5}

Switch($step[$StartAt]){
	1 { Write-Output "Download function will run" }
	{$_ -le 2} { Write-Output "Decrypt Function will run" }
	{$_ -le 3} { Write-Output "Extract Function will run" }
	{$_ -le 4} { Write-Output "Format Function will run" }
	{$_ -le 5} { Write-Output "Import Function will run" }
}

Superfast Powershell Script Gets SQL Server Log Utilization

This is a nifty Powershell script which uses DBCC sqlperf(logspace) to get the log utilization for all your databases on all your servers. Best of all, it is super quick! I have over a dozen servers from 2000 to 20008 with numerous databases on each. This routine returns all my log information in a matter of seconds! It is also an example of developing your own Powershell for piping objects in your scripts. This little endeavor has taught me the real value of returning objects from the “GETS” I develop.

Measure-Command determined the collection process for ALL my servers took less than a second!

Measure_GetLogSpace

The code is presented in several blocks because of the modular design whereby I can possibly use some of the pieces in other ways. The functions are imbedded in a PowerShell module which has many other SQL helper functions not pertinent to this topic. Take a look at An Introduction to PowerShell Modules which is a really good start for developing and using modules in version 2. Using modules to help centralize my admin functions has been of enormous benefit especially when developing new scripts. I like putting my helper functions into a module because my PowerGUI editor is able to pick them up for intellisense. Love that PowerGUI! Also note: everything presented is dependent on at least Powershell V 2.

This first block is the main function which does the actual work of getting and delivering the log information. You will notice the use of Begin… Process… and End… blocks and  a parameter which allows pipeline input.

Function Get-LogSpaceUsed {
<#
.SYNOPSIS
Gets Log space utilization for all databases on an SQL Server instance.

.DESCRIPTION
Uses DBCC sqlperf(logspace) to get log space utilization for all instance databases
for one or multiple servers. Server names can be piped to this function. Output is 
an object or array of objects with ServerName, DatabaseName, PercentLogSpaceUsed,
and LogSizeMb.

.PARAMETER Server
Mandatory: The SQL Server instance which is to be evaluated. Accepts pipeline input.

.EXAMPLE
Get-LogSpaceUsed -Server 'Server\Instance'
Returns log utilization for all databases on an instance.

.EXAMPLE
Get-Content "C:\Scripts\PoSH\Servers.txt" | Get-LogSpaceUsed 
Pipes a list of server names to the function and returns log utilization for
all databases on each server in the list.

.NOTES
Author: John Wood
Date: August, 2012
WEB: http://www.webofwood.com/
email: Johnp.wood@gmail.com
Copyright: (c) 2012 John P. Wood
You are free to copy/use this script as long as you keep these notes and 
attibute credit to the original author
#>	[CmdletBinding()]
	Param(
	[Parameter(Position=0, Mandatory=$true,ValueFromPipeline = $true)] 
	[string]$server
	)

	Begin {
		$alldata = @()
		$query = @"
DECLARE 
	@dbcc varchar(250),
	@scom varchar(250)
	set @dbcc = 'dbcc sqlperf(logspace)'

Create Table #Logspace 
	(
		DatabaseName varchar(250),
		LogSizeMB dec(10,2),
		PercentLogSpaceUsed dec(5,2),
		[Status] int
	)

Insert Into #LogSpace
	EXEC (@dbcc)

SELECT @@SERVERNAME As 'ServerName', DatabaseName, PercentLogSpaceUsed, LogSizeMB 
FROM #LogSpace 
ORDER BY LogSizeMB desc

Drop Table #LogSpace 
"@
	}

	Process{
		Try {
			# Get a new connection
			$conn = New-SqlConnection -server $server
			$ds=New-Object system.Data.DataSet
			$da=New-Object system.Data.SqlClient.SqlDataAdapter($query,$conn)
			$da.fill($ds) | Out-Null
			$da.Dispose()
		}
		Catch { 
			Throw $_.Exception 
		}
		Finally {
			Close-SqlConnection -conn $conn
			$conn = $null

		}
		Foreach($row in $ds.Tables[0].Rows) {
			$alldata += $row
		}
	}
	End {
		Return $alldata
	}
}

A helper function to create a new SQL Connection suitable for running a query

Function New-SqlConnection {
	[CmdletBinding()]
	Param(
		[string]$server=$(Throw "Missing server param"),
		[string]$database="master"
	)
	Process{
		Try {
		    $conn = new-object ('System.Data.SqlClient.SqlConnection')
		    $connString = "Server=$server;Integrated Security=SSPI;Database=$database;Application Name='Powershell App'"
		    $conn.ConnectionString = $connString
		    $conn.StatisticsEnabled = $true
		    $conn.Open()
			$conn
		}
		Catch {Throw $_.Exception }
	}
}

AND….. the script to bring it together and deliver the results

<#
.SYNOPSIS
Gets Log space utilization for all databases on one or more SQL Server instance(s).

.DESCRIPTION
Pipes an array of server names to a module funtion which obtains log utilization
information for each database and presents the rusults

.PARAMETER Servers
Optional: The SQL Server instance which is to be evaluated. If a server or list 
of servers is not presented, the script will use a default list of servers from
a local file.

.EXAMPLE
Get-LogSpaceUsed 
Uses a default servers list from a local file to gather log information 

.EXAMPLE
Get-LogSpaceUsed -servers "Myserver1","myserver2\instance"
Gets log information for databases on two specific servers 

.NOTES
Author: John Wood
Date: August, 2012
WEB: http://www.webofwood.com/
email: Johnp.wood@gmail.com
Copyright: (c) 2012 John P. Wood
You are free to copy/use this script as long as you keep these notes and 
attibute credit to the original author
#>	
[CmdletBinding()]
Param(
	[Parameter(Position=0, Mandatory=$false)] 
	[string[]]$servers = $(%{Get-Content "C:\Scripts\PoSH\Servers.txt"}),
	[Parameter(Position=1, Mandatory=$false)] 
	[int]$maxlogsize = 100
)
$rows = @()
Try{
	$logs = $servers | Get-LogSpaceUsed | Where-Object {$_.LogSizeMb -ge $maxlogsize}
}
Catch {
	Write-Error "$server Server error:"
	$ex = $_.Exception
	Write-Error $ex.Message
	While($ex.InnerException) {
		$ex = $ex.InnerException
		Write-Error $ex.Message
	}
}
$logs

Remove Older SQL Server Job Logs with Multithreaded Powershell

A short time ago I attended a WEB presentation called Speeding Up PowerShell: Multithreading and presented by Tobias Weltner. I have used Start-Job, etc. for running some tasks asynchronously but the presentation convinced me to try multiple threads instead. Watch the presentation and you will see there are many advantages to multiple threads over multiple Jobs.

I recently implemented a script to search all my SQL Servers for SQL Job logs older than 28 days and to remove them. I prefer using a centralized Powershell script to do this because it is easy to add/remove servers to a list rather than create a new task for a new server. The code presented here will operate on all my servers initiating a new Powershell thread for each server. This significantly cuts down on total processing time.

# (C) 2012 Dr. Tobias Weltner
# you may freely use this code for commercial or non-commercial purposes at your own risk
# as long as you credit its original author and keep this comment block.
# For PowerShell training or PowerShell support, feel free to contact tobias.weltner@email.de

# Set a limit for our pool so we don't overload the machine
$throttleLimit = 4
$SessionState = [system.management.automation.runspaces.initialsessionstate]::CreateDefault()
$Pool = [runspacefactory]::CreateRunspacePool(1, $throttleLimit, $SessionState, $Host)
$Pool.Open()

<#	Our script block will remove old SQL Job logs from a server.
	Keep in mind, each thread is run in it's own environment which means it does
	not automitacally enherit your sessions assemblies, variables, etc. Also, it (the thread) does 
	not include a profile. Therefore, you must make available any assemblies or 
	modules for EACH thread. Hence, the inclusion of the SQL assemplies and an
	included module with a number of SQL functions.
#>
$ScriptBlock = {
Param(
	[string]$server
)
[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")
Import-Module SqlDbAdmin

#	Number of days of logs to keep
$days = 28
$LastWrite = (Get-Date).AddDays(-$days)

#	call functions, which are in an included module, to connect to
#	SQL with SMO and to parse the server and log path into a valid UNC 
$smo = New-SMOconnection -server $server
$logpath = Get-UncPath -computername $server -path $smo.Information.ErrorLogPath
$smo.ConnectionContext.Disconnect()

#	Remove only the selected files
get-childitem $logpath -filter '*.txt'  | 
	Where {$_.LastWriteTime -le $LastWrite} | ForEach-Object {
		Remove-Item $_.FullName
		Write-Output "Removed file $($_.FullName)"
	}
}
#	End of Script Block

# 	Get an array list of all our servers
#         You will need to change this to your own path
$servers = @(Get-Content C:\Scripts\PoSH\Servers.txt)

<# 	Start a new thread for each server. The throttle we set up for our pool
	will insure the threads don't overload our machine in case we have a lot 
	of servers #>
$threads = @()
$handles = for ($x = 1; $x -le $servers.Count; $x++) {
	$server = $servers[$x]
	$powershell = [powershell]::Create().AddScript($ScriptBlock).AddArgument($server)
	$powershell.RunspacePool = $Pool
	$powershell.BeginInvoke()
  	$threads += $powershell
}

#	Cycle through and close the threads as each completes
do { 
  $i = 0
  $done = $true
  foreach ($handle in $handles) {
    if ($handle -ne $null) {
  	  if ($handle.IsCompleted) {
        $threads[$i].EndInvoke($handle)
        $threads[$i].Dispose()
        $handles[$i] = $null
      } else {
        $done = $false
      }
    }
    $i++ 
  }
  if (-not $done) { Start-Sleep -Milliseconds 500 }
} until ($done)

There are two Powershell Functions which are called from an included module. You can either include them in the script block or in your own module. For more information on script blocks, see the excellent article ScriptBlocks On the Fly. More information on Powershell 2.0 modules, try the article An Introduction to PowerShell Modules.

The first function you will need is used to get an SMO connection to an SQL Server.

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 {Throw $_.Exception}
	if ($conn.IsOpen -eq $false) {
		Throw "Could not connect to SQL Instance $server."
	}
	$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
	$smo
	}
}

The second function merely creates a UNC path by joining the server machine name with the local path of ErrorLogPath we got from SMO

Function Get-UncPath {
	[CmdletBinding()]
	Param(
		[string]$computername,
		[string]$path
	)
	Process{
	Join-Path "\\$($computername.split('\')[0])" $path.replace(':','$')
	}
}