internal/New-DbaLogShippingPrimaryDatabase.ps1

function New-DbaLogShippingPrimaryDatabase {
    <#
.SYNOPSIS
New-DbaLogShippingPrimaryDatabase add the primary database to log shipping
 
.DESCRIPTION
New-DbaLogShippingPrimaryDatabase will add the primary database to log shipping.
This is executed on the primary server.
 
.PARAMETER SqlInstance
SQL Server instance. You must have sysadmin access and server version must be SQL Server version 2000 or greater.
 
.PARAMETER SqlCredential
Allows you to login to servers using SQL Logins as opposed to Windows Auth/Integrated/Trusted. To use:
$scred = Get-Credential, then pass $scred object to the -SqlCredential parameter.
To connect as a different Windows user, run PowerShell as that user.
 
.PARAMETER Database
Database to set up log shipping for.
 
.PARAMETER BackupDirectory
Is the path to the backup folder on the primary server.
 
.PARAMETER BackupJob
Is the name of the SQL Server Agent job on the primary server that copies the backup into the backup folder.
 
.PARAMETER BackupJobID
The SQL Server Agent job ID associated with the backup job on the primary server.
 
.PARAMETER BackupRetention
Is the length of time, in minutes, to retain the log backup file in the backup directory on the primary server.
 
.PARAMETER BackupShare
Is the network path to the backup directory on the primary server.
 
.PARAMETER BackupThreshold
Is the length of time, in minutes, after the last backup before a threshold_alert error is raised.
The default is 60.
 
.PARAMETER CompressBackup
Enables the use of backup compression
 
.PARAMETER ThressAlert
Is the length of time, in minutes, when the alert is to be raised when the backup threshold is exceeded.
The default is 14,420.
 
.PARAMETER HistoryRetention
Is the length of time in minutes in which the history will be retained.
The default is 14420.
 
.PARAMETER MonitorServer
Is the name of the monitor server.
The default is the name of the primary server.
 
.PARAMETER MonitorCredential
Allows you to login to enter a secure credential.
This is only needed in combination with MonitorServerSecurityMode having either a 0 or 'sqlserver' value.
To use: $scred = Get-Credential, then pass $scred object to the -MonitorCredential parameter.
 
.PARAMETER MonitorServerSecurityMode
The security mode used to connect to the monitor server. Allowed values are 0, "sqlserver", 1, "windows"
The default is 1 or Windows.
 
.PARAMETER ThresholdAlertEnabled
Specifies whether an alert will be raised when backup threshold is exceeded.
The default is 0.
 
.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 Silent
Use this switch to disable any kind of verbose messages
 
.PARAMETER Force
The force parameter will ignore some errors in the parameters and assume defaults.
It will also remove the any present schedules with the same name for the specific job.
 
.NOTES
Author: Sander Stad (@sqlstad, sqlstad.nl)
Tags: Log shippin, primary database
     
Website: https://dbatools.io
Copyright: (C) Chrissy LeMaire, clemaire@gmail.com
License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0
 
.LINK
https://dbatools.io/New-DbaLogShippingPrimaryDatabase
 
.EXAMPLE
New-DbaLogShippingPrimaryDatabase -SqlInstance sql1 -Database DB1 -BackupDirectory D:\data\logshipping -BackupJob LSBackup_DB1 -BackupRetention 4320 -BackupShare "\\sql1\logshipping" -BackupThreshold 60 -CompressBackup -HistoryRetention 14420 -MonitorServer sql1 -ThresholdAlertEnabled
 
#>


    [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = "Low")]

    param (
        [parameter(Mandatory = $true)]
        [Alias("ServerInstance", "SqlServer")]
        [object]$SqlInstance,

        [System.Management.Automation.PSCredential]
        $SqlCredential,

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [object]$Database,

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [string]$BackupDirectory,

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [string]$BackupJob,

        [Parameter(Mandatory = $true)]
        [int]$BackupRetention,

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [string]$BackupShare,

        [int]$BackupThreshold = 60,

        [switch]$CompressBackup,

        [int]$ThressAlert = 14420,

        [int]$HistoryRetention = 14420,

        [string]$MonitorServer,

        [ValidateSet(0, "sqlserver", 1, "windows")]
        [object]$MonitorServerSecurityMode = 1,

        [System.Management.Automation.PSCredential]
        $MonitorCredential,

        [switch]$ThresholdAlertEnabled,

        [switch]$Silent,

        [switch]$Force
    )

    # Try connecting to the instance
    Write-Message -Message "Attempting to connect to $SqlInstance" -Level Verbose
    try {
        $server = Connect-SqlInstance -SqlInstance $SqlInstance -SqlCredential $SqlCredential
    }
    catch {
        Stop-Function -Message "Could not connect to Sql Server instance" -Target $SqlInstance -Continue
    }

    # Check if the backup UNC path is correct and reachable
    if ([bool]([uri]$BackupShare).IsUnc -and $BackupShare -notmatch '^\\(?:\\[^<>:`"/\\|?*]+)+$') {
        Stop-Function -Message "The backup share path $BackupShare should be formatted in the form \\server\share." -Target $SqlInstance
        return
    }
    else {
        if (-not ((Test-Path $BackupShare -PathType Container -IsValid) -and ((Get-Item $BackupShare).PSProvider.Name -eq 'FileSystem'))) {
            Stop-Function -Message "The backup share path $BackupShare is not valid or can't be reached." -Target $SqlInstance
            return
        }
    }

    # Check the backup compression
    if ($CompressBackup -eq $true) {
        Write-Message -Message "Setting backup compression to 1." -Level Verbose
        $BackupCompression = 1
    }
    elseif ($CompressBackup -eq $false) {
        Write-Message -Message "Setting backup compression to 0." -Level Verbose
        $BackupCompression = 0
    }
    elseif (-not $CompressBackup) {
        $defaultCompression = (Get-DbaSpConfigure -SqlInstance $SqlInstance -ConfigName DefaultBackupCompression).ConfiguredValue
        Write-Message -Message "Setting backup compression to default value $defaultCompression." -Level Verbose
        $BackupCompression = $defaultCompression
        
    }

    # Check of the MonitorServerSecurityMode value is of type string and set the integer value
    if ($MonitorServerSecurityMode -notin 0, 1) {
        $MonitorServerSecurityMode = switch ($MonitorServerSecurityMode) {"WINDOWS" { 1 } "SQLSERVER" { 0 } }
        Write-Message -Message "Setting monitor server security mode to $MonitorServerSecurityMode." -Level Verbose
    }

    # Check the MonitorServer
    if ($Force -and -not $MonitorServer) {
        $MonitorServer = $SqlInstance
        Write-Message -Message "Setting monitor server to $MonitorServer." -Level Verbose
    }

    # Check the MonitorServerSecurityMode if it's SQL Server authentication
    if ($MonitorServerSecurityMode -eq 0 -and -not $MonitorCredential) {
        Stop-Function -Message "The MonitorServerCredential cannot be empty when using SQL Server authentication." -Target $SqlInstance 
        return
    }
    elseif ($MonitorServerSecurityMode -eq 0 -and $MonitorCredential) {
        # Get the username and password from the credential
        $MonitorLogin = $MonitorCredential.UserName
        $MonitorPassword = $MonitorCredential.GetNetworkCredential().Password

        # Check if the user is in the database
        if ($server.Databases['master'].Users.Name -notcontains $MonitorLogin) {
            Stop-Function -Message "User $MonitorLogin for monitor login must be in the master database." -Target $SqlInstance 
            return
        }
    }

    # Check if the database is present on the source sql server
    if ($server.Databases.Name -notcontains $Database) {
        Stop-Function -Message "Database $Database is not available on instance $SqlInstance" -Target $SqlInstance 
        return
    }

    # Check the if Threshold alert needs to be enabled
    if ($ThresholdAlertEnabled) {
        [int]$ThresholdAlertEnabled = 1
        Write-Message -Message "Setting Threshold alert to $ThresholdAlertEnabled." -Level Verbose
    }
    else {
        [int]$ThresholdAlertEnabled = 0
        Write-Message -Message "Setting Threshold alert to $ThresholdAlertEnabled." -Level Verbose
    }

    # Set the log shipping primary
    $Query = "
        DECLARE @LS_BackupJobId AS uniqueidentifier;
        DECLARE @LS_PrimaryId AS uniqueidentifier;
        EXEC master.sys.sp_add_log_shipping_primary_database
            @database = N'$Database'
            ,@backup_directory = N'$BackupDirectory'
            ,@backup_share = N'$BackupShare'
            ,@backup_job_name = N'$BackupJob'
            ,@backup_retention_period = $BackupRetention"

            
    if ($SqlInstance.Version.Major -gt 9) {
        $Query += ",@backup_compression = $BackupCompression"
    }
    
    if ($MonitorServer) {
        $Query += ",@monitor_server = N'$MonitorServer'
            ,@monitor_server_security_mode = $MonitorServerSecurityMode
            ,@threshold_alert = $ThressAlert
            ,@threshold_alert_enabled = $ThresholdAlertEnabled"

    }

    $Query += ",@backup_threshold = $BackupThreshold
            ,@history_retention_period = $HistoryRetention
            ,@backup_job_id = @LS_BackupJobId OUTPUT
            ,@primary_id = @LS_PrimaryId OUTPUT "


    # Check the MonitorServerSecurityMode if it's SQL Server authentication
    if ($MonitorServer -and $MonitorServerSecurityMode -eq 0 ) {
        $Query += ",@monitor_server_login = N'$MonitorLogin'
            ,@monitor_server_password = N'$MonitorPassword' "

    }

    if ($server.Version.Major -gt 9) {
        $Query += ",@overwrite = 1;"
    }
    else {
        $Query += ";"
    }

    # Execute the query to add the log shipping primary
    if ($PSCmdlet.ShouldProcess($SqlServer, ("Configuring logshipping for primary database $Database on $SqlInstance"))) {
        try {
            Write-Message -Message "Configuring logshipping for primary database $Database." -Level Output
            Write-Message -Message "Executing query:`n$Query" -Level Verbose
            $server.Query($Query)
        }
        catch {
            Write-Message -Message "$($_.Exception.InnerException.InnerException.InnerException.InnerException.Message)" -Level Warning
            Stop-Function -Message "Error executing the query.`n$($_.Exception.Message)`n$($Query)" -ErrorRecord $_ -Target $SqlInstance -Continue
        }
    }

    Write-Message -Message "Finished adding the primary database $Database to log shipping." -Level Output
    
}