Tag Archives: multiple

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

	[Parameter(Position=0, Mandatory=$false)]
	[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}

	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" }

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)

<#	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 = {
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

#	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
  	$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) {
        $handles[$i] = $null
      } else {
        $done = $false
  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 {
    Param (
	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)

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 {
	Join-Path "\\$($computername.split('\')[0])" $path.replace(':','$')

Powershell Restore to New SQL Server Database

I don’t often have to restore a database. However, while having to restore a few dozen to set up database mirroring, I came across a few with multiple database files. My first ‘mirroring’ script did not account for this. I quickly wrote a new restore script to accommodate the possibility of multiple files.

          Restore to a NEW Database
          Restores an SQL Server backup file to a new database.
	   1) Uses existing Logical and physical file names and restores to the
	      SQL server default file locations.
	   2) Restores multiple files.
	   3) Can specify NoRecover (necessary for mirrored database)
     .PARAMETER  file
          Full path and file name for the backup file. Must be local
     .PARAMETER  Server
          The name\instance of the SQL Server.
     .PARAMETER  database
          The name of the database to be restored
          PS C:\> Invoke-SqlRestore -file 'D:\Backups\mydb.bak' `
		  	-server 'MyServr\SQLinstance' -database NEWDB
         AUTHOR:    John P. Wood
         CREATED:   July, 2010
         VERSION:   1.0.3
Function New-SMOconnection {
    Param ([string]$server)
	$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server)
	$conn.applicationName = "PowerShell SMO"
	$conn.StatementTimeout = 0
	if ($conn.IsOpen -eq $false) {
		Throw "Could not connect to server $($server)."
	$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
Function Get-FileName {
	$names = $path.Split('\\')
	$names[$names.Count - 1]
Function Invoke-SqlRestore {
	$backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") `
		($backupFile, "File")
	# Get local paths to the Database and Log file locations
	If ($smo.Settings.DefaultFile.Length -eq 0) {$DBPath = $smo.Information.MasterDBPath }
	Else { $DBPath = $smo.Settings.DefaultFile}
	If ($smo.Settings.DefaultLog.Length -eq 0 ) {$DBLogPath = $smo.Information.MasterDBLogPath }
	Else { $DBLogPath = $smo.Settings.DefaultLog}

	# Load up the Restore object settings
	$Restore = new-object Microsoft.SqlServer.Management.Smo.Restore
	$Restore.Action = 'Database'
	$Restore.Database = $database
    $Restore.ReplaceDatabase = $true
	if ($norecovery.IsPresent) { $Restore.NoRecovery = $true }
	Else { $Restore.Norecovery = $false }

	# Get information from the backup file
	$RestoreDetails = $Restore.ReadBackupHeader($smo)
	$DataFiles = $Restore.ReadFileList($smo)

	# Restore all backup files
	ForEach ($DataRow in $DataFiles) {
        $LogicalName = $DataRow.LogicalName
        $PhysicalName = Get-FileName -path $DataRow.PhysicalName
		$RestoreData = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
		$RestoreData.LogicalFileName = $LogicalName
		if ($DataRow.Type -eq "D") {
			# Restore Data file
			$RestoreData.PhysicalFileName = $DBPath + "\" + $PhysicalName
		Else {
			# Restore Log file
			$RestoreData.PhysicalFileName = $DBLogPath + "\" + $PhysicalName

	# If there are two files, assume the next is a Log
	if ($RestoreDetails.Rows.Count -gt 1) {
		$Restore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log
		$Restore.FileNumber = 2
# Get a new connection to the server
$smo = New-SMOconnection -server $server
Write-Host "Starting restore to New Database $database on $server."
Try {
	Invoke-SqlRestore -backupFile $file -smo $smo
Catch {
	$ex = $_.Exception
	Write-Output $ex.message
	$ex = $ex.InnerException
	while ($ex.InnerException) {
		Write-Output $ex.InnerException.message
		$ex = $ex.InnerException
	Throw $ex
Finally {
Write-Host "Restore ended without any errors."
	 1) Uses existing Logical and physical file names and restores to the
	    SQL server default file locations.
	 2) Restores multiple files.
	 3) Can specify NoRecover (necessary for mirrored database)