Stuart Moore

Musings of a Data professional

Stuart Moore

Category: powershell (Page 2 of 4)

A new addition to UK events – PSDay UK

The UK has a great range of techie events these days, but it’s always good to see another one starting up. PSDay.Uk is the first in an international one day PowerShell events.

The idea is to provide a local set of events that complement the 3 big international PowerShell conferences (, PsConf.Asia and PowerShell Global Summit). This makes it easier for local attendees to get to events (no airfare, possibly no hotels, much easier to get agreement from employers), gives an opportunity for local speakers, but also provides a framework to let the organisers tap into the International speakers.

My good friend Rob has written more on the idea and formation on the organising body of which he’s a member over on his blog here – What’s a PSDay

The initial UK event is happening on Friday 22nd September 2017 at CodeSkills in London. Full details and tickets are available on the website – and more information is available on Twitter (@PSDayUK) and Facebook

I’m completely chuffed at being picked to speak at this event as well. I’ll be presenting a new session titled “DevOps by the back door, via the Helpdesk”, where I’ll be sharing examples and ideas of how you can start your DevOps journey with small bite sized actions which will start to show your employer (and colleagues) that this stuff really does work.

Hope to see lots of people there. This sounds like a great project, so make sure you can say you were at the first one in years to come!

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.

Powershell, using ErrorVariable and WarningVariable

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'
Get-ChildItem -Path C:\DoesNotExist\ -ErrorVariable 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

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 {
  Process {
    Get-ChildItem -Path
Test-ErrorVariable -Path c:\DoesNotExist -ErrorAction SilentlyContinue -ErrorVariable 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

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.

Using the Chronometer PowerShell module to analyse module performance

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

Using regex to find PowerShell functions in PowerShell scripts

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:


  • 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.

Removing multiple Backup devices from an SMO restore object in PowerShell

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"

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)

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]

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.

Reading SQL Server Backup file headers directly using PowerShell.

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!).


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

Read More

Easier SQL Server Restores using DBATools

T-SQL Tuesday So for this month’s T-SQL Tuesday Kenneth asked us to talk about backups.

If you’ve been reading my blog for a while, you’ll be aware that I have a ‘thing’ about backups and restores (31 days of posts in a row on a topic shows a slight interest!), and I’ve been speaking at SQL user groups on the subject for years as well.


During my whole DBA career I’ve been aware that the absolute worst thing that can happen to me is having to admit to my employer that I can’t recover their precious data to the point they want (RPO), or do it in a time that they’re happy with (RTO).

To me, if either of those happens it’s a Resume Generating Event! And that’s an event I’ve spent years avoiding. And having those bases covered also stops other mistakes (what, I ran that DROP TABLE in prod?) from becoming RGEs as you’ve got a safety net.

As I’ve moved into management, I’ve also come to realise it’s even worse when you hear from someone who reports to you that they aren’t 100% sure about their backups, this could be 2 RGEs for the price of one!

So I’ve always been hugely keen on backups, and more importantly on ensuring that they’ve been tested and you’ve practiced your restores as much as possible.

SQL Server makes it very easy to perform backups, plenty of options to perform and unless you’ve a fairly large or specialised database setup then there’s fairly little setup required.

Restoring a database, now thats a whole other story. If you’re like most people, you’re taking a full backup each night and then transaction log backups hourly through the day. So at worst case you’re got 25 files to restore.

On a larger system you could have a weekend full backup a twice daily diff backup and 10 minute transaction log backups. Now, these things always fail just before the next full backup so you’re looking as 1 full backup, 13 differential backups and 71 transaction backups to process and restore! Now that’s going to take a while to script out and get going!

SSMS will help you out if you have access to the server that made the backups and it can see msdb, if that whole instance is now a smoking crater, all you’re going to have is a folder full of backup files.

So what to do about it?

Read More

Using the SQLAutoRestores PowerShell Module

PowerShellThe SQLAutoRestores module as currently stands (04/08/2016  v is very much based on my personal usage patterns, but the plan is to make it as flexible as possible. So if you see something in this workflow that you’d like to change, or need something to cope with your SQL Sever backup procedures, then please leave a comment below, or drop in a feature request at github –

Current practice at work is that all SQL Server databases are backed up, either to a central share, or locally and then transferred to the central share. So we end up with a backup folder structure like:

 |    +-DB1
 |    +-DB2

I want to randomly pick a set of backup files to restore. So the first thing I want to do is to get all the ‘Bottom folders’ of this directory tree:

$folders = Get-BottomFolders \\backupserver\share

And if I’ve split the backups across multiple shares I can add more:

$folders += Get-BottomFolders \\server2\backups$\

So now I want to pick a folder at random from those. But, not everyone cleans up after themselves, so there could be folders that don’t contain anything we’re interested in. So there’s a Test-DBBackupsExist function that makes sure we have a file. So we loop until we get something useful:

$RestoreFolder = Get-RandomElement $folders
while (!(Test-DBBackupsExist $RestoreFolder)){
    $RestoreFolder = Get-RandomElement $folders

Great, we’ve got a folder with some SQL Server backups in it. Now we need to see what’s in the backup files. So we do a scan of the file headers, which needs a SQL Server, so we build a SQL Server connection as well:

$SQLconnection = New-SQLConnection 'server1\instance2'
$BackupObjects = Get-DBBackupObject -InputPath $RestoreFolder -ServerInstance $SQLconnection

This returns a simple PowerShell Object containing the header hightlights from each file in the folder.

Note; at this point we’ve not checked we’ve gotten a complete restorable set of files. For all we know, we got 30 Transaction log files and no Full Backup to start from!

I prefer to restore databases to random points in time rather than just the latest available. This gives a wider range of options to compare, and might just mean that you’ll discover than your SAN is corrupting the 22:15 t-log backup.

The next function checks we’ve got at least one ‘anchoring’ full backup, picks the earliest point in time that backup covers, and then gets the latest point in time covered by the backup files, and returns a random point between those 2 extremes. This will be our Recovery Point Objective

$TimeToRestore = Get-PointInTime -BackupsObject $BackupObjects

We then filter out backup files to just those needed to his this point in time:

$Objective = Get-RestoreSet -BackupsObject $BackupObjects -TargetTime $TimeToRestore

Or if you did just want the latest point then you can:

$Objective = Get-RestoreSet -BackupsObject $BackupObjects -Latest

Now we deal with moving the restored database files to a different location:

$Objective = Get-FileRestoreMove -BackupsObject $Objective -DestinationPath e:\some\path

And now we run some tests before the ‘expensive’ time taking restore itself. First off we’ll check we’re not about to clobber another database:

Test-DatabaseExists -RestoreSQLServer $SQLconnection -DatabaseName $Objective[0].DatabaseName

Then we check we have enough space to restore. This includes checking for any file growth during the restore (if your transaction log grows to a stupid size during the day, then it’ll be grown to that size during the restore and sized down later on, so you need to accomdate the largest amount of space your database occupies, not just it’s final size):

Test-RestoreSpace -BackupsObject $Objective -RestoreSQLServer $SQLconnection -RestorePath e:\some\Path

And then we test the difference beteen the SQL Server version of the instance that did the backup and the SQL Server instance we’re asking to perform the restore. Microsoft state that restoring more that 2 major versions isn’t allowed, so we fail it in this case (non SQL Server backups aren’t supported (yet!))

Test-DBRestoreVersion -BackupsOject $Objective -RestoreSQLServer $SQLconnection

And finally we restore the database:

Restore-Database -BackupsObject $Objective -RestoreSQLServer $SQLconnection -RestoreTime $TimeToRestore

Now, we want to check the restore is fine. It is possible to restore a corrupt database with no errors! (Demo and example here) :

Test-Database -DatabaseName $Objective.Databasename -RestoreSQLServer $SQLconnection

And then clean up after ourselves:

Remove-Database -DatabaseName $Objective.Databasename -RestoreSQLServer $SQLconnection

Rinse, repeat ad infinitum. I’ve this process running 24×7 on a dedicated restore instance. On average I restore 80 databases a day and cover every production database in a 2 week windows (it’s random so not guaranteed, but I have a priority list that skews it!)

Currently I collect my statistics with some simple Send-MailMessage usage, but I want something more robust in this module, so thats on the list of things to get fixed before we go to properly.

Hopefully that’s given some ideas on how to use the module. I’d love to hear any ideas on improvements or how you’d want to use it in your environment. Comment here, drop me an email, or ping me on twitter (accounts all linked top right).

PowerShell module to automate restoring SQL Server backups

PowerShellI’ve long been a proponent of automatically restoring as many of your SQL Server backups as you can. There are many benefits of doing this, amongst them:

  • Checking your backups are working, I’ve seen people with backups they can’t restore
  • Knowing how long it will take to recover, your boss isn’t going to be happy if they’re still waiting 3 hours after you said the business would be back up and running
  • Having logs of having restore them, Auditors love this sort of thing
  • Peace of mind knowing that your restores are going to work when the brown stuff hits the reciprocating draught machine.

I’ve been using a collection of scripts and functions based on my previous blogs and presentations on the subjects. But they’ve never really been in a state that I felt comfortable sharing with the world.

Until now:

Read More

Page 2 of 4

Powered by WordPress & Theme by Anders Norén