# Show me my back-up config

#### Show me my back-up config

14, Nov Klaas

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: expression meaning $_ the current job step
$_.command the command in the current job step $_.command.split('"') the command split on ' " '
$_.command.split('"')[1] the second part of the splitted string ($_.command.split('"')[1].split('@')) that part split on '@'
($_.command.split('"')[1].split('@'))[1..255] all parts except the first one ([0]) '([ ,])*$' any number of spaces and commas at the end of any string
-replace ('([ ,])*$','') replace those spaces and commas by nothing 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.