Author: Stuart Moore Page 9 of 18

If you’re looking to record the error and warning outputs from your PowerShell commands whilst keeping your console free of nasty red text, then the ErrorVariable and WarningVariable parameters are just what you’re looking for.

Let’s take a look at a quick simple example:

Get-ChildItem -Path C:\DoesNotExist\
$ErrVar = 'ErrorVariable'
$ErrVar
Get-ChildItem -Path C:\DoesNotExist\ -ErrorVariable ErrVar
$ErrVar

Which assuming you don’t have C:\DoesNotExist\ on your workstation, then you should see output like this:

From this quick example a couple of things stand out:
* you pass the name for the Error variable in without the $
* If you resuse a variable name it’ll get clobbered, so be careful (or read on)
* The error is still output to the screen

The last one is not what we want. So one more little tweak will make that disapper

Get-ChildItem -Path C:\DoesNotExist\ -ErrorVariable ErrVar -ErrorAction SilentlyContinue
$ErrVar

That’s much better, no more red text! -ErrorAction SilentlyContinue does exactly what it says on the tin.

WarningVariable and WarningAction work in exactly the same way, just on Warning events rather than Errors.

Even better news is that any advanced functions you write automatically get these options:

function Test-ErrorVariable {
  [CmdletBinding()]
  Param(
    [string]$Path
  )
  Process {
    Get-ChildItem -Path
  }
}
Test-ErrorVariable -Path c:\DoesNotExist -ErrorAction SilentlyContinue -ErrorVariable ErrVar
$ErrVar

That’s it, just write an advanced function and PowerShell puts all this in behind the scenes for you

Now, what happens if you’re calling multiple commands and want to store the error output from all of them. Do you need to specify a new ErrorVariable for each one? That could get horribly messy with auto generating variable names in a loop. Luckily there’s a built in method for handling that, prepend the variable name with + and the new data will be added to the variable (which is an array):

Test-ErrorVariable -Path c:\DoesNotExist -ErrorAction SilentlyContinue -ErrorVariable ErrVar
Test-ErrorVariable -Path c:\DoesNotExistEither -ErrorAction SilentlyContinue -ErrorVariable +ErrVar
$ErrVar
$ErrVar.GetType()
$ErrVar[0]
$ErrVar[1]

So you can reuse the same name throughout your script. Don’t worry about when to add the + to the string, behind the scenes PS checks if the variable exists and if it doesn’t creates it the first time it’s used.

These Error and Warning paramters are very useful for collecting information about failed runs, and from hiding nasty screens of red text from end users if you don’t want to worry them.

Whilst going through my blog roll on Feedly this weekend, I spotted Kevin Marquette’s blog post announcing his new Chronometer module.

At a high level it generates a script profiler timeline as you run through a script. Showing you how long each line takes to run, which lines weren’t run and how many times that line has been called.

I’ve just finished writing a number of functions for the dbatools module to implement some new restore functionality, this seemed like a great way to peer into my functions and see what was going on inside, and spot where I was spending most of the time. This should make it easier to work out where I needed to focus attention to improve performance.

At it’s simplest form it can be called like this:

$start = Get-Date
$Chronometer = @{
    Path = 'c:\dbatools\module\functions\Restore-DbaDatabase.ps1'
    Script = {Restore-DbaDatabase -SqlServer localhost\sqlexpress2016 -Path C:\dbatools\backups -WithReplace -OutputScriptOnly}
}
$results = Get-Chronometer @Chronometer -Verbose
(New-TimeSpan -Start $start -End (Get-Date)).TotalSeconds

This took about 10 seconds to run on my test box and dumped out all the coverage for the Restore-DbaDatabase function.

Read More

Whilst having a play with someone else’s code I wanted to quickly find all the function definitions within a module, and then all the function calls within a function definition.

Having had a quick bingle around for a prewritten regex example I didn’t come up with much that fitted the bill. So in the hope that this will help the next person trying to do this here they are:

Assumptions:

• A PowerShell function name is of the form Word-Word
• A PowerShell function definition is of the form “Function Word-Word”
• A Powershell function call can be preceeded by a ‘|’,'(‘, or ‘ ‘
• The script is written using a reasonable style, so there is a ‘ ‘ post call

So to find the function definition I ended up using:

 -match 'function\s(\w+-\w+)'

The function name ends up in $Matches[1]

And for a PowerShell function call:

-match '[^\s|(]\w+-\w+'}

The function name ends up in $Matches[0]

This works accurately enough for what I needed it to do. Feel free to let me know if you can spot any improvements on it.

Probably a bit niche this one, but as I scratched my head trying to work it out I’ll share it to save others in future.

The Problem

Working on some new functionality for DbaTools, adding the ability to cope with striped SQL Server backupsets in fact. Everything’s going pretty swimmingly, I’ve got a process for finding the backup files and grouping them together.

Adding them to the Restore device collection is nice and easy as I have a Object to loop through:

Foreach ($RestoreFile in $RestoreFiles)
{
    $Device = New-Object -TypeName Microsoft.SqlServer.Management.Smo.BackupDeviceItem
    $Device.Name = $RestoreFile.BackupPath
    $Device.devicetype = "File"
    $Restore.Devices.Add($device)
}

nice and simple.

The problem comes when I want to reuse my restore object as I loop through the Differentials and the transaction logs. If these Devices aren’t removed, then SQL Server tries to reuse them on every run. As this code is designed to be run anywhere by anyone I’ve know idea of the number of devices needed. Not only will people stripe their backups across differing numbers of files, but it’s possible to strip your backups differently for each type! So this is perfectly fine:

• Full Backup – Across 4 files
• Differential Backup – Across 2 files
• Transaction Backup – Across 1 file (you CAN strip transaction backups if you want)

and you can even do it differently for different runs.
I’ve found examples for c# if you know the devices names that don’t work in PowerShell. I’ve found methods using Get-Member that don’t appear to be documented, and generally worn a dent in my work desk with my forehead (annoying my co-workers with the dull thud).

The classic PowerShell way of using ForEach fails:

ForEach ($Device in $Restore.Devices)
{
    $Restore.Devices.Remove($Device)
}

Fails with the nice red error message:

An error occurred while enumerating through a collection: Collection was modified; 
enumeration operation may not execute..

The Solution

The oft forgotten while loop!

while ($Restore.Devices.count -gt 0)
{
    $device = $restore.devices[0]
    $restore.devices.remove($Device)
}

No need to know beforehand how many objects you need to remove, though here I’m expecting at least 1. If there was a chance of 0 runs I’d use a do…while loop instead. But as if there wasn’t even 1 backup device to remove I’d have had an error during the restore I’m fairly safe here.

As part of my work on the Backup/Restore portions of DBATools I was interested to see if I could avoid the overhead of having to parse files using SQL Server to find out if they were backup files, and if they were what they contained. (I’m a DBA, ergo I’m paranoid, ergo I’m not trusting a file extension!)

Working on the assumption that SQL Server has a RESTORE HEADERONLY option, and that the speed results are returned with even from large backup files, I was hoping to find all the information I needed fairly early on in the file headers.

In this post I’m going to show how I worked through this using PowerShell (DBATools is trying to avoid using anything other than easily available PS cmdlets, so pulling dlls and other .Net objects would feel a little wrong!).

tl;dr

It sort of works. Not very useful with compressed backups, but reliable for filtering normal backups in a hurry.

Read More