Downloads:

63,067

Downloads of v 0.9.535:

86

Last Update:

29 Nov 2018

Package Maintainer(s):

Software Author(s):

  • Chrissy LeMaire

Tags:

admin powershell module template dba sqlserver sql tools database

dbatools (PowerShell Module)

This is not the latest version of dbatools (PowerShell Module) available.

  • 1
  • 2
  • 3

0.9.535 | Updated: 29 Nov 2018

Downloads:

63,067

Downloads of v 0.9.535:

86

Maintainer(s):

Software Author(s):

  • Chrissy LeMaire

  • 1
  • 2
  • 3
dbatools (PowerShell Module) 0.9.535

This is not the latest version of dbatools (PowerShell Module) available.

  • 1
  • 2
  • 3

Some Checks Have Failed or Are Not Yet Complete

Not All Tests Have Passed


Validation Testing Passed


Verification Testing Passed

Details

Scan Testing Resulted in Flagged:

This package was submitted (and approved) prior to automated virus scanning integration into the package moderation processs.

We recommend clicking the "Details" link to make your own decision on installing this package.

Details

To install dbatools (PowerShell Module), run the following command from the command line or from PowerShell:

>

To upgrade dbatools (PowerShell Module), run the following command from the command line or from PowerShell:

>

To uninstall dbatools (PowerShell Module), run the following command from the command line or from PowerShell:

>

NOTE: This applies to both open source and commercial editions of Chocolatey.

1. Ensure you are set for organizational deployment

Please see the organizational deployment guide

  • Open Source or Commercial:
    • Proxy Repository - Create a proxy nuget repository on Nexus, Artifactory Pro, or a proxy Chocolatey repository on ProGet. Point your upstream to https://community.chocolatey.org/api/v2. Packages cache on first access automatically. Make sure your choco clients are using your proxy repository as a source and NOT the default community repository. See source command for more information.
    • You can also just download the package and push it to a repository Download

3. Enter your internal repository url

(this should look similar to https://community.chocolatey.org/api/v2)

4. Choose your deployment method:


choco upgrade dbatools -y --source="'STEP 3 URL'" [other options]

See options you can pass to upgrade.

See best practices for scripting.

Add this to a PowerShell script or use a Batch script with tools and in places where you are calling directly to Chocolatey. If you are integrating, keep in mind enhanced exit codes.

If you do use a PowerShell script, use the following to ensure bad exit codes are shown as failures:


choco upgrade dbatools -y --source="'STEP 3 URL'"
$exitCode = $LASTEXITCODE

Write-Verbose "Exit code was $exitCode"
$validExitCodes = @(0, 1605, 1614, 1641, 3010)
if ($validExitCodes -contains $exitCode) {
  Exit 0
}

Exit $exitCode

- name: Ensure dbatools installed
  win_chocolatey:
    name: dbatools
    state: present
    version: 0.9.535
    source: STEP 3 URL

See docs at https://docs.ansible.com/ansible/latest/modules/win_chocolatey_module.html.


chocolatey_package 'dbatools' do
  action    :install
  version  '0.9.535'
  source   'STEP 3 URL'
end

See docs at https://docs.chef.io/resource_chocolatey_package.html.


Chocolatey::Ensure-Package
(
    Name: dbatools,
    Version: 0.9.535,
    Source: STEP 3 URL
);

Requires Otter Chocolatey Extension. See docs at https://inedo.com/den/otter/chocolatey.


cChocoPackageInstaller dbatools
{
   Name     = 'dbatools'
   Ensure   = 'Present'
   Version  = '0.9.535'
   Source   = 'STEP 3 URL'
}

Requires cChoco DSC Resource. See docs at https://github.com/chocolatey/cChoco.


package { 'dbatools':
  provider => 'chocolatey',
  ensure   => '0.9.535',
  source   => 'STEP 3 URL',
}

Requires Puppet Chocolatey Provider module. See docs at https://forge.puppet.com/puppetlabs/chocolatey.


salt '*' chocolatey.install dbatools version="0.9.535" source="STEP 3 URL"

See docs at https://docs.saltstack.com/en/latest/ref/modules/all/salt.modules.chocolatey.html.

5. If applicable - Chocolatey configuration/installation

See infrastructure management matrix for Chocolatey configuration elements and examples.

This package was approved as a trusted package on 29 Nov 2018.

Description

dbatools logo dbatools is sort of like a command-line SQL Server Management Studio. The project initially started out as Start-SqlMigration.ps1, but has now grown into a collection of over 300 commands that help automate SQL Server tasks and encourage best practices.

NOTE: This module requires a minimum of PowerShell v3.

NOTE: This is an automatically updated package. If you find it is out of date by more than a week, please contact the maintainer(s) and let them know the package is no longer updating correctly.


tools\.skipAutoUninstaller
 
tools\chocolateyBeforeModify.ps1
$ErrorActionPreference = 'Stop'

$moduleName = 'dbatools'      # this could be different from package name

$module = Get-Module -Name $moduleName
if ($module) {
    Write-Verbose "Module '$moduleName' is imported into the session. Removing it."
    Remove-Module -Name $moduleName -Force -ErrorAction SilentlyContinue

    if ($lib = [appdomain]::CurrentDomain.GetAssemblies() | Where-Object FullName -like "dbatools, *") {
        Write-Verbose "Found locked DLL files for module '$moduleName'."
        $moduleDir = Split-Path $module.Path -Parent
        if ($lib.Location -like "$moduleDir\*") {
            Write-Warning @"
We have detected dbatools to be already imported from '$moduleDir' and the dll files have been locked and cannot be updated.
Please close all consoles that have dbatools imported (Remove-Module dbatools is NOT enough).
"@
            throw 
        }
    }
}
tools\chocolateyInstall.ps1
$ErrorActionPreference = 'Stop'

$toolsDir   = "$(Split-Path -parent $MyInvocation.MyCommand.Definition)"
$moduleName = 'dbatools'  # this may be different from the package name and different case

if ($PSVersionTable.PSVersion.Major -lt 3) {
    throw "$moduleName) module requires a minimum of PowerShell v3."
}

# module may already be installed outside of Chocolatey
Remove-Module -Name $moduleName -Force -ErrorAction SilentlyContinue

$sourcePath = Join-Path -Path $toolsDir -ChildPath "$modulename\*"
$destPath   = Join-Path -Path $env:ProgramFiles -ChildPath "WindowsPowerShell\Modules\$moduleName"

if ($PSVersionTable.PSVersion.Major -ge 5)
{
    $manifestFile = Join-Path -Path $toolsDir -ChildPath "$moduleName\$moduleName.psd1"
    $manifest     = Test-ModuleManifest -Path $manifestFile -WarningAction Ignore -ErrorAction Stop
    $destPath     = Join-Path -Path $destPath -ChildPath $manifest.Version.ToString()
}

Write-Verbose "Creating destination directory '$destPath' for module."
New-Item -Path $destPath -ItemType Directory -Force -ErrorAction SilentlyContinue | Out-Null

Write-Verbose "Moving '$moduleName' files from '$sourcePath' to '$destPath'."
Move-Item -Path $sourcePath -Destination $destPath -Force

if ($PSVersionTable.PSVersion.Major -lt 4)
{
    $modulePaths = [Environment]::GetEnvironmentVariable('PSModulePath', 'Machine') -split ';'
    if ($modulePaths -notcontains $destPath)
    {
        Write-Verbose "Adding '$destPath' to PSModulePath."
        $newModulePath = @($destPath, $modulePaths) -join ';'

        [Environment]::SetEnvironmentVariable('PSModulePath', $newModulePath, 'Machine')
        $env:PSModulePath = $newModulePath
    }
}
tools\chocolateyUninstall.ps1
$ErrorActionPreference = 'Stop'

$moduleName = 'dbatools'
$sourcePath = Join-Path -Path $env:ProgramFiles -ChildPath "WindowsPowerShell\Modules\$moduleName"

Write-Verbose "Removing all version of '$moduleName' from '$sourcePath'."
Remove-Item -Path $sourcePath -Recurse -Force -ErrorAction SilentlyContinue

if ($PSVersionTable.PSVersion.Major -lt 4) {
    $modulePaths = [Environment]::GetEnvironmentVariable('PSModulePath', 'Machine') -split ';'

    Write-Verbose "Removing '$sourcePath' from PSModulePath."
    $newModulePath = $modulePaths | Where-Object { $_ -ne $sourcePath }

    [Environment]::SetEnvironmentVariable('PSModulePath', $newModulePath, 'Machine')
    $env:PSModulePath = $newModulePath
}
tools\dbatools\.vscode\extensions.json
{
    // See http://go.microsoft.com/fwlink/?LinkId=827846
    // for the documentation about the extensions.json format
    "recommendations": [
        "ms-vscode.PowerShell",
        "dbatools.search"
    ]
}
tools\dbatools\.vscode\settings.json
// Place your settings in this file to overwrite default and user settings.
{
    "files.trimTrailingWhitespace": true,
    "powershell.codeFormatting.preset": "OTBS"
}
tools\dbatools\allcommands.ps1
### DO NOT EDIT THIS FILE DIRECTLY ###
#ValidationTags#Messaging,FlowControl,Pipeline,CodeStyle#
function Add-DbaAgDatabase {
    <#
    .SYNOPSIS
        Adds a database to an availability group on a SQL Server instance.

    .DESCRIPTION
        Adds a database to an availability group on a SQL Server instance.

        Before joining the replica databases to the availablity group, the databases will be initialized with automatic seeding or full/log backup.

   .PARAMETER SqlInstance
        The target SQL Server instance or instances. Server version must be SQL Server version 2012 or higher.

        This should be the primary replica.

    .PARAMETER SqlCredential
        Login to the SqlInstance instance using alternative credentials. Windows and SQL Authentication supported. Accepts credential objects (Get-Credential)

    .PARAMETER Database
        The database or databases to add.

    .PARAMETER AvailabilityGroup
        The availability group where the databases will be added.

    .PARAMETER InputObject
        Enables piping from Get-DbaDatabase, Get-DbaDbSharePoint and more.

    .PARAMETER SeedingMode
        Specifies how the secondary replica will be initially seeded.

        Automatic enables direct seeding. This method will seed the secondary replica over the network. This method does not require you to backup and restore a copy of the primary database on the replica.

        Manual requires you to create a backup of the database on the primary replica and manually restore that backup on the secondary replica.

        If not specified, the setting from the availability group replica will be used. Otherwise the setting will be updated.

    .PARAMETER SharedPath
        The network share where the backups will be backed up and restored from.

        Each SQL Server service account must have access to this share.

        NOTE: If a backup / restore is performed, the backups will be left in tact on the network share.

    .PARAMETER UseLastBackup
        Use the last full backup of database.

    .PARAMETER WhatIf
        Shows what would happen if the command were to run. No actions are actually performed.

    .PARAMETER Confirm
        Prompts you for confirmation before executing any changing operations within the command.

    .PARAMETER EnableException
        By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
        This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
        Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

    .NOTES
        Tags: AvailabilityGroup, HA, AG
        Author: Chrissy LeMaire (@cl), netnerds.net
        Website: https://dbatools.io
        Copyright: (c) 2018 by dbatools, licensed under MIT
        License: MIT https://opensource.org/licenses/MIT

    .LINK
        https://dbatools.io/Add-DbaAgDatabase

    .EXAMPLE
        PS C:\> Add-DbaAgDatabase -SqlInstance sql2017a -AvailabilityGroup ag1 -Database db1, db2 -Confirm

        Adds db1 and db2 to ag1 on sql2017a. Prompts for confirmation.

    .EXAMPLE
        PS C:\> Get-DbaDatabase -SqlInstance sql2017a | Out-GridView -Passthru | Add-DbaAgDatabase -AvailabilityGroup ag1

        Adds selected databases from sql2017a to ag1

    .EXAMPLE
        PS C:\> Get-DbaDbSharePoint -SqlInstance sqlcluster | Add-DbaAgDatabase -AvailabilityGroup SharePoint

        Adds SharePoint databases as found in SharePoint_Config on sqlcluster to ag1 on sqlcluster

    .EXAMPLE
        PS C:\> Get-DbaDbSharePoint -SqlInstance sqlcluster -ConfigDatabase SharePoint_Config_2019 | Add-DbaAgDatabase -AvailabilityGroup SharePoint

        Adds SharePoint databases as found in SharePoint_Config_2019 on sqlcluster to ag1 on sqlcluster
    #>
    [CmdletBinding(SupportsShouldProcess, ConfirmImpact = 'Low')]
    param (
        [DbaInstanceParameter[]]$SqlInstance,
        [PSCredential]$SqlCredential,
        [parameter(Mandatory)]
        [string]$AvailabilityGroup,
        [string[]]$Database,
        [parameter(ValueFromPipeline)]
        [Microsoft.SqlServer.Management.Smo.Database[]]$InputObject,
        [ValidateSet('Automatic', 'Manual')]
        [string]$SeedingMode,
        [string]$SharedPath,
        [switch]$UseLastBackup,
        [switch]$EnableException
    )
    process {
        if ((Test-Bound -ParameterName SqlInstance)) {
            if ((Test-Bound -Not -ParameterName Database) -or (Test-Bound -Not -ParameterName AvailabilityGroup)) {
                Stop-Function -Message "You must specify one or more databases and one Availability Group when using the SqlInstance parameter."
                return
            }
        }

        foreach ($instance in $SqlInstance) {
            $InputObject += Get-DbaDatabase -SqlInstance $instance -SqlCredential $SqlCredential -Database $Database
        }

        foreach ($db in $InputObject) {
            $allbackups = @{
            }

            $Primary = $db.Parent
            # check primary, should be run against primary
            $ag = Get-DbaAvailabilityGroup -SqlInstance $db.Parent -AvailabilityGroup $AvailabilityGroup

            if ($ag.AvailabilityDatabases.Name -contains $db.Name) {
                Stop-Function -Message "$($db.Name) is already joined to $($ag.Name)" -Continue
            }

            $secondaryReplicas = $ag.AvailabilityReplicas | Where-Object Role -eq Secondary | Select-Object -Unique -ExpandProperty Name

            if ($SeedingMode -eq "Automatic") {
                # first check
                if ($Pscmdlet.ShouldProcess($Primary, "Backing up $db to NUL")) {
                    $null = Backup-DbaDatabase -BackupFileName NUL -SqlInstance $Primary -SqlCredential $SqlCredential -Database $db
                }
            }

            if ($Pscmdlet.ShouldProcess($ag.Parent.Name, "Adding availability group $db to $($db.Parent.Name)")) {
                try {
                    $agdb = New-Object Microsoft.SqlServer.Management.Smo.AvailabilityDatabase($ag, $db.Name)
                    # something is up with .net create(), force a stop
                    Invoke-Create -Object $agdb
                    Get-DbaAgDatabase -SqlInstance $ag.Parent -Database $db.Name
                } catch {
                    Stop-Function -Message "Failure" -ErrorRecord $_ -Continue
                }
            }

            foreach ($replica in $secondaryReplicas) {

                $agreplica = Get-DbaAgReplica -SqlInstance $Primary -AvailabilityGroup $ag.name -Replica $replica

                if ($SeedingMode) {
                    $agreplica.SeedingMode = $SeedingMode
                    $agreplica.alter()
                }
                $agreplica.refresh()
                $SeedingModeReplica = $agreplica.SeedingMode

                $primarydb = Get-DbaDatabase -SqlInstance $Primary -SqlCredential $SqlCredential -Database $db.name

                if ($SeedingModeReplica -ne 'Automatic') {
                    try {
                        if (-not $allbackups[$db]) {
                            if ($UseLastBackup) {
                                $allbackups[$db] = Get-DbaBackupHistory -SqlInstance $primarydb.Parent -Database $primarydb.Name -IncludeCopyOnly -Last -EnableException
                            } else {
                                $fullbackup = $primarydb | Backup-DbaDatabase -BackupDirectory $SharedPath -Type Full -EnableException
                                $logbackup = $primarydb | Backup-DbaDatabase -BackupDirectory $SharedPath -Type Log -EnableException
                                $allbackups[$db] = $fullbackup, $logbackup
                            }
                            Write-Message -Level Verbose -Message "Backups still exist on $SharedPath"
                        }
                        if ($Pscmdlet.ShouldProcess("$Secondary", "restoring full and log backups of $primarydb from $Primary")) {
                            # keep going to ensure output is shown even if dbs aren't added well.
                            $null = $allbackups[$db] | Restore-DbaDatabase -SqlInstance $replica -WithReplace -NoRecovery -TrustDbBackupHistory -EnableException
                        }
                    } catch {
                        Stop-Function -Message "Failure" -ErrorRecord $_ -Continue
                    }
                }

                $replicadb = Get-DbaAgDatabase -SqlInstance $replica -SqlCredential $SqlCredential -Database $db.Name -AvailabilityGroup $ag.Name   #credential of secondary !!

                if ($replicadb -and -not ($SeedingModeReplica -eq 'Automatic')) {
                    if ($Pscmdlet.ShouldProcess($ag.Parent.Name, "Joining availability group $db to $($db.Parent.Name)")) {
                        $timeout = 1
                        do {
                            try {
                                Write-Message -Level Verbose -Message "Trying to add $($replicadb.Name) to $replica"
                                $timeout++
                                $replicadb.JoinAvailablityGroup()
                                $replicadb.Refresh()
                                Start-Sleep -Seconds 1
                            } catch {
                                Stop-Function -Message "Error joining database to availability group" -ErrorRecord $_ -Continue
                            }
                        } while (-not $replicadb.IsJoined -and $timeout -lt 10)

                        if ($replicadb.IsJoined) {
                            $replicadb
                        } else {
                            Stop-Function -Continue -Message "Could not join $($replicadb.Name) to $replica"
                        }
                    }
                } else {
                    $replicadb
                }
            }
        }
    }
}
#ValidationTags#Messaging,FlowControl,Pipeline,CodeStyle#
function Add-DbaAgListener {
    <#
    .SYNOPSIS
        Adds a listener to an availability group on a SQL Server instance.

    .DESCRIPTION
        Adds a listener to an availability group on a SQL Server instance.

   .PARAMETER SqlInstance
        The target SQL Server instance or instances. Server version must be SQL Server version 2012 or higher.

    .PARAMETER SqlCredential
        Login to the SqlInstance instance using alternative credentials. Windows and SQL Authentication supported. Accepts credential objects (Get-Credential)

    .PARAMETER AvailabilityGroup
        The Availability Group to which a listener will be bestowed upon.

    .PARAMETER IPAddress
        Sets the IP address of the availability group listener.

    .PARAMETER SubnetMask
        Sets the subnet IP mask of the availability group listener. Defaults to 255.255.255.0.

    .PARAMETER Port
        Sets the port number used to communicate with the availability group. Defaults to 1433.

    .PARAMETER Dhcp
        Indicates whether the listener uses DHCP.

    .PARAMETER Passthru
        Don't create the listener, just pass thru an object that can be further customized before creation.

    .PARAMETER InputObject
        Enables piping from Get-DbaAvailabilityGroup

    .PARAMETER WhatIf
        Shows what would happen if the command were to run. No actions are actually performed.

    .PARAMETER Confirm
        Prompts you for confirmation before executing any changing operations within the command.

    .PARAMETER EnableException
        By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
        This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
        Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

    .NOTES
        Tags: AvailabilityGroup, HA, AG
        Author: Chrissy LeMaire (@cl), netnerds.net
        Website: https://dbatools.io
        Copyright: (c) 2018 by dbatools, licensed under MIT
        License: MIT https://opensource.org/licenses/MIT

    .LINK
        https://dbatools.io/Add-DbaAgListener

    .EXAMPLE
        PS C:\> Add-DbaAgListener -SqlInstance sql2017 -AvailabilityGroup SharePoint -IPAddress 10.0.20.20

        Creates a listener on 10.0.20.20 port 1433 for the SharePoint availability group on sql2017.

    .EXAMPLE
        PS C:\> Get-DbaAvailabilityGroup -SqlInstance sql2017 -AvailabilityGroup availabilitygroup1 | Add-DbaAgListener -Dhcp

        Creates a listener on port 1433 with a dynamic IP for the group1 availability group on sql2017.

    #>
    [CmdletBinding(SupportsShouldProcess, ConfirmImpact = 'Low')]
    param (
        [DbaInstanceParameter[]]$SqlInstance,
        [PSCredential]$SqlCredential,
        [string[]]$AvailabilityGroup,
        [ipaddress]$IPAddress,
        [ipaddress]$SubnetMask = "255.255.255.0",
        [int]$Port = 1433,
        [switch]$Dhcp,
        [switch]$Passthru,
        [parameter(ValueFromPipeline)]
        [Microsoft.SqlServer.Management.Smo.AvailabilityGroup[]]$InputObject,
        [switch]$EnableException
    )
    process {
        if ((Test-Bound -ParameterName SqlInstance) -and (Test-Bound -Not -ParameterName AvailabilityGroup)) {
            Stop-Function -Message "You must specify one or more databases and one or more Availability Groups when using the SqlInstance parameter."
            return
        }

        if ($SqlInstance) {
            $InputObject += Get-DbaAvailabilityGroup -SqlInstance $SqlInstance -SqlCredential $SqlCredential -AvailabilityGroup $AvailabilityGroup
        }

        foreach ($ag in $InputObject) {
            if ((Test-Bound -Not -ParameterName Name)) {
                $Name = $ag.Name
            }
            if ($Pscmdlet.ShouldProcess($ag.Parent.Name, "Adding $($IPAddress.IPAddressToString) to $($ag.Name)")) {
                try {
                    $aglistener = New-Object Microsoft.SqlServer.Management.Smo.AvailabilityGroupListener -ArgumentList $ag, $Name
                    $aglistener.PortNumber = $Port
                    $listenerip = New-Object Microsoft.SqlServer.Management.Smo.AvailabilityGroupListenerIPAddress -ArgumentList $aglistener

                    if (Test-Bound -ParameterName IPAddress) {
                        $listenerip.IPAddress = $IPAddress.IPAddressToString
                        $listenerip.SubnetMask = $SubnetMask.IPAddressToString
                    }

                    $listenerip.IsDHCP = $Dhcp
                    $aglistener.AvailabilityGroupListenerIPAddresses.Add($listenerip)

                    if ($Passthru) {
                        return $aglistener
                    } else {
                        # something is up with .net create(), force a stop
                        Invoke-Create -Object $aglistener
                    }
                } catch {
                    Stop-Function -Message "Failure" -ErrorRecord $_
                }
                Get-DbaAgListener -SqlInstance $ag.Parent -AvailabilityGroup $ag.Name -Listener $Name
            }
        }
    }
}
#ValidationTags#Messaging,FlowControl,Pipeline,CodeStyle#
function Add-DbaAgReplica {
    <#
    .SYNOPSIS
        Adds a replica to an availability group on a SQL Server instance.

    .DESCRIPTION
        Adds a replica to an availability group on a SQL Server instance.

        Automatically creates a database mirroring endpoint if required.

   .PARAMETER SqlInstance
        The target SQL Server instance or instances. Server version must be SQL Server version 2012 or higher.

    .PARAMETER SqlCredential
        Login to the SqlInstance instance using alternative credentials. Windows and SQL Authentication supported. Accepts credential objects (Get-Credential)

    .PARAMETER Name
        The name of the replica. Defaults to the SQL Server instance name.

    .PARAMETER AvailabilityGroup
        The Availability Group to which a replica will be bestowed upon.

    .PARAMETER AvailabilityMode
        Sets the availability mode of the availability group replica. Options are: AsynchronousCommit and SynchronousCommit. SynchronousCommit is default.

    .PARAMETER FailoverMode
        Sets the failover mode of the availability group replica. Options are Automatic and Manual. Automatic is default.

    .PARAMETER BackupPriority
        Sets the backup priority availability group replica. Default is 50.

    .PARAMETER Endpoint
        By default, this command will attempt to find a DatabaseMirror endpoint. If one does not exist, it will create it.

        If an endpoint must be created, the name "hadr_endpoint" will be used. If an alternative is preferred, use Endpoint.

    .PARAMETER Passthru
        Don't create the replica, just pass thru an object that can be further customized before creation.

    .PARAMETER InputObject
        Enables piping from Get-DbaAvailabilityGroup.

    .PARAMETER ConnectionModeInPrimaryRole
        Specifies the connection intent modes of an Availability Replica in primary role. AllowAllConnections by default.

    .PARAMETER ConnectionModeInSecondaryRole
        Specifies the connection modes of an Availability Replica in secondary role. AllowAllConnections by default.

    .PARAMETER ReadonlyRoutingConnectionUrl
        Sets the read only routing connection url for the availability replica.

    .PARAMETER SeedingMode
        Specifies how the secondary replica will be initially seeded.

        Automatic enables direct seeding. This method will seed the secondary replica over the network. This method does not require you to backup and restore a copy of the primary database on the replica.

        Manual requires you to create a backup of the database on the primary replica and manually restore that backup on the secondary replica.

    .PARAMETER Certificate
        Specifies that the endpoint is to authenticate the connection using the certificate specified by certificate_name to establish identity for authorization.

        The far endpoint must have a certificate with the public key matching the private key of the specified certificate.

    .PARAMETER WhatIf
        Shows what would happen if the command were to run. No actions are actually performed.

    .PARAMETER Confirm
        Prompts you for confirmation before executing any changing operations within the command.

    .PARAMETER EnableException
        By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
        This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
        Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

    .NOTES
        Tags: AvailabilityGroup, HA, AG
        Author: Chrissy LeMaire (@cl), netnerds.net
        Website: https://dbatools.io
        Copyright: (c) 2018 by dbatools, licensed under MIT
        License: MIT https://opensource.org/licenses/MIT

    .LINK
        https://dbatools.io/Add-DbaAgReplica

    .EXAMPLE
        PS C:\> Get-DbaAvailabilityGroup -SqlInstance sql2017a -AvailabilityGroup SharePoint | Add-DbaAgReplica -SqlInstance sql2017b

        Adds sql2017b to the SharePoint availability group on sql2017a

    .EXAMPLE
        PS C:\> Get-DbaAvailabilityGroup -SqlInstance sql2017a -AvailabilityGroup SharePoint | Add-DbaAgReplica -SqlInstance sql2017b -FailoverMode Manual

        Adds sql2017b to the SharePoint availability group on sql2017a with a manual failover mode.
    #>
    [CmdletBinding(SupportsShouldProcess, ConfirmImpact = 'Low')]
    param (
        [DbaInstanceParameter[]]$SqlInstance,
        [PSCredential]$SqlCredential,
        [string]$AvailabilityGroup,
        [string]$Name,
        [ValidateSet('AsynchronousCommit', 'SynchronousCommit')]
        [string]$AvailabilityMode = "SynchronousCommit",
        [ValidateSet('Automatic', 'Manual', 'External')]
        [string]$FailoverMode = "Automatic",
        [int]$BackupPriority = 50,
        [ValidateSet('AllowAllConnections', 'AllowReadWriteConnections')]
        [string]$ConnectionModeInPrimaryRole = 'AllowAllConnections',
        [ValidateSet('AllowAllConnections', 'AllowNoConnections', 'AllowReadIntentConnectionsOnly')]
        [string]$ConnectionModeInSecondaryRole = 'AllowAllConnections',
        [ValidateSet('Automatic', 'Manual')]
        [string]$SeedingMode = 'Automatic',
        [string]$Endpoint,
        [switch]$Passthru,
        [string]$ReadonlyRoutingConnectionUrl,
        [string]$Certificate,
        [parameter(ValueFromPipeline)]
        [Microsoft.SqlServer.Management.Smo.AvailabilityGroup]$InputObject,
        [switch]$EnableException
    )
    process {
        if (-not $AvailabilityGroup -and -not $InputObject) {
            Stop-Function -Message "You must specify either AvailabilityGroup or pipe in an availabilty group to continue."
            return
        }

        foreach ($instance in $SqlInstance) {
            try {
                $server = Connect-SqlInstance -SqlInstance $instance -SqlCredential $SqlCredential -MinimumVersion 11
            } catch {
                Stop-Function -Message "Failure" -Category ConnectionError -ErrorRecord $_ -Target $instance -Continue
            }

            if ($Certificate) {
                $cert = Get-DbaDbCertificate -SqlInstance $server -Certificate $Certificate
                if (-not $cert) {
                    Stop-Function -Message "Certificate $Certificate does not exist on $instance" -ErrorRecord $_ -Target $Certificate -Continue
                }
            }

            if ($AvailabilityGroup) {
                $InputObject = Get-DbaAvailabilityGroup -SqlInstance $server -AvailabilityGroup $AvailabilityGroup
            }

            $ep = Get-DbaEndpoint -SqlInstance $server -Type DatabaseMirroring

            if (-not $ep) {
                if ($Pscmdlet.ShouldProcess($server.Name, "Adding endpoint named $Endpoint to $instance")) {
                    if (-not $Endpoint) {
                        $Endpoint = "hadr_endpoint"
                    }
                    $ep = New-DbaEndpoint -SqlInstance $server -Name hadr_endpoint -Type DatabaseMirroring -EndpointEncryption Supported -EncryptionAlgorithm Aes -Certificate $Certificate
                    $null = $ep | Start-DbaEndpoint
                }
            }

            if ((Test-Bound -Not -ParameterName Name)) {
                $Name = $server.DomainInstanceName
            }

            if ($Pscmdlet.ShouldProcess($server.Name, "Creating a replica for $($InputObject.Name) named $Name")) {
                try {
                    $replica = New-Object Microsoft.SqlServer.Management.Smo.AvailabilityReplica -ArgumentList $InputObject, $Name
                    $replica.EndpointUrl = $ep.Fqdn
                    $replica.FailoverMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaFailoverMode]::$FailoverMode
                    $replica.AvailabilityMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaAvailabilityMode]::$AvailabilityMode
                    if ($server.EngineEdition -ne "Standard") {
                        $replica.ConnectionModeInPrimaryRole = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaConnectionModeInPrimaryRole]::$ConnectionModeInPrimaryRole
                        $replica.ConnectionModeInSecondaryRole = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaConnectionModeInSecondaryRole]::$ConnectionModeInSecondaryRole
                    }
                    $replica.BackupPriority = $BackupPriority

                    if ($ReadonlyRoutingConnectionUrl) {
                        $replica.ReadonlyRoutingConnectionUrl = $ReadonlyRoutingConnectionUrl
                    }

                    if ($SeedingMode -and $server.VersionMajor -ge 13) {
                        $replica.SeedingMode = $SeedingMode
                    }

                    if ($Passthru) {
                        return $replica
                    }

                    $defaults = 'ComputerName', 'InstanceName', 'SqlInstance', 'AvailabilityGroup', 'Name', 'Role', 'RollupSynchronizationState', 'AvailabilityMode', 'BackupPriority', 'EndpointUrl', 'SessionTimeout', 'FailoverMode', 'ReadonlyRoutingList'

                    $InputObject.AvailabilityReplicas.Add($replica)
                    $agreplica = $InputObject.AvailabilityReplicas[$Name]
                    Add-Member -Force -InputObject $agreplica -MemberType NoteProperty -Name ComputerName -value $agreplica.Parent.ComputerName
                    Add-Member -Force -InputObject $agreplica -MemberType NoteProperty -Name InstanceName -value $agreplica.Parent.InstanceName
                    Add-Member -Force -InputObject $agreplica -MemberType NoteProperty -Name SqlInstance -value $agreplica.Parent.SqlInstance
                    Add-Member -Force -InputObject $agreplica -MemberType NoteProperty -Name AvailabilityGroup -value $agreplica.Parent.Name
                    Add-Member -Force -InputObject $agreplica -MemberType NoteProperty -Name Replica -value $agreplica.Name # backwards compat

                    Select-DefaultView -InputObject $agreplica -Property $defaults
                } catch {
                    $msg = $_.Exception.InnerException.InnerException.Message
                    if (-not $msg) {
                        $msg = $_
                    }
                    Stop-Function -Message $msg -ErrorRecord $_ -Continue
                }
            }
        }
    }
}
#ValidationTags#Messaging,FlowControl,Pipeline,CodeStyle#
function Add-DbaCmsRegServer {
    <#
    .SYNOPSIS
        Adds registered servers to SQL Server Central Management Server (CMS)

    .DESCRIPTION
        Adds registered servers to SQL Server Central Management Server (CMS). If you need more flexiblity, look into Import-DbaCmsRegServer which
        accepts multiple kinds of input and allows you to add reg servers from different CMSes.

    .PARAMETER SqlInstance
        The target SQL Server instance

    .PARAMETER SqlCredential
        Login to the target instance using alternative credentials. Windows and SQL Authentication supported. Accepts credential objects (Get-Credential)

    .PARAMETER ServerName
        Server Name is the actual SQL instance name (labeled Server Name)

    .PARAMETER Name
        Name is basically the nickname in SSMS CMS interface (labeled Registered Server Name)

    .PARAMETER Description
        Adds a description for the registered server

    .PARAMETER Group
        Adds the registered server to a specific group.

    .PARAMETER InputObject
        Allows the piping of a registered server group

    .PARAMETER WhatIf
        Shows what would happen if the command were to run. No actions are actually performed.

    .PARAMETER Confirm
        Prompts you for confirmation before executing any changing operations within the command.

    .PARAMETER EnableException
        By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.

        This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.

        Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

    .NOTES
        Tags: RegisteredServer, CMS
        Author: Chrissy LeMaire (@cl), netnerds.net

        Website: https://dbatools.io
        Copyright: (c) 2018 by dbatools, licensed under MIT
        License: MIT https://opensource.org/licenses/MIT

    .LINK
        https://dbatools.io/Add-DbaCmsRegServer

    .EXAMPLE
        PS C:\> Add-DbaCmsRegServer -SqlInstance sql2008 -ServerName sql01

        Creates a registered server on sql2008's CMS which points to the SQL Server, sql01. When scrolling in CMS, the name "sql01" will be visible.

    .EXAMPLE
        PS C:\> Add-DbaCmsRegServer -SqlInstance sql2008 -ServerName sql01 -Name "The 2008 Clustered Instance" -Description "HR's Dedicated SharePoint instance"

        Creates a registered server on sql2008's CMS which points to the SQL Server, sql01. When scrolling in CMS, "The 2008 Clustered Instance" will be visible.
        Clearly this is hard to explain ;)

    .EXAMPLE
        PS C:\> Add-DbaCmsRegServer -SqlInstance sql2008 -ServerName sql01 -Group hr\Seattle

        Creates a registered server on sql2008's CMS which points to the SQL Server, sql01. When scrolling in CMS, the name "sql01" will be visible within the Seattle group which is in the hr group.

    .EXAMPLE
        PS C:\> Get-DbaCmsRegServerGroup -SqlInstance sql2008 -Group hr\Seattle | Add-DbaCmsRegServer -ServerName sql01111

        Creates a registered server on sql2008's CMS which points to the SQL Server, sql01. When scrolling in CMS, the name "sql01" will be visible within the Seattle group which is in the hr group.

    #>
    [CmdletBinding(SupportsShouldProcess)]
    param (
        [Alias("ServerInstance", "SqlServer")]
        [DbaInstanceParameter[]]$SqlInstance,
        [PSCredential]$SqlCredential,
        [parameter(Mandatory)]
        [string]$ServerName,
        [string]$Name = $ServerName,
        [string]$Description,
        [object]$Group,
        [parameter(ValueFromPipeline)]
        [Microsoft.SqlServer.Management.RegisteredServers.ServerGroup[]]$InputObject,
        [switch]$EnableException
    )
    process {
        if (-not $InputObject -and -not $SqlInstance) {
            Stop-Function -Message "You must either pipe in a registered server group or specify a sqlinstance"
            return
        }

        # double check in case a null name was bound
        if (-not $Name) {
            $Name = $ServerName
        }

        foreach ($instance in $SqlInstance) {
            if (($Group)) {
                if ($Group -is [Microsoft.SqlServer.Management.RegisteredServers.ServerGroup]) {
                    $InputObject += Get-DbaCmsRegServerGroup -SqlInstance $instance -SqlCredential $SqlCredential -Group $Group.Name
                } else {
                    $InputObject += Get-DbaCmsRegServerGroup -SqlInstance $instance -SqlCredential $SqlCredential -Group $Group
                }
            } else {
                $InputObject += Get-DbaCmsRegServerGroup -SqlInstance $instance -SqlCredential $SqlCredential -Id 1
            }

            if (-not $InputObject) {
                Stop-Function -Message "No matching groups found on $instance" -Continue
            }
        }

        foreach ($reggroup in $InputObject) {
            $parentserver = Get-RegServerParent -InputObject $reggroup

            if ($null -eq $parentserver) {
                Stop-Function -Message "Something went wrong and it's hard to explain, sorry. This basically shouldn't happen." -Continue
            }

            $server = $parentserver.ServerConnection.SqlConnectionObject

            if ($Pscmdlet.ShouldProcess($parentserver.SqlInstance, "Adding $ServerName")) {
                try {
                    $newserver = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer($reggroup, $Name)
                    $newserver.ServerName = $ServerName
                    $newserver.Description = $Description
                    $newserver.Create()

                    Get-DbaCmsRegServer -SqlInstance $server -Name $Name -ServerName $ServerName
                } catch {
                    Stop-Function -Message "Failed to add $ServerName on $($parentserver.SqlInstance)" -ErrorRecord $_ -Continue
                }
            }
        }
    }
    end {
        Test-DbaDeprecation -DeprecatedOn "1.0.0" -Alias Add-DbaRegisteredServer
    }
}
#ValidationTags#Messaging,FlowControl,Pipeline,CodeStyle#
function Add-DbaCmsRegServerGroup {
    <#
    .SYNOPSIS
        Adds registered server groups to SQL Server Central Management Server (CMS)

    .DESCRIPTION
        Adds registered server groups to SQL Server Central Management Server (CMS). If you need more flexibility, look into Import-DbaCmsRegServer which accepts multiple kinds of input and allows you to add reg servers and groups from different CMS.

    .PARAMETER SqlInstance
        The target SQL Server instance

    .PARAMETER SqlCredential
        Login to the target instance using alternative credentials. Windows and SQL Authentication supported. Accepts credential objects (Get-Credential)

    .PARAMETER Name
        The name of the registered server group

    .PARAMETER Description
        The description for the registered server group

    .PARAMETER Group
        The SQL Server Central Management Server group. If no groups are specified, the new group will be created at the root.

    .PARAMETER InputObject
        Allows results from Get-DbaCmsRegServerGroup to be piped in

    .PARAMETER WhatIf
        Shows what would happen if the command were to run. No actions are actually performed.

    .PARAMETER Confirm
        Prompts you for confirmation before executing any changing operations within the command.

    .PARAMETER EnableException
        By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.

        This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.

        Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

    .NOTES
        Tags: RegisteredServer, CMS
        Author: Chrissy LeMaire (@cl), netnerds.net

        Website: https://dbatools.io
        Copyright: (c) 2018 by dbatools, licensed under MIT
        License: MIT https://opensource.org/licenses/MIT

    .LINK
        https://dbatools.io/Add-DbaCmsRegServerGroup

    .EXAMPLE
        PS C:\> Add-DbaCmsRegServerGroup -SqlInstance sql2012 -Name HR

        Creates a registered server group called HR, in the root of sql2012's CMS

    .EXAMPLE
        PS C:\> Add-DbaCmsRegServerGroup -SqlInstance sql2012, sql2014 -Name sub-folder -Group HR

        Creates a registered server group on sql2012 and sql2014 called sub-folder within the HR group

    .EXAMPLE
        PS C:\> Get-DbaCmsRegServerGroup -SqlInstance sql2012, sql2014 -Group HR | Add-DbaCmsRegServerGroup -Name sub-folder

        Creates a registered server group on sql2012 and sql2014 called sub-folder within the HR group of each server

    #>
    [CmdletBinding(SupportsShouldProcess)]
    param (
        [Alias("ServerInstance", "SqlServer")]
        [DbaInstanceParameter[]]$SqlInstance,
        [PSCredential]$SqlCredential,
        [parameter(Mandatory)]
        [string]$Name,
        [string]$Description,
        [string]$Group,
        [parameter(ValueFromPipeline)]
        [Microsoft.SqlServer.Management.RegisteredServers.ServerGroup[]]$InputObject,
        [switch]$EnableException
    )
    process {
        if (-not $InputObject -and -not $SqlInstance) {
            Stop-Function -Message "You must either pipe in a registered server group or specify a sqlinstance"
            return
        }
        foreach ($instance in $SqlInstance) {
            if ((Test-Bound -ParameterName Group)) {
                $InputObject += Get-DbaCmsRegServerGroup -SqlInstance $instance -SqlCredential $SqlCredential -Group $Group
            } else {
                $InputObject += Get-DbaCmsRegServerGroup -SqlInstance $instance -SqlCredential $SqlCredential -Id 1
            }
        }

        foreach ($reggroup in $InputObject) {
            $parentserver = Get-RegServerParent -InputObject $reggroup
            $server = $parentserver.ServerConnection.ServerInstance.SqlConnectionObject

            if ($null -eq $parentserver) {
                Stop-Function -Message "Something went wrong and it's hard to explain, sorry. This basically shouldn't happen." -Continue
            }

            if ($Pscmdlet.ShouldProcess($parentserver.SqlInstance, "Adding $Name")) {
                try {
                    $newgroup = New-Object Microsoft.SqlServer.Management.RegisteredServers.ServerGroup($reggroup, $Name)
                    $newgroup.Description = $Description
                    $newgroup.Create()

                    Get-DbaCmsRegServerGroup -SqlInstance $parentserver.ServerConnection.SqlConnectionObject -Group (Get-RegServerGroupReverseParse -object $newgroup)
                    $parentserver.ServerConnection.Disconnect()
                } catch {
                    Stop-Function -Message "Failed to add $reggroup on $server" -ErrorRecord $_ -Continue
                }
            }
        }
    }
    end {
        Test-DbaDeprecation -DeprecatedOn "1.0.0" -Alias Add-DbaRegisteredServerGroup
    }
}
#ValidationTags#Messaging,FlowControl,Pipeline,CodeStyle#
function Add-DbaComputerCertificate {
    <#
    .SYNOPSIS
        Adds a computer certificate - useful for older systems.

    .DESCRIPTION
        Adds a computer certificate from a local or remote computer.

    .PARAMETER ComputerName
        The target SQL Server instance or instances. Defaults to localhost.

    .PARAMETER Credential
        Allows you to login to $ComputerName using alternative credentials.

    .PARAMETER SecurePassword
        The password for the certificate, if it is password protected.

    .PARAMETER Certificate
        The target certificate object.

    .PARAMETER Path
        The local path to the target certificate object.

    .PARAMETER Store
        Certificate store. Default is LocalMachine.

    .PARAMETER Folder
        Certificate folder. Default is My (Personal).

    .PARAMETER EnableException
        By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
        This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
        Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

    .PARAMETER WhatIf
        If this switch is enabled, no actions are performed but informational messages will be displayed that explain what would happen if the command were to run.

    .PARAMETER Confirm
        If this switch is enabled, you will be prompted for confirmation before executing any operations that change state.

    .NOTES
        Tags: Certificate
        Author: Chrissy LeMaire (@cl), netnerds.net

        Website: https://dbatools.io
        Copyright: (c) 2018 by dbatools, licensed under MIT
        License: MIT https://opensource.org/licenses/MIT

    .EXAMPLE
        PS C:\> Add-DbaComputerCertificate -ComputerName Server1 -Path C:\temp\cert.cer

        Adds the local C:\temp\cert.cer to the remote server Server1 in LocalMachine\My (Personal).

    .EXAMPLE
        PS C:\> Add-DbaComputerCertificate -Path C:\temp\cert.cer

        Adds the local C:\temp\cert.cer to the local computer's LocalMachine\My (Personal) certificate store.

    #>
    [CmdletBinding(SupportsShouldProcess, ConfirmImpact = "Low")]
    param (
        [Alias("ServerInstance", "SqlServer", "SqlInstance")]
        [DbaInstance[]]$ComputerName = $env:COMPUTERNAME,
        [PSCredential]$Credential,
        [Alias("Password")]
        [SecureString]$SecurePassword,
        [parameter(ValueFromPipeline)]
        [System.Security.Cryptography.X509Certificates.X509Certificate2[]]$Certificate,
        [string]$Path,
        [string]$Store = "LocalMachine",
        [string]$Folder = "My",
        [Alias('Silent')]
        [switch]$EnableException
    )

    begin {

        if ($Path) {
            if (!(Test-Path -Path $Path)) {
                Stop-Function -Message "Path ($Path) does not exist." -Category InvalidArgument
                return
            }

            try {
                # This may be too much, but oh well
                $bytes = [System.IO.File]::ReadAllBytes($Path)
                $Certificate = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2
                $Certificate.Import($bytes, $SecurePassword, [System.Security.Cryptography.X509Certificates.X509KeyStorageFlags]::DefaultKeySet)
            } catch {
                Stop-Function -Message "Can't import certificate." -ErrorRecord $_
                return
            }
        }

        #region Remoting Script
        $scriptBlock = {

            param (
                $CertificateData,

                [SecureString]$SecurePassword,

                $Store,

                $Folder
            )

            $cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2
            $cert.Import($CertificateData, $SecurePassword, [System.Security.Cryptography.X509Certificates.X509KeyStorageFlags]::DefaultKeySet)
            Write-Message -Level Verbose -Message "Importing cert to $Folder\$Store"
            $tempStore = New-Object System.Security.Cryptography.X509Certificates.X509Store($Folder, $Store)
            $tempStore.Open('ReadWrite')
            $tempStore.Add($cert)
            $tempStore.Close()

            Write-Message -Level Verbose -Message "Searching Cert:\$Store\$Folder"
            Get-ChildItem "Cert:\$Store\$Folder" -Recurse | Where-Object { $_.Thumbprint -eq $cert.Thumbprint }
        }
        #endregion Remoting Script
    }
    process {
        if (Test-FunctionInterrupt) { return }

        if (-not $Certificate) {
            Stop-Function -Message "You must specify either Certificate or Path" -Category InvalidArgument
            return
        }

        foreach ($cert in $Certificate) {

            try {
                $certData = $cert.Export([System.Security.Cryptography.X509Certificates.X509ContentType]::PFX, $SecurePassword)
            } catch {
                Stop-Function -Message "Can't export certificate" -ErrorRecord $_ -Continue
            }

            foreach ($computer in $ComputerName) {

                if ($PSCmdlet.ShouldProcess("local", "Connecting to $computer to import cert")) {
                    try {
                        Invoke-Command2 -ComputerName $computer -Credential $Credential -ArgumentList $certdata, $SecurePassword, $Store, $Folder -ScriptBlock $scriptblock -ErrorAction Stop |
                            Select-DefaultView -Property FriendlyName, DnsNameList, Thumbprint, NotBefore, NotAfter, Subject, Issuer
                    } catch {
                        Stop-Function -Message "Failure" -ErrorRecord $_ -Target $computer -Continue
                    }
                }
            }
        }
    }
}
#ValidationTags#Messaging,FlowControl,Pipeline,CodeStyle#
function Add-DbaDbMirrorMonitor {
    <#
    .SYNOPSIS
        Creates a database mirroring monitor job that periodically updates the mirroring status for every mirrored database on the server instance.

    .DESCRIPTION
        Creates a database mirroring monitor job that periodically updates the mirroring status for every mirrored database on the server instance.

        Basically executes sp_dbmmonitoraddmonitoring.

    .PARAMETER SqlInstance
        The target SQL Server instance

    .PARAMETER SqlCredential
        Login to the target instance using alternate Windows or SQL Login Authentication. Accepts credential objects (Get-Credential).

    .PARAMETER WhatIf
        Shows what would happen if the command were to run. No actions are actually performed.

    .PARAMETER Confirm
        Prompts you for confirmation before executing any changing operations within the command.

    .PARAMETER EnableException
        By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
        This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
        Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

    .NOTES
        Tags: Mirror, HA
        Author: Chrissy LeMaire (@cl), netnerds.net

        Website: https://dbatools.io
        Copyright: (c) 2018 by dbatools, licensed under MIT
        License: MIT https://opensource.org/licenses/MIT

    .LINK
        https://dbatools.io/Add-DbaDbMirrorMonitor

    .EXAMPLE
        PS C:\> Add-DbaDbMirrorMonitor -SqlInstance sql2008, sql2012

        Creates a database mirroring monitor job that periodically updates the mirroring status for every mirrored database on sql2008 and sql2012.

    #>
    [CmdletBinding(SupportsShouldProcess, ConfirmImpact = 'Low')]
    param (
        [parameter(Mandatory, ValueFromPipeline)]
        [DbaInstanceParameter[]]$SqlInstance,
        [PSCredential]$SqlCredential,
        [switch]$EnableException
    )
    process {
        foreach ($instance in $SqlInstance) {
            try {
                $server = Connect-SqlInstance -SqlInstance $instance -SqlCredential $SqlCredential -MinimumVersion 9
            } catch {
                Stop-Function -Message "Failure" -Category ConnectionError -ErrorRecord $_ -Target $instance -Continue
            }

            if ($Pscmdlet.ShouldProcess($instance, "add mirror monitoring")) {
                try {
                    $server.Query("msdb.dbo.sp_dbmmonitoraddmonitoring")
                    [pscustomobject]@{
                        ComputerName  = $server.ComputerName
                        InstanceName  = $server.ServiceName
                        SqlInstance   = $server.DomainInstanceName
                        MonitorStatus = "Added"
                    }
                } catch {
                    Stop-Function -Message "Failure" -ErrorRecord $_ -Continue
                }
            }
        }
    }
}
#ValidationTags#Messaging,FlowControl,Pipeline,CodeStyle#
function Add-DbaPfDataCollectorCounter {
    <#
    .SYNOPSIS
        Adds a Performance Data Collector Counter.

    .DESCRIPTION
        Adds a Performance Data Collector Counter.

    .PARAMETER ComputerName
        The target computer. Defaults to localhost.

    .PARAMETER Credential
        Allows you to login to $ComputerName using alternative credentials. To use:

        $cred = Get-Credential, then pass $cred object to the -Credential parameter.

    .PARAMETER CollectorSet
        The Collector Set name.

    .PARAMETER Collector
        The Collector name.

    .PARAMETER Counter
        The Counter name. This must be in the form of '\Processor(_Total)\% Processor Time'.

    .PARAMETER InputObject
        Accepts the object output by Get-DbaPfDataCollector via the pipeline.

    .PARAMETER WhatIf
        If this switch is enabled, no actions are performed but informational messages will be displayed that explain what would happen if the command were to run.

    .PARAMETER Confirm
        If this switch is enabled, you will be prompted for confirmation before executing any operations that change state.

    .PARAMETER EnableException
        By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
        This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
        Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

    .NOTES
        Tags: PerfMon
        Author: Chrissy LeMaire (@cl), netnerds.net

        Website: https://dbatools.io
        Copyright: (c) 2018 by dbatools, licensed under MIT
        License: MIT https://opensource.org/licenses/MIT

    .LINK
        https://dbatools.io/Add-DbaPfDataCollectorCounter

    .EXAMPLE
        PS C:\> Add-DbaPfDataCollectorCounter -ComputerName sql2017 -CollectorSet 'System Correlation' -Collector DataCollector01  -Counter '\LogicalDisk(*)\Avg. Disk Queue Length'

        Adds the '\LogicalDisk(*)\Avg. Disk Queue Length' counter within the DataCollector01 collector within the System Correlation collector set on sql2017.

    .EXAMPLE
        PS C:\> Get-DbaPfDataCollector | Out-GridView -PassThru | Add-DbaPfDataCollectorCounter -Counter '\LogicalDisk(*)\Avg. Disk Queue Length' -Confirm

        Allows you to select which Data Collector you'd like to add the counter '\LogicalDisk(*)\Avg. Disk Queue Length' on localhost and prompts for confirmation.

    #>
    [CmdletBinding(SupportsShouldProcess, ConfirmImpact = "Low")]
    param (
        [DbaInstance[]]$ComputerName = $env:COMPUTERNAME,
        [PSCredential]$Credential,
        [Alias("DataCollectorSet")]
        [string[]]$CollectorSet,
        [Alias("DataCollector")]
        [string[]]$Collector,
        [Alias("Name")]
        [parameter(Mandatory, ValueFromPipelineByPropertyName)]
        [object[]]$Counter,
        [parameter(ValueFromPipeline)]
        [object[]]$InputObject,
        [switch]$EnableException
    )
    begin {
        $setscript = {
            $setname = $args[0]; $Addxml = $args[1]
            $set = New-Object -ComObject Pla.DataCollectorSet
            $set.SetXml($Addxml)
            $set.Commit($setname, $null, 0x0003) #add or modify.
            $set.Query($setname, $Null)
        }
    }
    process {
        if ($InputObject.Credential -and (Test-Bound -ParameterName Credential -Not)) {
            $Credential = $InputObject.Credential
        }

        if (($InputObject | Get-Member -MemberType NoteProperty -ErrorAction SilentlyContinue).Count -le 3 -and $InputObject.ComputerName -and $InputObject.Name) {
            # it's coming from Get-DbaPfAvailableCounter
            $ComputerName = $InputObject.ComputerName
            $Counter = $InputObject.Name
            $InputObject = $null
        }

        if (-not $InputObject -or ($InputObject -and (Test-Bound -ParameterName ComputerName))) {
            foreach ($computer in $ComputerName) {
                $InputObject += Get-DbaPfDataCollector -ComputerName $computer -Credential $Credential -CollectorSet $CollectorSet -Collector $Collector
            }
        }

        if ($InputObject) {
            if (-not $InputObject.DataCollectorObject) {
                Stop-Function -Message "InputObject is not of the right type. Please use Get-DbaPfDataCollector or Get-DbaPfAvailableCounter."
                return
            }
        }

        foreach ($object in $InputObject) {
            $computer = $InputObject.ComputerName
            $null = Test-ElevationRequirement -ComputerName $computer -Continue
            $setname = $InputObject.DataCollectorSet
            $collectorname = $InputObject.Name
            $xml = [xml]($InputObject.DataCollectorSetXml)

            foreach ($countername in $counter) {
                $node = $xml.SelectSingleNode("//Name[.='$collectorname']")
                $newitem = $xml.CreateElement('Counter')
                $null = $newitem.PsBase.InnerText = $countername
                $null = $node.ParentNode.AppendChild($newitem)
                $newitem = $xml.CreateElement('CounterDisplayName')
                $null = $newitem.PsBase.InnerText = $countername
                $null = $node.ParentNode.AppendChild($newitem)
            }
            $plainxml = $xml.OuterXml

            if ($Pscmdlet.ShouldProcess("$computer", "Adding $counters to $collectorname with the $setname collection set")) {
                try {
                    $results = Invoke-Command2 -ComputerName $computer -Credential $Credential -ScriptBlock $setscript -ArgumentList $setname, $plainxml -ErrorAction Stop
                    Write-Message -Level Verbose -Message " $results"
                    Get-DbaPfDataCollectorCounter -ComputerName $computer -Credential $Credential -CollectorSet $setname -Collector $collectorname -Counter $counter
                } catch {
                    Stop-Function -Message "Failure importing $Countername to $computer." -ErrorRecord $_ -Target $computer -Continue
                }
            }
        }
    }
}
function Backup-DbaDatabase {
    <#
    .SYNOPSIS
        Backup one or more SQL Sever databases from a single SQL Server SqlInstance.

    .DESCRIPTION
        Performs a backup of a specified type of 1 or more databases on a single SQL Server Instance. These backups may be Full, Differential or Transaction log backups.

    .PARAMETER SqlInstance
        The SQL Server instance hosting the databases to be backed up.

    .PARAMETER SqlCredential
        Credentials to connect to the SQL Server instance if the calling user does not have permission.

    .PARAMETER Database
        The database(s) to process. This list is auto-populated from the server. If unspecified, all databases will be processed.

    .PARAMETER ExcludeDatabase
        The database(s) to exclude. This list is auto-populated from the server.

    .PARAMETER BackupFileName
        The name of the file to backup to. This is only accepted for single database backups.
        If no name is specified then the backup files will be named DatabaseName_yyyyMMddHHmm (i.e. "Database1_201714022131") with the appropriate extension.

        If the same name is used repeatedly, SQL Server will add backups to the same file at an incrementing position.

        SQL Server needs permissions to write to the specified location. Path names are based on the SQL Server (C:\ is the C drive on the SQL Server, not the machine running the script).

        Passing in NUL as the BackupFileName will backup to the NUL: device

    .PARAMETER TimeStampFormat
        By default the command timestamps backups using the format yyyyMMddHHmm. Using this parameter this can be overridden. The timestamp format should be defined using the Get-Date formats, illegal formats will cause an error to be thrown

    .PARAMETER BackupDirectory
        Path in which to place the backup files. If not specified, the backups will be placed in the default backup location for SqlInstance.
        If multiple paths are specified, the backups will be striped across these locations. This will overwrite the FileCount option.

        If the path does not exist, Sql Server will attempt to create it. Folders are created by the Sql Instance, and checks will be made for write permissions.

        File Names with be suffixed with x-of-y to enable identifying striped sets, where y is the number of files in the set and x ranges from 1 to y.

    .PARAMETER ReplaceInName
        If this switch is set, the following list of strings will be replaced in the BackupFileName and BackupDirectory strings:
            instancename - will be replaced with the instance Name
            servername - will be replaced with the server name
            dbname - will be replaced with the database name
            timestamp - will be replaced with the timestamp (either the default, or the format provided)
            backuptype - will be replaced with Full, Log or Differential as appropriate

    .PARAMETER CopyOnly
        If this switch is enabled, CopyOnly backups will be taken. By default function performs a normal backup, these backups interfere with the restore chain of the database. CopyOnly backups will not interfere with the restore chain of the database.

        For more details please refer to this MSDN article - https://msdn.microsoft.com/en-us/library/ms191495.aspx

    .PARAMETER Type
        The type of SQL Server backup to perform. Accepted values are "Full", "Log", "Differential", "Diff", "Database"

    .PARAMETER FileCount
        This is the number of striped copies of the backups you wish to create.    This value is overwritten if you specify multiple Backup Directories.

    .PARAMETER CreateFolder
        If this switch is enabled, each database will be backed up into a separate folder on each of the paths specified by BackupDirectory.

    .PARAMETER CompressBackup
        If this switch is enabled, the function will try to perform a compressed backup if supported by the version and edition of SQL Server. Otherwise, this function will use the server(s) default setting for compression.

    .PARAMETER MaxTransferSize
        Sets the size of the unit of transfer. Values must be a multiple of 64kb.

    .PARAMETER Blocksize
        Specifies the block size to use. Must be one of 0.5KB, 1KB, 2KB, 4KB, 8KB, 16KB, 32KB or 64KB. This can be specified in bytes.
        Refer to https://msdn.microsoft.com/en-us/library/ms178615.aspx for more detail

    .PARAMETER BufferCount
        Number of I/O buffers to use to perform the operation.
        Refer to https://msdn.microsoft.com/en-us/library/ms178615.aspx for more detail

    .PARAMETER Checksum
        If this switch is enabled, the backup checksum will be calculated.

    .PARAMETER Verify
        If this switch is enabled, the backup will be verified by running a RESTORE VERIFYONLY against the SqlInstance

    .PARAMETER WithFormat
        Formats the media as the first step of the backup operation. NOTE: This will set Initialize and SkipTapeHeader to $true.

    .PARAMETER Initialize
        Initializes the media as part of the backup operation.

    .PARAMETER SkipTapeHeader
        Initializes the media as part of the backup operation.

    .PARAMETER InputObject
        Internal parameter

    .PARAMETER AzureBaseUrl
        The URL to the base container of an Azure Storage account to write backups to.

        If specified, the only other parameters than can be used are "NoCopyOnly", "Type", "CompressBackup", "Checksum", "Verify", "AzureCredential", "CreateFolder".

    .PARAMETER AzureCredential
        The name of the credential on the SQL instance that can write to the AzureBaseUrl, only needed if using Storage access keys
        If using SAS credentials, the command will look for a credential with a name matching the AzureBaseUrl

    .PARAMETER NoRecovery
        This is passed in to perform a tail log backup if needed

    .PARAMETER BuildPath
        By default this command will not attempt to create missing paths, this switch will change the behaviour so that it will

    .PARAMETER IgnoreFileChecks
        This switch stops the function from checking for the validity of paths. This can be useful if SQL Server only has read access to the backup area.
        Note, that as we cannot check the path you may well end up with errors.

    .PARAMETER OutputScriptOnly
        Switch causes only the T-SQL script for the backup to be generated. Will not create any paths if they do not exist

    .PARAMETER EnableException
        By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
        This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
        Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

    .PARAMETER WhatIf
        If this switch is enabled, no actions are performed but informational messages will be displayed that explain what would happen if the command were to run.

    .PARAMETER Confirm
        If this switch is enabled, you will be prompted for confirmation before executing any operations that change state.

    .NOTES
        Tags: DisasterRecovery, Backup, Restore
        Author: Stuart Moore (@napalmgram), stuart-moore.com

        Website: https://dbatools.io
        Copyright: (c) 2018 by dbatools, licensed under MIT
        License: MIT https://opensource.org/licenses/MIT

    .EXAMPLE
        PS C:\> Backup-DbaDatabase -SqlInstance Server1 -Database HR, Finance

        This will perform a full database backup on the databases HR and Finance on SQL Server Instance Server1 to Server1 default backup directory.

    .EXAMPLE
        PS C:\> Backup-DbaDatabase -SqlInstance sql2016 -BackupDirectory C:\temp -Database AdventureWorks2014 -Type Full

        Backs up AdventureWorks2014 to sql2016 C:\temp folder.

    .EXAMPLE
        PS C:\> Backup-DbaDatabase -SqlInstance sql2016 -AzureBaseUrl https://dbatoolsaz.blob.core.windows.net/azbackups/ -AzureCredential dbatoolscred -Type Full -CreateFolder

        Performs a full backup of all databases on the sql2016 instance to their own containers under the https://dbatoolsaz.blob.core.windows.net/azbackups/ container on Azure blog storage using the sql credential "dbatoolscred" registered on the sql2016 instance.

    .EXAMPLE
        PS C:\> Backup-DbaDatabase -SqlInstance sql2016 -AzureBaseUrl https://dbatoolsaz.blob.core.windows.net/azbackups/  -Type Full

        Performs a full backup of all databases on the sql2016 instance to the https://dbatoolsaz.blob.core.windows.net/azbackups/ container on Azure blog storage using the Shared Access Signature sql credential "https://dbatoolsaz.blob.core.windows.net/azbackups" registered on the sql2016 instance.

    .EXAMPLE
        PS C:\> Backup-Dbadatabase -SqlInstance Server1\Prod -Database db1 -BackupDirectory \\filestore\backups\servername\instancename\dbname\backuptype -Type Full -ReplaceInName

        Performs a full backup of db1 into the folder \\filestore\backups\server1\prod\db1

    .EXAMPLE
        PS C:\> Backup-Dbadatabase -SqlInstance Server1\Prod -BackupDirectory \\filestore\backups\servername\instancename\dbname\backuptype -BackupFileName dbname-backuptype-timestamp.trn -Type Log -ReplaceInName

        Performs a log backup for every database. For the database db1 this would results in backup files in \\filestore\backups\server1\prod\db1\Log\db1-log-31102018.trn

    .EXAMPLE
        PS C:\> Backup-DbaDatabase -SqlInstance Sql2017 -Database master -BackupFileName NUL

        Performs a backup of master, but sends the output to the NUL device (ie; throws it away)
    #>
    [CmdletBinding(DefaultParameterSetName = "Default", SupportsShouldProcess)]
    [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidUsingPlainTextForPassword", "")] #For AzureCredential
    param (
        [parameter(ParameterSetName = "Pipe", Mandatory)]
        [DbaInstanceParameter]$SqlInstance,
        [PSCredential]$SqlCredential,
        [Alias("Databases")]
        [object[]]$Database,
        [object[]]$ExcludeDatabase,
        [string[]]$BackupDirectory,
        [string]$BackupFileName,
        [switch]$ReplaceInName,
        [switch]$CopyOnly,
        [ValidateSet('Full', 'Log', 'Differential', 'Diff', 'Database')]
        [string]$Type = 'Database',
        [parameter(ParameterSetName = "NoPipe", Mandatory, ValueFromPipeline)]
        [object[]]$InputObject,
        [switch]$CreateFolder,
        [int]$FileCount = 0,
        [switch]$CompressBackup,
        [switch]$Checksum,
        [switch]$Verify,
        [int]$MaxTransferSize,
        [int]$BlockSize,
        [int]$BufferCount,
        [string]$AzureBaseUrl,
        [string]$AzureCredential,
        [switch]$NoRecovery,
        [switch]$BuildPath,
        [switch]$WithFormat,
        [switch]$Initialize,
        [switch]$SkipTapeHeader,
        [string]$TimeStampFormat,
        [switch]$IgnoreFileChecks,
        [switch]$OutputScriptOnly,
        [Alias('Silent')]
        [switch]$EnableException
    )

    begin {
        if (-not (Test-Bound 'TimeStampFormat')) {
            Write-Message -Message 'Setting Default timestampformat' -Level Verbose
            $TimeStampFormat = "yyyyMMddHHmm"
        }
        if ($SqlInstance.length -ne 0) {
            try {
                $Server = Connect-SqlInstance -SqlInstance $SqlInstance -SqlCredential $SqlCredential -AzureUnsupported
            } catch {
                Stop-Function -Message "Cannot connect to $SqlInstance" -ErrorRecord $_
                return
            }

            if ($Database) {
                $InputObject = $server.Databases | Where-Object Name -in $Database
            } else {
                $InputObject = $server.Databases | Where-Object Name -ne 'tempdb'
            }

            if ($ExcludeDatabase) {
                $InputObject = $InputObject | Where-Object Name -notin $ExcludeDatabase
            }

            if ($null -eq $BackupDirectory -and $backupfileName -ne 'NUL') {
                Write-Message -Message 'No backupfolder passed in, setting it to instance default' -Level Verbose
                $BackupDirectory = (Get-DbaDefaultPath -SqlInstance $SqlInstance).Backup
            }

            if ($BackupDirectory.Count -gt 1) {
                Write-Message -Level Verbose -Message "Multiple Backup Directories, striping"
                $Filecount = $BackupDirectory.Count
            }

            if ($InputObject.Count -gt 1 -and $BackupFileName -ne '' -and $True -ne $ReplaceInFile) {
                Stop-Function -Message "1 BackupFile specified, but more than 1 database."
                return
            }

            if (($MaxTransferSize % 64kb) -ne 0 -or $MaxTransferSize -gt 4mb) {
                Stop-Function -Message "MaxTransferSize value must be a multiple of 64kb and no greater than 4MB"
                return
            }
            if ($BlockSize) {
                if ($BlockSize -notin (0.5kb, 1kb, 2kb, 4kb, 8kb, 16kb, 32kb, 64kb)) {
                    Stop-Function -Message "Block size must be one of 0.5kb,1kb,2kb,4kb,8kb,16kb,32kb,64kb"
                    return
                }
            }
            if ('' -ne $AzureBaseUrl) {
                $AzureBaseUrl = $AzureBaseUrl.Trim("/")
                if ('' -ne $AzureCredential) {
                    Write-Message -Message "Azure Credential name passed in, will proceed assuming it's value" -Level Verbose
                    $FileCount = 1
                } else {
                    Write-Message -Message "AzureUrl and no credential, testing for SAS credential"
                    if (Get-DbaCredential -SqlInstance $server -Name $AzureBaseUrl) {
                        Write-Message -Message "Found a SAS backup credental" -Level Verbose
                    } else {
                        Stop-Function -Message "You must provide the credential name for the Azure Storage Account"
                        return
                    }
                }
                $BackupDirectory = $AzureBaseUrl
            }

            if ($OutputScriptOnly) {
                $IgnoreFileChecks = $true
            }
        }
    }

    process {
        if (!$SqlInstance -and !$InputObject) {
            Stop-Function -Message "You must specify a server and database or pipe some databases"
            return
        }

        Write-Message -Level Verbose -Message "$($InputObject.Count) database to backup"

        foreach ($Database in $InputObject) {
            $ProgressId = Get-Random
            $failures = @()
            $dbname = $Database.Name

            if ($dbname -eq "tempdb") {
                Stop-Function -Message "Backing up tempdb not supported" -Continue
            }

            if ('Normal' -notin ($Database.Status -split ',')) {
                Stop-Function -Message "Database status not Normal. $dbname skipped." -Continue
            }

            if ($Database.DatabaseSnapshotBaseName) {
                Stop-Function -Message "Backing up snapshots not supported. $dbname skipped." -Continue
            }

            if ($null -eq $server) { $server = $Database.Parent }

            Write-Message -Level Verbose -Message "Backup database $database"

            if ($null -eq $Database.RecoveryModel) {
                $Database.RecoveryModel = $server.Databases[$Database.Name].RecoveryModel
                Write-Message -Level Verbose -Message "$dbname is in $($Database.RecoveryModel) recovery model"
            }

            # Fixes one-off cases of StackOverflowException crashes, see issue 1481
            $dbRecovery = $Database.RecoveryModel.ToString()
            if ($dbRecovery -eq 'Simple' -and $Type -eq 'Log') {
                $failreason = "$database is in simple recovery mode, cannot take log backup"
                $failures += $failreason
                Write-Message -Level Warning -Message "$failreason"
            }

            $lastfull = $database.Refresh().LastBackupDate.Year

            if ($Type -notin @("Database", "Full") -and $lastfull -eq 1) {
                $failreason = "$database does not have an existing full backup, cannot take log or differentialbackup"
                $failures += $failreason
                Write-Message -Level Warning -Message "$failreason"
            }

            if ($CopyOnly -ne $true) {
                $CopyOnly = $false
            }

            $server.ConnectionContext.StatementTimeout = 0
            $backup = New-Object Microsoft.SqlServer.Management.Smo.Backup
            $backup.Database = $Database.Name
            $Suffix = "bak"

            if ($CompressBackup) {
                if ($database.EncryptionEnabled) {
                    Write-Message -Level Warning -Message "$dbname is enabled for encryption, will not compress"
                    $backup.CompressionOption = 2
                } elseif ($server.Edition -like 'Express*' -or ($server.VersionMajor -eq 10 -and $server.VersionMinor -eq 0 -and $server.Edition -notlike '*enterprise*') -or $server.VersionMajor -lt 10) {
                    Write-Message -Level Warning -Message "Compression is not supported with this version/edition of Sql Server"
                } else {
                    Write-Message -Level Verbose -Message "Compression enabled"
                    $backup.CompressionOption = 1
                }
            }

            if ($Checksum) {
                $backup.Checksum = $true
            }

            if ($Type -in 'Diff', 'Differential') {
                Write-Message -Level VeryVerbose -Message "Creating differential backup"
                $SMOBackuptype = "Database"
                $backup.Incremental = $true
                $outputType = 'Differential'
            }
            $Backup.NoRecovery = $false
            if ($Type -eq "Log") {
                Write-Message -Level VeryVerbose -Message "Creating log backup"
                $Suffix = "trn"
                $OutputType = 'Log'
                $SMOBackupType = 'Log'
                $Backup.NoRecovery = $NoRecovery
            }

            if ($Type -in 'Full', 'Database') {
                Write-Message -Level VeryVerbose -Message "Creating full backup"
                $SMOBackupType = "Database"
                $OutputType = 'Full'
            }

            $backup.CopyOnly = $copyonly
            $backup.Action = $SMOBackupType
            if ('' -ne $AzureBaseUrl -and $null -ne $AzureCredential) {
                $backup.CredentialName = $AzureCredential
            }

            Write-Message -Level Verbose -Message "Building file name"

            $BackupFinalName = ''
            $FinalBackupPath = @()
            $timestamp = Get-Date -Format $TimeStampFormat
            if ('NUL' -eq $BackupFileName) {
                $FinalBackupPath += 'NUL:'
                $IgnoreFileChecks = $true
            } elseif ('' -ne $BackupFileName) {
                $File = New-Object System.IO.FileInfo($BackupFileName)
                $BackupFinalName = $file.Name
                $suffix = $file.extension -Replace '^\.', ''
                if ( '' -ne (Split-Path $BackupFileName)) {
                    Write-Message -Level Verbose -Message "Fully qualified path passed in"
                    $FinalBackupPath += [IO.Path]::GetFullPath($file.DirectoryName)
                }
            } else {
                Write-Message -Level VeryVerbose -Message "Setting filename - $timestamp"
                $BackupFinalName = "$($dbname)_$timestamp.$suffix"
            }

            Write-Message -Level Verbose -Message "Building backup path"
            if ($FinalBackupPath.Count -eq 0) {
                $FinalBackupPath += $BackupDirectory
            }

            if ($BackupDirectory.Count -eq 1 -and $Filecount -gt 1) {
                for ($i = 0; $i -lt ($Filecount - 1); $i++) {
                    $FinalBackupPath += $FinalBackupPath[0]
                }
            }

            if ($AzureBaseUrl -or $AzureCredential) {
                $slash = "/"
            } else {
                $slash = "\"
            }
            if ($FinalBackupPath.Count -gt 1) {
                $File = New-Object System.IO.FileInfo($BackupFinalName)
                for ($i = 0; $i -lt $FinalBackupPath.Count; $i++) {
                    $FinalBackupPath[$i] = $FinalBackupPath[$i] + $slash + $($File.BaseName) + "-$($i+1)-of-$FileCount.$suffix"
                }
            } elseif ($FinalBackupPath[0] -ne 'NUL:') {
                $FinalBackupPath[0] = $FinalBackupPath[0] + $slash + $BackupFinalName
            }

            if ($CreateFolder -and $FinalBackupPath[0] -ne 'NUL:') {
                for ($i = 0; $i -lt $FinalBackupPath.Count; $i++) {
                    $parent = [IO.Path]::GetDirectoryName($FinalBackupPath[$i])
                    $leaf = [IO.Path]::GetFileName($FinalBackupPath[$i])
                    $FinalBackupPath[$i] = [IO.Path]::Combine($parent, $dbname, $leaf)
                }
            }

            if ($True -eq $ReplaceInName) {
                for ($i = 0; $i -lt $FinalBackupPath.count; $i++) {
                    $FinalBackupPath[$i] = $FinalBackupPath[$i] -replace ('dbname', $dbname)
                    $FinalBackupPath[$i] = $FinalBackupPath[$i] -replace ('instancename', $SqlInstance.InstanceName)
                    $FinalBackupPath[$i] = $FinalBackupPath[$i] -replace ('servername', $SqlInstance.ComputerName)
                    $FinalBackupPath[$i] = $FinalBackupPath[$i] -replace ('timestamp', $timestamp)
                    $FinalBackupPath[$i] = $FinalBackupPath[$i] -replace ('backuptype', $outputType)
                }
            }

            if (-not $IgnoreFileChecks -and -not $AzureBaseUrl) {
                $parentPaths = ($FinalBackupPath | ForEach-Object { Split-Path $_ } | Select-Object -Unique)
                foreach ($parentPath in $parentPaths) {
                    if (-not (Test-DbaPath -SqlInstance $server -Path $parentPath)) {
                        if (($BuildPath -eq $true) -or ($CreateFolder -eq $True)) {
                            $null = New-DbaDirectory -SqlInstance $server -Path $parentPath
                        } else {
                            $failreason += "SQL Server cannot check if $parentPath exists. You can try disabling this check with -IgnoreFileChecks"
                            $failures += $failreason
                            Write-Message -Level Warning -Message "$failreason"
                        }
                    }
                }
            }


            if ('' -eq $AzureBaseUrl -and $BackupDirectory) {
                $FinalBackupPath = $FinalBackupPath | ForEach-Object { [IO.Path]::GetFullPath($_) }
            }


            $script = $null
            $backupComplete = $false

            if (!$failures) {
                $Filecount = $FinalBackupPath.Count

                foreach ($backupfile in $FinalBackupPath) {
                    $device = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem
                    if ('' -ne $AzureBaseUrl) {
                        $device.DeviceType = "URL"
                    } else {
                        $device.DeviceType = "File"
                    }

                    if ($WithFormat) {
                        Write-Message -Message "WithFormat specified. Ensuring Initialize and SkipTapeHeader are set to true." -Level Verbose
                        $Initialize = $true
                        $SkipTapeHeader = $true
                    }

                    $backup.FormatMedia = $WithFormat
                    $backup.Initialize = $Initialize
                    $backup.SkipTapeHeader = $SkipTapeHeader
                    $device.Name = $backupfile
                    $backup.Devices.Add($device)
                }
                $humanBackupFile = $FinalBackupPath -Join ','
                Write-Message -Level Verbose -Message "Devices added"
                $percent = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] {
                    Write-Progress -id $ProgressId -activity "Backing up database $dbname to $humanBackupFile" -percentcomplete $_.Percent -status ([System.String]::Format("Progress: {0} %", $_.Percent))
                }
                $backup.add_PercentComplete($percent)
                $backup.PercentCompleteNotification = 1
                $backup.add_Complete($complete)

                if ($MaxTransferSize) {
                    $backup.MaxTransferSize = $MaxTransferSize
                }
                if ($BufferCount) {
                    $backup.BufferCount = $BufferCount
                }
                if ($BlockSize) {
                    $backup.Blocksize = $BlockSize
                }

                Write-Progress -id $ProgressId -activity "Backing up database $dbname to $humanBackupFile" -percentcomplete 0 -status ([System.String]::Format("Progress: {0} %", 0))

                try {
                    if ($Pscmdlet.ShouldProcess($server.Name, "Backing up $dbname to $humanBackupFile")) {
                        $CurrentLsn = (Read-DbaTransactionLog -SqlInstance $server -Database $dbname -RowLimit 1).'Current LSN'
                        $NumericLSN = (Convert-DbaLSN -LSN $CurrentLsn).Numeric
                        if ($OutputScriptOnly -ne $True) {
                            $Filelist = @()
                            $FileList += $server.Databases[$dbname].FileGroups.Files | Select-Object @{ Name = "FileType"; Expression = { "D" } }, @{ Name = "Type"; Expression = { "D" } }, @{ Name = "LogicalName"; Expression = { $_.Name } }, @{ Name = "PhysicalName"; Expression = { $_.FileName } }
                            $FileList += $server.Databases[$dbname].LogFiles | Select-Object @{ Name = "FileType"; Expression = { "L" } }, @{ Name = "Type"; Expression = { "L" } }, @{ Name = "LogicalName"; Expression = { $_.Name } }, @{ Name = "PhysicalName"; Expression = { $_.FileName } }

                            $backup.SqlBackup($server)
                            $script = $backup.Script($server)
                            Write-Progress -id $ProgressId -activity "Backing up database $dbname to $backupfile" -status "Complete" -Completed
                            $BackupComplete = $true
                            if ($server.VersionMajor -eq '8') {
                                $HeaderInfo = Get-BackupAncientHistory -SqlInstance $server -Database $dbname
                            } else {
                                $HeaderInfo = Get-DbaBackupHistory -SqlInstance $server -Database $dbname -Last -IncludeCopyOnly -LastLsn $NumericLsn -RecoveryFork $database.RecoveryForkGuid  | Sort-Object -Property End -Descending | Select-Object -First 1
                            }
                            $Verified = $false
                            if ($Verify) {
                                $verifiedresult = [PSCustomObject]@{
                                    SqlInstance          = $server.name
                                    DatabaseName         = $dbname
                                    BackupComplete       = $BackupComplete
                                    BackupFilesCount     = $FinalBackupPath.Count
                                    BackupFile           = (Split-Path $FinalBackupPath -Leaf)
                                    BackupFolder         = (Split-Path $FinalBackupPath | Sort-Object -Unique)
                                    BackupPath           = ($FinalBackupPath | Sort-Object -Unique)
                                    Script               = $script
                                    Notes                = $failures -join (',')
                                    FullName             = ($FinalBackupPath | Sort-Object -Unique)
                                    FileList             = $FileList
                                    SoftwareVersionMajor = $server.VersionMajor
                                    Type                 = $outputType
                                    FirstLsn             = $HeaderInfo.FirstLsn
                                    DatabaseBackupLsn    = $HeaderInfo.DatabaseBackupLsn
                                    CheckPointLsn        = $HeaderInfo.CheckPointLsn
                                    LastLsn              = $HeaderInfo.LastLsn
                                    BackupSetId          = $HeaderInfo.BackupSetId
                                    LastRecoveryForkGUID = $HeaderInfo.LastRecoveryForkGUID
                                } | Restore-DbaDatabase -SqlInstance $server -DatabaseName DbaVerifyOnly -VerifyOnly -TrustDbBackupHistory -DestinationFilePrefix DbaVerifyOnly
                                if ($verifiedResult[0] -eq "Verify successful") {
                                    $failures += $verifiedResult[0]
                                    $Verified = $true
                                } else {
                                    $failures += $verifiedResult[0]
                                    $Verified = $false
                                }
                            }
                            $HeaderInfo | Add-Member -Type NoteProperty -Name BackupComplete -Value $BackupComplete
                            $HeaderInfo | Add-Member -Type NoteProperty -Name BackupFile -Value (Split-Path $FinalBackupPath -Leaf)
                            $HeaderInfo | Add-Member -Type NoteProperty -Name BackupFilesCount -Value $FinalBackupPath.Count
                            if ($FinalBackupPath[0] -eq 'NUL:') {
                                $pathresult = "NUL:"
                            } else {
                                $pathresult = (Split-Path $FinalBackupPath | Sort-Object -Unique)
                            }
                            $HeaderInfo | Add-Member -Type NoteProperty -Name BackupFolder -Value $pathresult
                            $HeaderInfo | Add-Member -Type NoteProperty -Name BackupPath -Value ($FinalBackupPath | Sort-Object -Unique)
                            $HeaderInfo | Add-Member -Type NoteProperty -Name DatabaseName -Value $dbname
                            $HeaderInfo | Add-Member -Type NoteProperty -Name Notes -Value ($failures -join (','))
                            $HeaderInfo | Add-Member -Type NoteProperty -Name Script -Value $script
                            $HeaderInfo | Add-Member -Type NoteProperty -Name Verified -Value $Verified
                        } else {
                            $backup.Script($server)
                        }
                    }
                } catch {
                    if ($NoRecovery -and ($_.Exception.InnerException.InnerException.InnerException -like '*cannot be opened. It is in the middle of a restore.')) {
                        Write-Message -Message "Exception thrown by db going into restoring mode due to recovery" -Leve Verbose
                    } else {
                        Write-Progress -id $ProgressId -activity "Backup" -status "Failed" -completed
                        Stop-Function -message "Backup Failed" -ErrorRecord $_ -Continue
                        $BackupComplete = $false
                    }
                }
            }
            $OutputExclude = 'FullName', 'FileList', 'SoftwareVersionMajor'
            if ($failures.Count -eq 0) {
                $OutputExclude += ('Notes', 'FirstLsn', 'DatabaseBackupLsn', 'CheckpointLsn', 'LastLsn', 'BackupSetId', 'LastRecoveryForkGuid')
            }
            $headerinfo | Select-DefaultView -ExcludeProperty $OutputExclude
            $BackupFileName = $null
        }
    }
}
#ValidationTags#Messaging,FlowControl,Pipeline,CodeStyle#
function Backup-DbaDbCertificate {
    <#
    .SYNOPSIS
        Exports database certificates from SQL Server using SMO.

    .DESCRIPTION
        Exports database certificates from SQL Server using SMO and outputs the .cer and .pvk files.

    .PARAMETER SqlInstance
        The target SQL Server instance or instances. This can be a collection and receive pipeline input to allow the function to be executed against multiple SQL Server instances.

    .PARAMETER SqlCredential
        Login to the target instance using alternative credentials. Windows and SQL Authentication supported. Accepts credential objects (Get-Credential)

    .PARAMETER Certificate
        Exports certificate that matches the name(s).

    .PARAMETER Database
        Exports the encryptor for specific database(s).

    .PARAMETER ExcludeDatabase
        Database(s) to skip when exporting encryptors.

    .PARAMETER EncryptionPassword
        A string value that specifies the system path to encrypt the private key.

    .PARAMETER DecryptionPassword
        A string value that specifies the system path to decrypt the private key.

    .PARAMETER Path
        The path to output the files to. The path is relative to the SQL Server itself. If no path is specified, the default data directory will be used.

    .PARAMETER Suffix
        The suffix of the filename of the exported certificate.

    .PARAMETER InputObject
        Enables piping from Get-DbaDbCertificate

    .PARAMETER Confirm
        If this switch is enabled, you will be prompted for confirmation before executing any operations that change state.

    .PARAMETER EnableException
        By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
        This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
        Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

    .PARAMETER WhatIf
        If this switch is enabled, no actions are performed but informational messages will be displayed that explain what would happen if the command were to run.

    .NOTES
        Tags: Migration, Certificate
        Author: Jess Pomfret (@jpomfret)

        Website: https://dbatools.io
        Copyright: (c) 2018 by dbatools, licensed under MIT
        License: MIT https://opensource.org/licenses/MIT

    .EXAMPLE
        PS C:\> Backup-DbaDbCertificate -SqlInstance Server1

        Exports all the certificates on the specified SQL Server to the default data path for the instance.

    .EXAMPLE
        PS C:\> $cred = Get-Credential sqladmin
        PS C:\> Backup-DbaDbCertificate -SqlInstance Server1 -SqlCredential $cred

        Connects using sqladmin credential and exports all the certificates on the specified SQL Server to the default data path for the instance.

    .EXAMPLE
        PS C:\> Backup-DbaDbCertificate -SqlInstance Server1 -Certificate Certificate1

        Exports only the certificate named Certificate1 on the specified SQL Server to the default data path for the instance.

    .EXAMPLE
        PS C:\> Backup-DbaDbCertificate -SqlInstance Server1 -Database AdventureWorks

        Exports only the certificates for AdventureWorks on the specified SQL Server to the default data path for the instance.

    .EXAMPLE
        PS C:\> Backup-DbaDbCertificate -SqlInstance Server1 -ExcludeDatabase AdventureWorks

        Exports all certificates except those for AdventureWorks on the specified SQL Server to the default data path for the instance.

    .EXAMPLE
        PS C:\> Backup-DbaDbCertificate -SqlInstance Server1 -Path \\Server1\Certificates -EncryptionPassword (ConvertTo-SecureString -force -AsPlainText GoodPass1234!!)

        Exports all the certificates and private keys on the specified SQL Server.

    .EXAMPLE
        PS C:\> $EncryptionPassword = ConvertTo-SecureString -AsPlainText "GoodPass1234!!" -force
        PS C:\> $DecryptionPassword = ConvertTo-SecureString -AsPlainText "Password4567!!" -force
        PS C:\> Backup-DbaDbCertificate -SqlInstance Server1 -EncryptionPassword $EncryptionPassword -DecryptionPassword $DecryptionPassword

        Exports all the certificates on the specified SQL Server using the supplied DecryptionPassword, since an EncryptionPassword is specified private keys are also exported.

    .EXAMPLE
        PS C:\> Backup-DbaDbCertificate -SqlInstance Server1 -Path \\Server1\Certificates

        Exports all certificates on the specified SQL Server to the specified path.

    .EXAMPLE
        PS C:\> Backup-DbaDbCertificate -SqlInstance Server1 -Suffix DbaTools

        Exports all certificates on the specified SQL Server to the specified path, appends DbaTools to the end of the filenames.

    .EXAMPLE
        PS C:\> Get-DbaDbCertificate -SqlInstance sql2016 | Backup-DbaDbCertificate

        Exports all certificates found on sql2016 to the default data directory.

    #>
    [CmdletBinding(DefaultParameterSetName = "Default", SupportsShouldProcess, ConfirmImpact = 'Low')]
    param (
        [parameter(Mandatory, ParameterSetName = "instance")]
        [Alias("ServerInstance", "SqlServer")]
        [DbaInstanceParameter[]]$SqlInstance,
        [PSCredential]$SqlCredential,
        [parameter(ParameterSetName = "instance")]
        [object[]]$Certificate,
        [parameter(ParameterSetName = "instance")]
        [object[]]$Database,
        [parameter(ParameterSetName = "instance")]
        [object[]]$ExcludeDatabase,
        [Security.SecureString]$EncryptionPassword,
        [Security.SecureString]$DecryptionPassword,
        [System.IO.FileInfo]$Path,
        [string]$Suffix = "$(Get-Date -format 'yyyyMMddHHmmssms')",
        [parameter(ValueFromPipeline, ParameterSetName = "collection")]
        [Microsoft.SqlServer.Management.Smo.Certificate[]]$InputObject,
        [Alias('Silent')]
        [switch]$EnableException
    )

    begin {
        if (-not $EncryptionPassword -and $DecryptionPassword) {
            Stop-Function -Message "If you specify a decryption password, you must also specify an encryption password" -Target $DecryptionPassword
        }

        Test-DbaDeprecation -DeprecatedOn "1.0.0" -Alias Backup-DbaDatabaseCertificate

        function export-cert ($cert) {
            $certName = $cert.Name
            $db = $cert.Parent
            $server = $db.Parent
            $instance = $server.Name
            $actualPath = $Path

            if ($null -eq $actualPath) {
                $actualPath = Get-SqlDefaultPaths -SqlInstance $server -filetype Data
            }

            $actualPath = "$actualPath".TrimEnd('\')
            $fullCertName = "$actualPath\$certName$Suffix"
            $exportPathKey = "$fullCertName.pvk"

            if (!(Test-DbaPath -SqlInstance $server -Path $actualPath)) {
                Stop-Function -Message "$SqlInstance cannot access $actualPath" -Target $actualPath
            }

            if ($Pscmdlet.ShouldProcess($instance, "Exporting certificate $certName from $db on $instance to $actualPath")) {
                Write-Message -Level Verbose -Message "Exporting Certificate: $certName to $fullCertName"
                try {

                    $exportPathCert = "$fullCertName.cer"

                    # because the password shouldn't go to memory...
                    if ($EncryptionPassword.Length -gt 0 -and $DecryptionPassword.Length -gt 0) {

                        Write-Message -Level Verbose -Message "Both passwords passed in. Will export both cer and pvk."

                        $cert.export(
                            $exportPathCert,
                            $exportPathKey,
                            [System.Runtime.InteropServices.marshal]::PtrToStringAuto([System.Runtime.InteropServices.marshal]::SecureStringToBSTR($EncryptionPassword)),
                            [System.Runtime.InteropServices.marshal]::PtrToStringAuto([System.Runtime.InteropServices.marshal]::SecureStringToBSTR($DecryptionPassword))
                        )
                    } elseif ($EncryptionPassword.Length -gt 0 -and $DecryptionPassword.Length -eq 0) {
                        Write-Message -Level Verbose -Message "Only encryption password passed in. Will export both cer and pvk."

                        $cert.export(
                            $exportPathCert,
                            $exportPathKey,
                            [System.Runtime.InteropServices.marshal]::PtrToStringAuto([System.Runtime.InteropServices.marshal]::SecureStringToBSTR($EncryptionPassword))
                        )
                    } else {
                        Write-Message -Level Verbose -Message "No passwords passed in. Will export just cer."
                        $exportPathKey = "Password required t