In this article I show how to extract all parameters from Ola Hallengren's maintenancesolution back-ups. We'll use some regular expressions again.

Case

We use Ola Hallengren's magnificent maintenance solution to create sql backups. The stored procedure 'DatabaseBackup' has a lot of parameters, some with defaults. Having the solution on dozens of servers, it is impossible to remember all settings and all choices we once made.
The stored procedure is called with a command which is the command property of a job step, which is tied to a SQL agent job. It's just one string, but it may be a very long one, and the order of the parameters isn't fixed. It's not very practical to use SSMS and start clicking 'SQL Server Agent > Jobs > 4 or more 'DatabaseBackup' jobs > properties > Steps > Edit' to inspect our parameter values on each SQL Server, one by one.

Wouldn't it be nice to have some PowerShell magic to find them all?
Here it is:

using dbatools, of course

Yesterday, 2017-11-13, I found there wasn't a Get-DbaAgentJobStep in dbatools yet, so I created it. While testing this in my company I noticed the commands in maintenancesolution. It reminded me of the trouble we sometimes have with tuning jobs, having them not fighting each other, meeting requirements, documenting and more.
So I decided to work a bit further on this new function and get more information out of those strings.

functions built on functions

It's always a trade off between laziness and future proofing when we need to choose between calling one function inside another or writing from scratch. It saves us the effort of copying a bit or a lot of code, but it makes the calling function dependent on the other one.

Let's start being lazy and just use Get-DbaAgentJobStep as it is. In my new function I'm even going to start defining only one parameter:

function Get-MaintenanceSolutionBackupJobSteps {
    param([Object[]]$sqlinstance)
}

Then we call Get-DbaAgentJobStep with only this $sqlinstance parameter. Since the dbatools function handles multiple instances in a foreach loop, we can submit a collection of instances in this one parameter too.
But we do want it to return only the back-up jobs using Ola's [dbo].[DatabaseBackup] stored procedure.

This is the first risky assumption we need to make:

• do we filter on the job name? A DBA can alter those.
• do we filter on the job step name? A DBA can alter those too.
• do we rely on a description or other job property? I don't know a reliable one.
• do we check the command executed? Let's do that! Even it isn't 100% guaranteed, it's the best choice in my opinion.

(Get-DbaAgentJobStep $sqlinstance).where({$_.command -match 'EXECUTE \[dbo\]\.\[DatabaseBackup\]'})

It doesn't need to be a one liner, but those are fun, not?
Notice the .Where method. If you have PowerShell versions 4 or lower, you'll have to replace that with | Where-Object {}.
$_.command stands for the 'command' property of the Job Step currently processed. We know maintenancesolution creates the stored procedure [dbo].[DatabaseBackup] and calls it in the Job Step in the

• DatabaseBackup - USER_DATABASES - FULL
• DatabaseBackup - USER_DATABASES - DIFF
• DatabaseBackup - USER_DATABASES - LOG
• DatabaseBackup - SYSTEM_DATABASES - FULL

jobs by default. Of course, there may be additional jobs that call the same stored procedure, so we want to catch those too.

The way Ola executes this is with EXECUTE [dbo].[DatabaseBackup] ... but because -match handles regular expressions, we escape the square brackets [ ] with backslashes \\, thus EXECUTE \[dbo\]\.\[DatabaseBackup\].

At this point we have collected the relevant job steps as objects. Let's grab the opportunity to populate some properties we will want to return in the end. In the next step we'll drill down into the command string, and at that level we can no longer reach those important properties:

        $BUConf = @{
        ComputerName = $_.ComputerName
        InstanceName = $_.InstanceName
        SQLInstance = $_.SqlInstance
        AgentJob = $_.AgentJob
        AgentJobStep = $_.Name
        }

That's pretty much self explaining I guess. 'ComputerName','InstanceName','SqlInstance' are three properties we consequently return in all dbatools functions. The names of the 'Agent Job' and 'Agent Job Step' are the grandparent and parent of the command respectively.

Extracting back-up parameters

Ola offers a lot of configuration parameters. Most are mapped to the same parameters in the SQL Server BACKUP DATABASE statement.
Extracting them all with named captures would not be very easy:

• There are only a few required parameters, so most are missing most of the time.
• The order of the parameters is free to choose.
• The syntax of some parameters can be very flexible, like '@Databases'.

We will turn to .split method and -replace operator to start with. The @ sign seems to be the start of a new parameter, so that's a logical choice to begin with. However, when looking a bit closer, there are some text parts in the beginning and end we don't need. If we split on the " first and retain only the middle part ( part two of three, so index [1]), that eliminates some redundant text for us:

($_.command.split('`"')[1].split('@'))[1..255] -replace ('([ ,])*$','')

Let's split this expression in parts:

That's a lot of work done in such a short statement, isn't it?

The result looks like this:

The principle of uncertainty

As we noted above, we have a nice collection of parameters, but we can not be sure which ones the next job will return. There may be several combinations of parameters for each job step. That makes it hard to define a list or object.

We'll add every parameter to a hash table. The parameter name will be the key and the value will be the ... value. :-)
We're not finished with manipulating the strings, though. We want to get rid of those ' \' ' and also the leading ' N\' ' we have in front of the back-up directories for example.

$myKey,$myValue = $_.split('=').trim() -replace "^N\'|\'",''

Again, so much power in such small space. We assign the key and value to two variables in one statement. Splitting on '=' will return two objects for every parameter and they will become the values of $myKey and $myValue respectively. At the same time we have removed leading and trailing spaces with .trim() and replaced those ' \' ' and ' N\' ' with nothing.

We add every key-value pair to our hash table:

        foreach {
        $myKey,$myValue = $_.split('=').trim() -replace "^N\'|\'",''
        $BUConf.Add($myKey,$myValue)
        }

A reliable output

We could return the hash table, but...
the number and order of parameters can be different for every job step we examine. That makes it difficult to show multiple objects in the console, and even more difficult to pipe to Out-Gridview or save as .csv or to a table of any kind.

So we create a custom object that contains every possible property as provided by Ola. Then we populate those we have collected. As a result we will often have job steps with a lot of empty properties. But some of those will have a default when there is no value explicitly given to the stored procedure. It's nice and informative that we can show the actual value instead of $null then.

Coalesce for PowerShell

There are a few different ways to imitate a 'COALESCE' function:

• use .Net methods
• if ($par -eq $null) {'default'} else {$par} or any variation on that, like if (!$par)
• ($par,'default') | select -First 1
• ($par,'default' -ne $null)[0]
• ($par, 'default').Where({$_})[0]

I haven't made thorough comparisons or deep studies, so we'll just pick one:

[PSCUstomObject]@{
            ComputerName = $BUConf.ComputerName
            InstanceName = $BUConf.InstanceName
            AgentJob = $BUConf.SqlInstance
            Databases = $BUConf.Databases
            BackupType = $BUConf.BackupType
            CopyOnly = ($BUConf.CopyOnly, 'N').Where({$_})[0]
            Directory = $BUConf.Directory
            MirrorDirectory = $BUConf.MirrorDirectory
            MirrorCleanupTime = $BUConf.MirrorCleanupTime
            MirrorCleanupMode = ($BUConf.MirrorCleanupMode, 'AFTER_BACKUP').Where({$_})[0]
            AvailabilityGroups = $BUConf.AvailabilityGroups
            Compress = ($BUConf.Compress, 'Server Default').Where({$_})[0]
            CheckSum = ($BUConf.CheckSum, 'N').Where({$_})[0]
            Verify = ($BUConf.Verify, 'N').Where({$_})[0]
            Encrypt = ($BUConf.Encrypt, 'N').Where({$_})[0]
            EncryptionAlgorithm = ($BUConf.EncryptionAlgorithm, 'No Encryption').Where({$_})[0]
            ServerCertificate = $BUConf.ServerCertificate
            ServerAsymmetricKey = $BUConf.ServerAsymmetricKey
            EncryptionKey = $BUConf.EncryptionKey
            ReadWriteFileGroups = ($BUConf.ReadWriteFileGroups, 'N').Where({$_})[0]
            Updateability = ($BUConf.Updateability, 'ALL').Where({$_})[0]
            OverrideBackupPreference = ($BUConf.OverrideBackupPreference, 'N').Where({$_})[0]
            NoRecovery = ($BUConf.NoRecovery, 'N').Where({$_})[0]
            URL = $BUConf.URL
            Credential = $BUConf.Credential
            BackupSoftWare = ($BUConf.BackupSoftware, 'SQL Native').Where({$_})[0]
            Description = $BUConf.Description
            BlockSize = $BUConf.BlockSize
            BufferCount = $BUConf.BufferCount
            MaxTransferSize = $BUConf.MaxTransferSize
            NumberOfFiles = $BUConf.NumberOffiles
            Threads = $BUConf.Threads
            Throttle = $BUConf.Throttle
            CompressionLevel = ($BUConf.CompressionLevel, 0).Where({$_})[0]
            ChangeBackupType = ($BUConf.ChangeBackupType, 'N').Where({$_})[0]
            CleanupTime = $BUConf.CleanupTime
            CleanupMode = ($BUConf.CleanupMode, 'AFTER_BACKUP').Where({$_})[0]
            Execute = ($BUConf.Execute, 'Y').Where({$_})[0]
            LogToTable = ($BUConf.LogToTable, 'N').Where({$_})[0]
        }

We assign every possible key from the hash table to a property of our PSCustomObject. Those that weren't defined will remain $null, except for those where we wrapped the assignment in ($BUConf.LogToTable, 'N').Where({$_})[0] to provide the default when there is no value given.

If $BUConf.LogToTable is $null, it will not pass the .Where({$_}) filter. Index [0] will then point to the second element instead; 'N'.

The Complete script

function Get-MaintenanceSolutionBackupJobSteps {
    param([Object[]]$sqlinstance)
    (Get-DbaAgentJobStep $sqlinstance).where({$_.command -match 'EXECUTE \[dbo\]\.\[DatabaseBackup\]'}) |
     foreach {
        $BUConf = @{
        ComputerName = $_.ComputerName
        InstanceName = $_.InstanceName
        SQLInstance = $_.SqlInstance
        AgentJob = $_.AgentJob
        AgentJobStep = $_.Name
        }

        ($_.command.split('`"')[1].split('@'))[1..255] -replace ('([ ,])*$','') |
        foreach {
        $myKey,$myValue = $_.split('=').trim() -replace "^N\'|\'",''
        $BUConf.Add($myKey,$myValue)
        }
        [PSCUstomObject]@{
            ComputerName = $BUConf.ComputerName
            InstanceName = $BUConf.InstanceName
            AgentJob = $BUConf.SqlInstance
            Databases = $BUConf.Databases
            BackupType = $BUConf.BackupType
            CopyOnly = ($BUConf.CopyOnly, 'N').Where({$_})[0]
            Directory = $BUConf.Directory
            MirrorDirectory = $BUConf.MirrorDirectory
            MirrorCleanupTime = $BUConf.MirrorCleanupTime
            MirrorCleanupMode = ($BUConf.MirrorCleanupMode, 'AFTER_BACKUP').Where({$_})[0]
            AvailabilityGroups = $BUConf.AvailabilityGroups
            Compress = ($BUConf.Compress, 'Server Default').Where({$_})[0]
            CheckSum = ($BUConf.CheckSum, 'N').Where({$_})[0]
            Verify = ($BUConf.Verify, 'N').Where({$_})[0]
            Encrypt = ($BUConf.Encrypt, 'N').Where({$_})[0]
            EncryptionAlgorithm = ($BUConf.EncryptionAlgorithm, 'No Encryption').Where({$_})[0]
            ServerCertificate = $BUConf.ServerCertificate
            ServerAsymmetricKey = $BUConf.ServerAsymmetricKey
            EncryptionKey = $BUConf.EncryptionKey
            ReadWriteFileGroups = ($BUConf.ReadWriteFileGroups, 'N').Where({$_})[0]
            Updateability = ($BUConf.Updateability, 'ALL').Where({$_})[0]
            OverrideBackupPreference = ($BUConf.OverrideBackupPreference, 'N').Where({$_})[0]
            NoRecovery = ($BUConf.NoRecovery, 'N').Where({$_})[0]
            URL = $BUConf.URL
            Credential = $BUConf.Credential
            BackupSoftWare = ($BUConf.BackupSoftware, 'SQL Native').Where({$_})[0]
            Description = $BUConf.Description
            BlockSize = $BUConf.BlockSize
            BufferCount = $BUConf.BufferCount
            MaxTransferSize = $BUConf.MaxTransferSize
            NumberOfFiles = $BUConf.NumberOffiles
            Threads = $BUConf.Threads
            Throttle = $BUConf.Throttle
            CompressionLevel = ($BUConf.CompressionLevel, 0).Where({$_})[0]
            ChangeBackupType = ($BUConf.ChangeBackupType, 'N').Where({$_})[0]
            CleanupTime = $BUConf.CleanupTime
            CleanupMode = ($BUConf.CleanupMode, 'AFTER_BACKUP').Where({$_})[0]
            Execute = ($BUConf.Execute, 'Y').Where({$_})[0]
            LogToTable = ($BUConf.LogToTable, 'N').Where({$_})[0]
        }
     }
 }

If you save this function in a script you can dotsource it to import the function.

. D:\path\to\Get-MaintenanceSolutionBackupConfig.ps1

Then you can call it like this:

Get-MaintenanceSolutionBackupConfig $myInstances | Out-Gridview

To be continued

I have tested this function just on my own SQL Servers. Since their configurations are very alike, a lot of possible combinations may arise on your machines. If you encounter any problem, please let me know.
Also, if Ola or the SQL Server team decide to add parameters to the back-up procedures, this script will have to be adapted.
We'll give this a bit of time and then see if this function can be modified to become part of dbatools.

Previous Post