Musings of a Data professional

Stuart Moore

Month: July 2017

Return debugging information from PowerShell functions via variables

You’re writing a series of PowerShell functions that are to be used in a pipeline (pseudo code):

$Output = Get-Data | Where-Data 

and you want to grab the output of each command but not interfere with the pipeline throughput? Say you want to see what Get-Data is getting and passing into Where-Data

Well, we’re going to look at implementing something close to the built in ErrorVariable and WarningVariable I’ve written about before We’ll start with this pipeline:

$output = Get-Data -Path 'C:\dbatools\standby' | Where-Object {$_.Name -like 'Restore-DbaDatabase.ps1'} 

where we’ll have the following Get-Data function:

function Get-Data{
  Process {
    Get-ChildItem -Path $Path

So nothing too fancy, really just a wrapper around Get-ChildItem, but it gets me a decent data set that I can then filter. Now if this function was also doing some filtering of the data and we weren’t seeing the output we wanted in $ouput then it would be nice to grab the data before it’s passed on. To do this we’ll take a variable name as a parameter and then assign than within the function using Set-Variable:

function Get-Data{
  Process {
    $Data = Get-ChildItem -Path $Path
    if ($ReturnVar){
      Set-Variable -Name $ReturnVar -Value $Data -Scope Global
$output = Get-Data -Path 'C:\dbatools\standby' -ReturnVar ReturnVar | Where-Object {$_.Name -eq 'PointInTime20170707182205.bak'} 

and we’ll get:

Things to note in the function:
we use Set-Variable as this copes better if the variable already exists. Be careful of this as you will clobber the existing variable
Wrap Set-Variable in an if block, or it’ll throw when passed an empty variable name (if you wanted to be really solid, you’d want to put some validation around the parameter to make sure you only take in legal variable names.
We set the scope of the variable to Global to make sure we can see it outside of our command or module.

If you have a large function with lots of points where you’d like the option to return data from various points for future debugging, then you can always have multiple return variable parameters and populate them as you need them.

Creating a Time Series database for SQL Server Restore testing

As a prelude to a post describing some new features of the dbatools Restore-DbaDatabase function, I thought I’d just got through how I create a time series database to give me something to test restoring to Point in Time with minimal overhead.

This sort of database can also be really handy for your own training if you want to play with point in time restores, or explore what can be done with standby restores.


I want to end up with:

  • A database – basic unit of restore
  • Single table – Let’s keep this small and simple
  • Table contains a series of rows written at a set time interval

So the table should end up like this:

StepID DateStamp
3 08/06/2017 09:46:45
4 08/06/2017 09:47:15

We should end up with a nice spread of times so we can easily say ‘Restore to this point in time’ and then be able to check we’ve actually gotten there

Powered by WordPress & Theme by Anders Norén