dbaship

1.10.110

dbaship.psm1

<#
.SYNOPSIS
    Provides scripts to build a basic logshipping process
 
    get-help about_dbaship
 
.PARAMETER version
    0.0.8
 
.DESCRIPTION
 
 #>


# =======================================================================================
# INTERNAL FUNCTIONS AND PREP
# =======================================================================================

function get-shipHelp{
    get-help about_dbaship
}

function out-shipMsg{
<#
.SYNOPSIS
For internal use of dbaShip module only
#>
    param(
        [string]$param,
        [string]$value,
        [switch]$flush
    )
    if( !$script:msg ){
        $script:msg = @()
    }
    if( $param ){
        $script:msg += [pscustomobject]@{
            param = $param #
            value = $value # (
        }
    }
    if( $flush.IsPresent ){
        $msg | format-table -AutoSize
        if( $script:msg ){
            $script:msg = @()
        }
    }
    #log
    if( $script:ship_log_folder ){
        if( test-path $script:ship_log_folder -PathType Container ){
            (  ($param+(" "*50)).Substring(0,35)+" "+($value+(" "*100)).Substring(0,100) ) | out-file $ship_log -Append
        }
    }
}

function join-AnyPath {
  ($args -join '\') -replace '(?!^)[\\/]+', [IO.Path]::DirectorySeparatorChar
}

function get-shipValue{
<#
.SYNOPSIS
For internal use of dbaShip module only
#>
    param(
        [System.Object]$res,
        [string]$key,
        [switch]$echo
    )
    $ret = $null
    $found = ""
    foreach( $row in $res ){
        if( $row.param -eq $key ){
            $ret = $row.value
        }
        if( $echo.IsPresent ){
            if( $ret ){  #-and $found -eq ""
                $found = "*"
            }
            write-output  "$( ($row.param+(" "*50)).Substring(0,35) )$($row.value)$($found)"
            $found = " "
        }
    }
    return $ret
}

function set-shipParam{
[System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")]
[CmdletBinding(SupportsShouldProcess)]
    param(
        [string]$primary_server,
        [Parameter(Mandatory)]
        [string]$primary_database,
        [Parameter(Mandatory)]
        [string]$primary_root,
        [Parameter(Mandatory)]
        [string]$secondary_server,
        # defaults to primary_database
        [string]$secondary_database,
        [Parameter(Mandatory)]
        [string]$secondary_root,
        #
        [Parameter(Mandatory)]
        [string]$data_root,
        # defaults to $data_root
        [string]$log_root,
        #
        [string[]]$databases,
        #
        [string]$monitor_server,
        [switch]$clean
    )

    # clean
    if( $clean.IsPresent ){
        remove-Item env:ship_*
    }


    # default
    if( [string]::IsNullOrEmpty($secondary_database) ){
        $secondary_database = $primary_database
    }
    if( [string]::IsNullOrEmpty($log_root) ){
        $log_root = $data_root
    }


    # store in environment
    $env:ship_primary_server        = $primary_server
    $env:ship_primary_database        = $primary_database
    $env:ship_primary_root            = $primary_root
    $env:ship_secondary_server        = $secondary_server
    $env:ship_secondary_database    = $secondary_database
    $env:ship_secondary_root        = $secondary_root
    $env:ship_data_root                = $data_root
    $env:ship_log_root                = $log_root
    $env:ship_databases                = $databases # it will be stored as space separated strings
    $env:ship_monitor_server        = $monitor_server

    if( $PSCmdlet.ShouldProcess($query, 'test') ){
        test-shipParam
    }
}

function get-shipParam{
[System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")]
# For internal use of dbaShip module only
param(
    [switch]$nosql,
    [switch]$nomsg
)
    # get from environment
    $script:primary_server        = $env:ship_primary_server
    $script:primary_database    = $env:ship_primary_database
    $script:primary_root        = $env:ship_primary_root
    $script:secondary_server    = $env:ship_secondary_server
    $script:secondary_database    = $env:ship_secondary_database
    $script:secondary_root        = $env:ship_secondary_root
    $script:data_root            = $env:ship_data_root
    $script:log_root            = $env:ship_log_root
    $script:databases            = $env:ship_databases ? $env:ship_databases.split(' ') : $null
    $script:monitor_server      = $env:ship_monitor_server

    $script:primary_server     = $primary_server.ToUpper()
    $script:secondary_server   = $secondary_server.ToUpper()

    $script:hoy                = get-date -f "yyyyMMdd"
    $script:ts                 = get-date -f "yyyyMMddHHmm"
    $script:p_id               = [System.Diagnostics.Process]::GetCurrentProcess().ID

    # log
    if( !$nomsg.IsPresent ){
        out-shipMsg 'command' ( Get-PSCallStack )[1].Command
        out-shipMsg 'ts' $ts
        if( $credential -and $credential.UserName ){
            out-shipMsg 'UserName' $credential.UserName
        }else{
            throw '-credential not found'
        }
        out-shipMsg 'primary_database' $script:primary_database
    }

    # verify
    if( [string]::IsNullOrEmpty($script:primary_root) ){
        throw '-primary_root not found'
    }

    if( [string]::IsNullOrEmpty($script:secondary_root) ){
        throw '-secondary_root not found'
    }

    # default
    if ( [string]::IsNullOrEmpty($script:secondary_database) ){
        throw '-secondary_database not found'
    }

    $script:primary_host                = $script:primary_server.contains('\')   ? $script:primary_server.split('\')[0]   : $script:primary_server
    $script:secondary_host              = $script:secondary_server.contains('\') ? $script:secondary_server.split('\')[0] : $script:secondary_server
    $script:primary_root_folder         = $script:primary_server.contains('\')   ? ( join-anypath $script:primary_root $script:primary_server.split('\')[1] ) : $script:primary_root
    $script:secondary_root_folder       = $script:secondary_server.contains('\') ? ( join-anypath $script:secondary_root $script:secondary_server.split('\')[1] ) : $script:secondary_root

    $script:share_name                  = Split-Path $script:primary_root -Leaf
    $script:ship_share                  = join-anypath '\' $script:primary_host $script:share_name
    $script:share_folder                = $script:primary_server.contains('\')   ? ( join-anypath $ship_share $script:primary_server.split('\')[1] ) : $script:ship_share

    $script:backup_folder               = join-anypath $script:primary_root_folder $script:primary_database
    $script:backup_share_folder         = join-anypath $script:share_folder $script:primary_database
    $script:ship_destination_directory  = join-anypath $script:secondary_root_folder $script:primary_database
    $script:UserName                    = $credential.UserName

    $script:backup_job_name                = 'LSBackup_'+$script:primary_database
    $script:backup_schedule_name        = 'LSBackupSchedule_'+$script:primary_database
    $script:job_name                    = $script:primary_server.ToUpper()+'_'+$script:primary_database
    $script:copy_job_name                = 'LSCopy_'+$script:job_name
    $script:copy_schedule_name            = 'LSCopySchedule_'+$script:job_name
    $script:restore_job_name            = 'LSRestore_'+$script:job_name
    $script:restore_schedule_name        = 'LSRestoreSchedule_'+$script:job_name

    $script:primary_backup_name         = $script:primary_database+'_'+$ts+'.bak'
    $script:primary_backup              = join-anypath $script:primary_root $script:primary_database $script:primary_backup_name
    $script:server_root                 = $env:computername -eq $script:primary_host ? $script:primary_root : $env:computername -eq $script:secondary_host ? $script:secondary_root : $null
    $script:ship_log_folder             = ( $null -eq $script:server_root ) ? $pwd.path : ( join-anypath ($script:server_root ) "dbaship" )
    $script:ship_log                    = join-anypath $script:ship_log_folder "dbaship_$($hoy)_$($script:p_id).txt"

    $script:standby_folder              = join-anypath $script:secondary_root "standby"
    $script:data_folder                 = join-anypath $script:data_root 'DATA'
    $script:log_folder                  = join-anypath $script:log_root 'Log'

    $script:login_file                  = join-anypath $script:ship_log_folder "login_$($script:primary_server)_$($script:primary_database)_$($ts)_$($script:p_id).sql"

    if( $nosql.IsPresent ){
        return;
    }

    # load sqlserver module
    if( !(get-module sqlserver) ){
        import-module sqlserver
    }


    $sqlparams = @"
    -- $ts
    if db_name()<>'master'
        use master;
    set nocount on;
    select [param]='@ts', value=convert(varchar(500),'$ts' );
 
"@

    return $sqlparams

}

function show-shipParam{

    get-shipParam -nosql

    write-host "`$UserName $UserName "
    write-host "`$primary_server $primary_server "
    write-host "`$primary_database $primary_database "
    write-host "`$primary_root $primary_root "
    write-host "`$secondary_server $secondary_server "
    write-host "`$secondary_database $secondary_database "
    write-host "`$secondary_root $secondary_root "
    write-host "`$data_root $data_root "
    write-host "`$log_root $log_root "
    write-host "`$monitor_server $monitor_server "

    write-host " "

    write-host "`$hoy $hoy "
    write-host "`$ts $ts "
    write-host "`$p_id $p_id "

    write-host "`$primary_host $primary_host "
    write-host "`$secondary_host $secondary_host "

    write-host "`$share_name $share_name "
    write-host "`$backup_folder $backup_folder "
    write-host "`$backup_share_folder $backup_share_folder "
    write-host "`$ship_share $ship_share "
    write-host "`$ship_destination_directory $ship_destination_directory "

    write-host "`$backup_job_name $backup_job_name "
    write-host "`$copy_job_name $copy_job_name "
    write-host "`$backup_schedule_name $backup_schedule_name "
    write-host "`$copy_schedule_name $copy_schedule_name "
    write-host "`$restore_job_name $restore_job_name "
    write-host "`$restore_schedule_name $restore_schedule_name "

    write-host "`$primary_backup_name $primary_backup_name "
    write-host "`$primary_backup $primary_backup "
    write-host "`$server_root $server_root "
    write-host "`$ship_log_folder $ship_log_folder "
    write-host "`$ship_log $ship_log "
    write-host "`$standby_folder $standby_folder "

    write-host "`$data_folder $data_folder "
    write-host "`$log_folder $log_folder "

    write-host "`$login_file $login_file "

    out-shipMsg -flush
}

function test-shipParam{
[System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")]
    param(
        [switch]$show
    )
    get-shipParam -nosql

    # check if current server is primary/secondary/neither
    out-shipMsg 'servername' $env:computername
    if( $env:computername -eq $primary_host ){
        out-shipMsg "server" "primary_server"
    }elseif( $env:computername -eq $secondary_host ){
        out-shipMsg "server" "secondary_server"
    }else{
        out-shipMsg "server" "This server is neither primary or secondary..."
        out-shipMsg "" "some commands can not be run from this copmuter"
        out-shipMsg "" "folders can not be checked from this computer"
    }

    # check/import sql module
    $sqlserver = ( get-module sqlserver )
    if( $sqlserver ){
        out-shipMsg 'sqlserver' 'module imported'
    }else{
        out-shipMsg 'sqlserver' 'module not found...'
    }

    # check powershell version
    $version = $psversiontable.psversion.tostring()
    out-shipMsg "PSVersion" $version
    if( $psversiontable.psversion.major -lt 7 ){
        out-shipMsg "Powershell" "Upgrade to 7+"
    }

    # check access to share
    if( (test-path -path $ship_share ) ){
        out-shipMsg $ship_share 'Accessible'
    }else{
        out-shipMsg $ship_share 'Is not accessible...'
    }

    # verify primary databases exists
    #if( $databases.count -eq 0 ){
    # $databases += $primary_database
    #}
    if( $databases ){
        out-shipMsg "databases" $databases.count
    }
    $querydb = "select [param] = [name], [value] = database_id from sys.databases"
    $resdb = invoke-sqlcmd `
        -ServerInstance $primary_server `
        -Database master `
        -Credential $credential `
        -TrustServerCertificate `
        -Query $querydb `
        -Verbose
    #foreach( $db in $databases ){
        $dbexists = get-shipValue $resdb $primary_database
        if( $dbexists ){
            out-shipMsg "db:$primary_database" "OK"
        }else{
            out-shipMsg "db:$primary_database" "not found in $primary_server..."
        }
    #}

    # check drives in primary server
    if( $env:computername -eq $primary_host ){
        out-shipMsg 'primary_root' $primary_root
        if ( !(test-path $primary_root -PathType Container) ){
            out-shipMsg $primary_root 'not found...'
        }

        # check share
        $shareExists = Get-SmbShare | Where-Object { $_.Path -eq $primary_root }
        out-shipMsg 'share' $ship_share
        if( $shareExists ){
            out-shipMsg $ship_share 'Shared'
        }else{
            out-shipMsg $ship_share 'Not found...'
        }
    }

    # check drives in secondary server
    if( $env:computername -eq $secondary_host ){
        out-shipMsg 'secondary_root' $secondary_root
        if ( !(test-path $secondary_root -PathType Container) ){
            out-shipMsg 'secondary_root' 'not found...'
        }

        # check data and logs root
        out-shipMsg 'data_root' $data_root
        if ( !(test-path $data_root -PathType Container) ){
            out-shipMsg 'data_root' 'not found...'
        }
        out-shipMsg 'log_root' $log_root
        if ( !(test-path $log_root -PathType Container) ){
            out-shipMsg 'log_root' 'not found...'
        }
    }

    # sysadmin role
    out-shipMsg 'UserName' $credential.UserName
    $query = @"
        SELECT
            [param] = princ.name collate Latin1_General_CI_AS,
            [value] = princ.type
        FROM sys.server_principals princ
        INNER JOIN sys.syslogins logins
            ON princ.sid = logins.sid
        WHERE logins.sysadmin = 1
        union
        select
            UserName = princ.name collate Latin1_General_CI_AS,
            UserType = princ.type
        from sys.database_principals princ
        INNER JOIN sys.syslogins logins
                ON princ.sid = logins.sid
        WHERE logins.sysadmin = 1;
"@

    # verify sysadmin role in primary server
    $res1 = invoke-sqlcmd `
        -ServerInstance $primary_server `
        -Database master `
        -Credential $credential `
        -TrustServerCertificate `
        -Query $query `
        -Verbose
    $admin1 = get-shipValue $res1 $credential.UserName #-echo
    if( $admin1 ){
        out-shipMsg 'sysadmin' 'OK Primary'
    }else{
        out-shipMsg $credential.UserName "Is not sysadmin in $primary_server..."
    }

    # verify sysadmin role in secondary server
    $res2 = invoke-sqlcmd `
        -ServerInstance $secondary_server `
        -Database master `
        -Credential $credential `
        -TrustServerCertificate `
        -Query $query `
        -Verbose
    $admin2 = get-shipValue $res2 $credential.UserName #-echo
    if( $admin2 ){
        out-shipMsg 'sysadmin' 'OK Secondary'
    }else{
        out-shipMsg $credential.UserName 'Is not sysadmin...'
    }


    out-shipMsg "dbaship_log" $ship_log
    if( !(test-path $ship_log_folder -PathType Container) ){
        out-shipMsg "dbaship_log_folder" "not found..."
    }

    # test if powershell session is "run as admin"
    if( ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator) ){
        out-shipMsg 'session' 'Administrator'
    }else{
        out-shipMsg 'session' 'Warning: some commands may require to Run as Administrator !!!'
    }

    out-shipMsg -flush

    # show list of calculated parameters from basic ones
    if( $show.IsPresent ){
        show-shipParam
    }

}

# =======================================================================================
# IN PRIMARY SERVER
# =======================================================================================

function get-shipConfig{
    get-shipParam -nosql

    $query1 = @"
        select
            @@servername [primary_server], primary_database, secondary_server, secondary_database,
            case when user_specified_monitor = 1 then monitor_server else null end [monitor_server],
            last_backup_date, backup_directory
        from msdb.dbo.log_shipping_primary_databases pd
        left join msdb.dbo.log_shipping_primary_secondaries ps
            on ps.primary_id = pd.primary_id;
"@

    $res1 = invoke-sqlcmd `
        -ServerInstance $primary_server `
        -Database master `
        -Credential $credential `
        -TrustServerCertificate `
        -Query $query1 `
        -Verbose
    $res1 |format-table -autosize



    $query2 = @"
        select
            primary_server, primary_database, @@servername [secondary_server], secondary_database,
            case when user_specified_monitor = 1 then monitor_server else null end [monitor_server],
            last_copied_date, last_restored_date, restore_mode
        from msdb.dbo.log_shipping_secondary lss
        join msdb.dbo.log_shipping_secondary_databases lssd
            on lssd.secondary_id = lss.secondary_id;
"@
    $res2 = invoke-sqlcmd `
        -ServerInstance $secondary_server `
        -Database master `
        -Credential $credential `
        -TrustServerCertificate `
        -Query $query2 `
        -Verbose
    $res2 |format-table -autosize

    out-shipMsg -flush
}

function add-shipFolder{
#[System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")]
    param(
        [switch]$just_primary
    )
    get-shipParam -nosql

    #if( !$PSBoundParameters.ContainsKey('databases') ){
    #if( !$databases -or $just_primary.IsPresent ){
    # $databases += $primary_database
    #}

    out-shipMsg 'servername' $env:computername

    if( !($env:computername -eq $primary_host) -and !( $env:computername -eq $secondary_host ) ){
        throw "This command can only be run in $primary_server or $secondary_server"
    }

    # primary
    if( $env:computername -eq $primary_host ){
        out-shipMsg "server" "primary_server"

        # check if root exist, creates it
        out-shipMsg 'primary_root' $primary_root
        if ( !(test-path $primary_root -PathType Container) ){
            new-item -Path $primary_root -ItemType Directory -Force | out-null
        }

        # create database folders for log shipping trn and bak
        #foreach( $database in $databases ){
            $path = join-path $primary_root $primary_database
            out-shipMsg ("db:"+$primary_database) $path
            new-item -Path $path -ItemType Directory -Force | out-null
        #}

        # log folder for dbaship
        out-shipMsg 'dbaship logs' $ship_log_folder
        if ( !(test-path $ship_log_folder -PathType Container) ){
            new-item -Path $ship_log_folder -ItemType Directory -Force | out-null
        }

        #check if share exiss
        out-shipMsg "ship_share" $ship_share
        if ( !(Get-SmbShare -Name $share_name -ErrorAction SilentlyContinue) ){
            out-shipMsg "warning" "share does not exists..."
        }

        out-shipMsg 'Ret' '0' -flush
    }


    # secondary
    if( $env:computername -eq $secondary_host ){
        out-shipMsg "server" "secondary_server"

        # check if root exist, creates it
        out-shipMsg 'secondary_root' $secondary_root
        if ( !(test-path $secondary_root -PathType Container) ){
            new-item -Path $secondary_root -ItemType Directory -Force | out-null
        }

        # create database folders for log shipping trn and bak
        #foreach( $database in $databases ){
            $path = join-path $secondary_root $primary_database
            out-shipMsg ("db:"+$primary_database) $path
            new-item -Path $path -ItemType Directory -Force | out-null
        #}

        # log folder for dbaship
        out-shipMsg 'dbaship logs' $ship_log_folder
        if ( !(test-path $ship_log_folder -PathType Container) ){
            new-item -Path $ship_log_folder -ItemType Directory -Force | out-null
        }

        # create database data and log folders in secondary
        out-shipMsg 'mdf' $data_folder
        new-item -Path $data_folder -ItemType Directory -Force | out-null

        out-shipMsg 'ldf' $log_folder
        new-item -Path $log_folder -ItemType Directory -Force | out-null

        # creates standby folder
        out-shipMsg 'standby' $standby_folder
        if ( !(test-path $standby_folder -PathType Container) ){
            new-item -Path $standby_folder -ItemType Directory -Force | out-null
        }

        out-shipMsg 'Ret' '0' -flush
    }

}

function set-shipPrimary{
[CmdletBinding(SupportsShouldProcess)]
    param(
        [switch]$run,
        [switch]$bypass,
        [switch]$full
    )

    $sqlparams = get-shipParam

    # check if this db has been backed up
    $query1 = @"
        select
            [param] = DB_NAME(database_id),
            [value] = count(*)
        FROM master.sys.databases d
        LEFT OUTER JOIN msdb.dbo.backupset b
            ON b.database_name = d.name
        where database_id = db_id('$primary_database')
            and b.backup_finish_date is not null
        group by DB_NAME(database_id);
 
"@
    $resbu = invoke-sqlcmd `
        -ServerInstance $primary_server `
        -Database master `
        -Credential $credential `
        -TrustServerCertificate `
        -Query $query1
    #$resbu
    $bu = get-ShipValue $resbu "$primary_database"
    out-shipMsg 'backups' $bu
    if( !($bu -gt "0") ){
        if( !$bypass.IsPresent ){
            out-shipMsg -flush
            throw "Need to take a FULL backup first! ($primary_database) "
        }
    }


    # check recovery model set to full if -full
    $alter = $full.IsPresent ? 1 : 0
    $queryrm = @"
        declare @recovery_model_desc nvarchar(60)
        SELECT top 1
            @recovery_model_desc = recovery_model_desc
        FROM sys.databases
        where [name] = '$primary_database';
 
        if( @recovery_model_desc='SIMPLE' and '$alter'='1' )
        begin
            alter database [$primary_database] set recovery full;
            select [param] = '$primary_database', [value] = 'FULL';
        end
        else
            select [param] = '$primary_database', [value] = @recovery_model_desc;
 
"@
    $resrm = invoke-sqlcmd `
        -ServerInstance $primary_server `
        -Database master `
        -Credential $credential `
        -TrustServerCertificate `
        -Query $queryrm `
        -Verbose
    $rm = get-ShipValue $resrm "$primary_database"
    if( $rm -eq 'SIMPLE' ){
        throw "Database $primary_database is in SINGLE recovery model"
    }else{
        out-shipMsg $primary_database 'FULL'
    }


    $query = @"
    $sqlparams
    -- Execute the following statements at the Primary to configure Log Shipping
    -- for the database [$primary_server].[$primary_database],
    -- The script needs to be run at the Primary in the context of the [msdb] database.
    -------------------------------------------------------------------------------------
 
    select [param]='@primary_server', value = convert(varchar(100), '$primary_server' )
    union all select [param]='@primary_database', value = convert(varchar(100), '$primary_database' )
 
    DECLARE @LS_BackupJobId AS uniqueidentifier
    DECLARE @LS_PrimaryId AS uniqueidentifier
    DECLARE @SP_Add_RetCode As int
 
    -- must run from master
    EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
         @database = N'$primary_database'
        ,@backup_directory = N'$backup_folder'
        ,@backup_share = N'$backup_share_folder'
        ,@backup_job_name = N'$backup_job_name'
        ,@backup_retention_period = 7200
        ,@backup_compression = 1
        ,@backup_threshold = 45
        ,@threshold_alert_enabled = 1
        ,@history_retention_period = 7200
        ,@backup_job_id = @LS_BackupJobId OUTPUT
        ,@primary_id = @LS_PrimaryId OUTPUT
        ,@overwrite = 1
 
    select [param]='@SP_Add_RetCode', value = convert(varchar(100), @SP_Add_RetCode)
 
    IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)
    BEGIN
        select [param]='@backup_job_name', value = convert(varchar(100), '$backup_job_name' )
 
    DECLARE @LS_BackUpScheduleUID As uniqueidentifier
    DECLARE @LS_BackUpScheduleID AS int
 
        EXEC msdb.dbo.sp_add_schedule
                 @schedule_name = N'$backup_schedule_name'
                ,@enabled = 1
                ,@freq_type = 4
                ,@freq_interval = 1
                ,@freq_subday_type = 4
                ,@freq_subday_interval = 15
                ,@freq_recurrence_factor = 0
                ,@active_start_date = $hoy
                ,@active_end_date = 99991231
                ,@active_start_time = 0
                ,@active_end_time = 235900
                ,@schedule_uid = @LS_BackUpScheduleUID OUTPUT
                ,@schedule_id = @LS_BackUpScheduleID OUTPUT
 
 
        EXEC msdb.dbo.sp_attach_schedule
                @job_id = @LS_BackupJobId
                ,@schedule_id = @LS_BackUpScheduleID
 
        EXEC msdb.dbo.sp_update_job
                @job_id = @LS_BackupJobId
                ,@enabled = 1
    END
 
    select [param]='@primary_AlertJob', value = convert(varchar(100), 'TBD')
    EXEC master.dbo.sp_add_log_shipping_alert_job
 
    select [param]='@secondary_server', value = convert(varchar(100), '$secondary_server' )
    union all select [param]='@secondary_database', value = convert(varchar(100), '$secondary_database' )
 
    -- must run from master
    EXEC master.dbo.sp_add_log_shipping_primary_secondary
         @primary_database = N'$primary_database'
        ,@secondary_server = N'$secondary_server'
        ,@secondary_database = N'$secondary_database'
        ,@overwrite = 1
 
    select [param]='Ret', value='0';
 
"@

    if( $script.IsPresent ){
        write-output $query
    }else{
        invoke-sqlcmd `
            -ServerInstance $primary_server `
            -Database master `
            -Credential $credential `
            -TrustServerCertificate `
            -Query $query `
            -Verbose
    }

    out-shipMsg "ret" "0" -flush

    if( $run.IsPresent ){
        $query3 = @"
            $sqlparams
 
            select [param]='backup_job_name', value = convert(varchar(100), '$backup_job_name' );
 
            EXEC msdb.dbo.sp_start_job @job_name = '$backup_job_name';
"@

        invoke-sqlcmd `
            -ServerInstance $primary_server `
            -Database master `
            -Credential $credential `
            -TrustServerCertificate `
            -Query $query3 `
            -Verbose

        out-shipMsg "ret" "0" -flush
    }

}

function backup-shipPrimary{
    param(
        [switch]$bypass,
        [switch]$wait
    )
    $sqlparams = get-shipParam

    $loop = $true
    while($loop){

        # verify the first logshipping has run
        $query1 = @"
            declare @last_backup_date datetime;
 
            select @last_backup_date = last_backup_date
            from msdb.dbo.log_shipping_primary_databases
            where primary_database = '$primary_database'
 
            if @last_backup_date is null
                select -1 found; -- not found
            else
                select 1 found; -- found
"@
        $res = invoke-sqlcmd `
            -ServerInstance $primary_server `
            -Database master `
            -Credential $credential `
            -TrustServerCertificate `
            -Query $query1 `
            -Verbose

        $res.found

        if( $wait.IsPresent ){
            if( $res.found -eq 1 ){
                $loop = $false
            }else{
                $ts2 = get-date -f "yyyyMMddHHmm"

                # wait for the backup job to finish ( the logshipping backup job not this backup )
                if( ($ts2 - $ts) -ge 2 ){
                    $loop = $false
                }else{
                    out-shipMsg "wait $ts2 " "for $backup_job_name "
                    Start-Sleep -Seconds 60
                }
            }

        }else{

            if( $res.found -eq -1 -and !$bypass.IsPresent ){
                throw "Job $backup_job_name has not run yet. Wait for Job to run or run Job manually."
            }else{
                $loop = $false
            }
        }
    }

    $query2 = @"
    $sqlparams
    BACKUP DATABASE [$primary_database]
        TO DISK = N'$primary_backup'
        WITH COPY_ONLY, FORMAT, INIT,
        NAME = N'$primary_database-Full Database Backup copy_only',
        SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 5
 
"@
    write-output $query2

    invoke-sqlcmd `
        -ServerInstance $primary_server `
        -Database master `
        -Credential $credential `
        -TrustServerCertificate `
        -Query $query2 `
        -Verbose

    out-shipMsg "backup" "primary_backup"
    out-shipMsg 'Ret' '0' -flush
}

# =======================================================================================
# IN SECONDARY SERVER
# =======================================================================================

function copy-shipBackup{
    param(
        [string]$source,
        [switch]$robocopy
    )
    get-shipParam -nosql

    if( $env:COMPUTERNAME.ToUpper() -ne $secondary_host.ToUpper() ){
        throw "This script must be run in [$secondary_server]"
    }

    $bak = (Get-ChildItem -Path $backup_share_folder -Filter "*.bak" -File | Sort-Object LastWriteTime -Descending | Select-Object -First 1 ).FullName

    # check if any file found
    if(!$bak){
        throw "no backup found for $primary_database in $backup_share_folder..."
    }

    out-shipMsg "copy" "$bak"
    # Enable-PSRemoting -Force

    if( $robocopy.IsPresent ){

        if( [string]::IsNullOrEmpty($source) ){
            $source = $backup_share_folder
        }

        $destin = $ship_destination_directory

        $cmd = {
            robocopy "$source" "$destin" "*.bak" /XO /ZB /R:3 /W:30 /MT:4 /TEE /LOG+:"$ship_log"
        }

        $res = Invoke-Command `
            -ScriptBlock $cmd `
            -ArgumentList $source,$destin,$ship_log

        $res
        if( ( $res | out-string ).contains('ERROR') ){
            out-shipMsg "ret" "1" -flush
            throw "Error copying backup from $source to $destin"
        }



    }else{

        copy-item -path $bak -Destination $ship_destination_directory -force -ErrorAction Stop

        # check if copied
        if( !( test-path $bak -PathType Leaf) ){
            throw 'Error: unable to copy $bak'
        }
    }
    out-shipMsg "paste" $ship_destination_directory
    out-shipMsg "ret" "0" -flush
}

function restore-shipSecondary{
    param(
        [switch]$bypass,
        [switch]$local,
        [switch]$script
    )
    get-shipParam -nosql > null

    if( !$bypass.IsPresent -and ( $env:COMPUTERNAME.ToUpper() -ne $secondary_host.ToUpper() ) ){
        throw "This script must be run in [$secondary_server]"
    }

    # defaults
    if( $local.IsPresent -and $data_folder -eq '\DATA' ){
        $data_folder = 'C:\MSSQL'
    }
    if( $data_folder -eq '\DATA' ){
        Throw "You must provide -data_root or -local switch"
    }
    if( $log_folder -eq '\Log' ){
        $log_folder = join-AnyPath ( split-path $data_folder -Parent ) 'Log'
    }

    # find most recent backup file
    $bak = (Get-ChildItem -Path $ship_destination_directory -Filter "*.bak" -File | Sort-Object LastWriteTime -Descending | Select-Object -First 1 ).FullName

    # check if any file found
    if(!$bak){
        throw "no backup found for $primary_database in $ship_destination_directory..."
    }

    if(!$sctipt.IsPresent){
        # create folder for database data and log
        if ( !(test-path $data_folder -PathType Container) ){
            out-shipMsg 'data_folder' $data_folder
            new-item -Path $data_folder -ItemType Directory -Force | out-null
        }
        if ( !(test-path $log_folder -PathType Container) ){
            out-shipMsg 'log_folder' $log_folder
            new-item -Path $log_folder -ItemType Directory -Force | out-null
        }
    }

<#
 
    if( $script.IsPresent ){
        # basic logical and physical
        $moves = @"
        MOVE N'$primary_database' TO N'$data_folder\$secondary_database.mdf',
        MOVE N'$($primary_database)_log' TO N'$log_folder\$($secondary_database)_log.ldf',
 
"@
    }else{
#>


        # get logical and physical files from primary database
        $query1 = @"
    select d.name [database], f.name [logical],
        'MOVE N'''+f.name+''' TO N'''
        +case f.type_desc when 'rows' then '$data_folder\'
            when 'log' then '$log_folder\'
            else '$data_folder\'
        end
        +case when f.type_desc='rows' and file_id = 1 then '$secondary_database.mdf'''
            when f.type_desc='rows' and file_id > 1 and file_id<100 then '$($secondary_database)_'+right('0'+convert(varchar(2),file_id),2)+'.ndf'''
            when f.type_desc='log' then '$($secondary_database)_log.ldf'''
            else '$($secondary_database)_'+convert(varchar(12),file_id)+'.ndf'''
        end [physical]
    FROM sys.master_files f
    INNER JOIN sys.databases d
        ON d.database_id = f.database_id
    where d.name = '$primary_database'
    order by d.name,f.file_id;
 
"@
        $files = invoke-sqlcmd `
            -ServerInstance $primary_server `
            -Database master `
            -Credential $credential `
            -TrustServerCertificate `
            -Query $query1 `
            -Verbose

        $moves = ""
        foreach( $file in $files ){
            $moves = $moves+' '+$file.physical+",`n"
        }
# }

    $query2 = @"
    -- use master
    RESTORE DATABASE [$secondary_database]
        FROM DISK = N'$bak'
        WITH FILE = 1,
$moves NORECOVERY, NOUNLOAD, STATS = 5;
 
    select [param] = 'restore', [value]='$secondary_database'
"@

    write-output $query2

    if( !$script.IsPresent ){
        invoke-sqlcmd `
            -ServerInstance $secondary_server `
            -Database master `
            -Credential $credential `
            -TrustServerCertificate `
            -Query $query2 `
            -Verbose
    }

    out-shipMsg 'from' "$bak"
    out-shipMsg 'Ret' '0' -flush
}

function set-shipSecondary{
[CmdletBinding(SupportsShouldProcess)]
    param(
        [switch]$script
    )
    $sqlparams = get-shipParam

    $query = @"
    -- Execute the following statements at the Secondary to configure Log Shipping
    -- for the database [$secondary_server].[$primary_database],
    -- the script needs to be run at the Secondary in the context of the [master] database.
    ---------------------------------------------------------------------------------------
 
    $sqlparams
 
    DECLARE @LS_Secondary__CopyJobId AS uniqueidentifier
    DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier
    DECLARE @LS_Secondary__SecondaryId AS uniqueidentifier
    DECLARE @LS_Add_RetCode As int
 
    EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary
         @primary_server = N'$primary_server'
        ,@primary_database = N'$primary_database'
        ,@backup_source_directory = N'$backup_share_folder'
        ,@backup_destination_directory = N'$ship_destination_directory'
        ,@copy_job_name = N'$copy_job_name'
        ,@restore_job_name = N'$restore_job_name'
        ,@file_retention_period = 4320
        ,@overwrite = 1
        ,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT
        ,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT
        ,@secondary_id = @LS_Secondary__SecondaryId OUTPUT
 
 
    select [param] = '@copy_job_name', value = convert(varchar(500), '$copy_job_name' )
    union all select '@restore_job_name', convert(varchar(500), '$restore_job_name' )
    union all select '@LS_Secondary__SecondaryId', convert(varchar(500), @LS_Secondary__SecondaryId )
    union all select '@LS_Add_RetCode', convert(varchar(500), @LS_Add_RetCode)
 
    IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
    BEGIN
        select [param]='@copy_schedule_name', value = convert(varchar(100), '$copy_schedule_name' )
 
        DECLARE @LS_SecondaryCopyJobScheduleUID As uniqueidentifier
        DECLARE @LS_SecondaryCopyJobScheduleID AS int
 
        EXEC msdb.dbo.sp_add_schedule
                 @schedule_name = N'$copy_schedule_name'
                ,@enabled = 1
                ,@freq_type = 4
                ,@freq_interval = 1
                ,@freq_subday_type = 4
                ,@freq_subday_interval = 15
                ,@freq_recurrence_factor = 0
                ,@active_start_date = $hoy
                ,@active_end_date = 99991231
                ,@active_start_time = 700
                ,@active_end_time = 235900
                ,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT
                ,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT
 
 
        EXEC msdb.dbo.sp_attach_schedule
                @job_id = @LS_Secondary__CopyJobId
                ,@schedule_id = @LS_SecondaryCopyJobScheduleID
 
 
        DECLARE @LS_SecondaryRestoreJobScheduleUID As uniqueidentifier
        DECLARE @LS_SecondaryRestoreJobScheduleID AS int
 
        EXEC msdb.dbo.sp_add_schedule
                @schedule_name = '$restore_schedule_name'
                ,@enabled = 1
                ,@freq_type = 4
                ,@freq_interval = 1
                ,@freq_subday_type = 4
                ,@freq_subday_interval = 15
                ,@freq_recurrence_factor = 0
                ,@active_start_date = $hoy
                ,@active_end_date = 99991231
                ,@active_start_time = 0
                ,@active_end_time = 235900
                ,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT
                ,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT
 
        select [param] = '@restore_schedule_name', value = convert( varchar(500), '$restore_schedule_name' )
 
 
        EXEC msdb.dbo.sp_attach_schedule
                @job_id = @LS_Secondary__RestoreJobId
                ,@schedule_id = @LS_SecondaryRestoreJobScheduleID
    END
 
 
    DECLARE @LS_Add_RetCode2 As int
 
    IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
    BEGIN
 
        select [param]='@secondary_database', value=convert(varchar(500), '$secondary_database' )
 
        EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database
                 @secondary_database = N'$secondary_database'
                ,@primary_server = N'$primary_server'
                ,@primary_database = N'$primary_database'
                ,@restore_delay = 0
                ,@restore_mode = 1
                ,@disconnect_users = 1
                ,@restore_threshold = 45
                ,@threshold_alert_enabled = 1
                ,@history_retention_period = 7200
                ,@overwrite = 1
                ,@ignoreremotemonitor = 1
 
        select [param]='@LS_Add_RetCode2', value=convert(varchar(500), @LS_Add_RetCode2 )
 
    END
 
 
    IF (@@error = 0 AND @LS_Add_RetCode = 0)
    BEGIN
        EXEC msdb.dbo.sp_update_job
                 @job_id = @LS_Secondary__CopyJobId
                ,@enabled = 1
 
        EXEC msdb.dbo.sp_update_job
                 @job_id = @LS_Secondary__RestoreJobId
                ,@enabled = 1
    END
 
    select [param]='Ret', value='0';
 
"@

    if( $script.IsPresent ){
        write-output $query
    }else{
        invoke-sqlcmd `
            -ServerInstance $secondary_server `
            -Database master `
            -Credential $credential `
            -TrustServerCertificate `
            -Query $query `
            -Verbose
    }

    out-shipMsg 'Ret' '0' -flush
}

# =======================================================================================
# INSTALL MONITORS
# =======================================================================================

function add-shipLinkedServer{
    param(
        [Parameter(Mandatory)]
        [string]$monitor_server,
        [System.Management.Automation.PSCredential]$monitor_credential,
        [string[]]$instance = ("primary", "secondary" )
    )
    $sqlparams = get-shipParam

    $monitor_server = $monitor_server.ToUpper()

    $query1 = @"
        $sqlparams
 
        if (db_name() != N'master')
        begin
            raiserror('Needs to run in master',16,1);
            return;
        end
 
        declare @server sysname = '$monitor_server'
        set @server = upper( @server );
        select @server = N'LOGSHIPLINK_' + substring(@server, 1, 90) + N'_' + cast(checksum(@server) as nvarchar(20));
 
        declare @name sysname = (
            select top 1
                [name]
            from sys.servers
            where [name] = @server
        );
 
        if @name is not null
        begin
            select [param]='linkedserver', [value]=@name;
            return;
        end
 
        declare @res int
        exec @res = master.dbo.sp_addlinkedserver @server = @server,
            @srvproduct =N'SQL Server'
            --,@provstr=N'Encrypt=Yes;TrustServerCertificate=yes'
            ;
 
        if @res=0
        begin
            exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@server
                ,@useself=N'True'
                -- ,@locallogin=NULL
                -- ,@rmtuser='$($monitor_credential.Username)',@rmtpassword='{rmtpassword}'
            ;
 
            exec sp_setnetname @server = @server,
                    @netname = N'MONITOR-SQL';
 
            exec master.dbo.sp_serveroption @server=@server, @optname=N'rpc', @optvalue=N'true';
 
            exec master.dbo.sp_serveroption @server=@server, @optname=N'rpc out', @optvalue=N'true';
 
            exec master.dbo.sp_serveroption @server=@server, @optname=N'data access', @optvalue=N'true';
 
            exec master.dbo.sp_serveroption @server=@server, @optname=N'use remote collation', @optvalue=N'true';
 
            exec master.dbo.sp_serveroption @server=@server, @optname=N'remote proc transaction promotion', @optvalue=N'true';
 
            exec master.dbo.sp_serveroption @server=@server, @optname=N'system', @optvalue=N'true';
 
            select [param]='linkedserver', [value]=@server;
        end
"@

    if( $instance -contains "primary" ){

        if( $monitor_server.ToUpper() -eq $primary_server.ToUpper() ){

            $server1 = $primary_server
        }else{

            $res1 = invoke-sqlcmd `
                -ServerInstance $primary_server `
                -Database master `
                -Credential $credential `
                -TrustServerCertificate `
                -Query $query1 `
                -Verbose
            $server1 = get-ShipValue $res1 'linkedserver'

        }

        if( $server1 ){

            out-shipMsg 'primary-linkedserver' $server1
        }else{

            out-shipMsg 'primary-linkedserver' "unable to create linked server in $primary_server "
        }
    }

    if( $instance -contains "secondary" ){

        if( $monitor_server.ToUpper() -eq $secondary_server.ToUpper() ){

            $server2 = $secondary_server
        }else{

            $res2 = invoke-sqlcmd `
                -ServerInstance $secondary_server `
                -Database master `
                -Credential $credential `
                -TrustServerCertificate `
                -Query $query1 `
                -Verbose
            $server2 = get-ShipValue $res2 'linkedserver'
        }

        if( $server2 ){

            out-shipMsg 'secondary-linkedserver' $server2
        }else{

            out-shipMsg 'secondary-linkedserver' "unable to create linked server in $secondary_server "
        }
    }

    out-shipMsg 'Ret' '0' -flush
}

function set-shipMonitorPrimary{
[CmdletBinding(SupportsShouldProcess)]
    param(
        [Parameter(Mandatory)]
        [string]$monitor_server
    )
    # add linked server if it does not exists
    add-shipLinkedServer -monitor_server $monitor_server -instance "primary"

    $sqlparams = get-shipParam

    # pull primary db info
    $query1 = @"
        $sqlparams
 
        declare @primary_id uniqueidentifier;
 
        select top 1
            @primary_id = primary_id
        FROM msdb.dbo.log_shipping_monitor_primary
        WHERE primary_database = '$primary_database';
 
        select [param]='@primary_id', value=convert(varchar(500), @primary_id );
"@
    $res = invoke-sqlcmd `
        -ServerInstance $primary_server `
        -Database master `
        -Credential $Credential `
        -TrustServerCertificate `
        -Query $query1 `
        -Verbose
    $primary_id = get-ShipValue $res '@primary_id'

    if( !$primary_id ){
        throw "Error: Could not find [primary_id] in $primary_server for $primary_database ..."
    }


    # set the primary monitor
    $query2 = @"
        $sqlparams
 
        select [param]='primary_database', value=convert(varchar(500), '$primary_database' )
 
        declare @primary_id uniqueidentifier = '$primary_id';
 
        EXEC msdb.dbo.sp_processlogshippingmonitorprimary
            @mode = 1
            ,@primary_id = @primary_id
            ,@primary_server = N'$primary_server'
            ,@monitor_server = N'$monitor_server'
            ,@monitor_server_security_mode = 1
            ,@primary_database = N'$primary_database'
            ,@backup_threshold = 45
            ,@threshold_alert = 14420
            ,@threshold_alert_enabled = 1
            ,@history_retention_period = 7200
 
        select [param]='primary monitor', value='$monitor_server';
"@

    invoke-sqlcmd `
        -ServerInstance $monitor_server `
        -Database master `
        -Credential $credential `
        -TrustServerCertificate `
        -Query $query2 `
        -Verbose

    # update primary server with monitor info
    $query3 = @"
        $sqlparams
 
        declare @primary_id uniqueidentifier = '$primary_id';
 
        UPDATE msdb.dbo.log_shipping_primary_databases SET
            monitor_server = '$monitor_server'
            ,user_specified_monitor = 1
        WHERE primary_id = @primary_id;
 
        select [param]='$primary_server', value='monitor updated';
"@
    invoke-sqlcmd `
        -ServerInstance $primary_server `
        -Database master `
        -Credential $credential `
        -TrustServerCertificate `
        -Query $query3 `
        -Verbose

    out-shipMsg 'Ret' 0

    out-shipMsg -flush

    # https://www.sqlservercentral.com/articles/adding-a-log-shipping-monitor

    # https://www.mssqltips.com/sqlservertip/2799/steps-to-add-log-shipping-monitor-into-an-existing-sql-server/

}

function set-shipMonitorSecondary{
[CmdletBinding(SupportsShouldProcess)]
    param(
        [Parameter(Mandatory)]
        [string]$monitor_server,
        [switch]$script
    )
    # add linked server if it does not exists
    add-shipLinkedServer -monitor_server $monitor_server -instance "secondary"

    $sqlparams = get-shipParam

    # pull secondary db info
    $query1 = @"
        $sqlparams
        declare @secondary_id uniqueidentifier
 
        select top 1
            @secondary_id = secondary_id
        from msdb.dbo.log_shipping_monitor_secondary
        where primary_server=N'$primary_server'
            and primary_database = N'$primary_database'
        -- and secondary_server= N'$secondary_server'
        -- and secondary_database= N'$secondary_database'
 
        select [param]='@secondary_id', value=convert(varchar(500), @secondary_id )
"@

    if( $script.IsPresent ){
        write-output $query1
    }else{
        $res = invoke-sqlcmd `
            -ServerInstance $secondary_server `
            -Database master `
            -Credential $Credential `
            -TrustServerCertificate `
            -Query $query1 `
            -Verbose
    }


    # set secondary monitor
    if( $script.IsPresent ){
        $secondary_id_src = '-- variable @secondary_id from previous script'
    }else{
        $secondary_id = get-ShipValue $res '@secondary_id'
        $secondary_id_src = @"
        declare @secondary_id uniqueidentifier = N'$secondary_id';
"@
        out-shipMsg 'secondary_id' $secondary_id
    }

    $query2 = @"
        $sqlparams
        $secondary_id_src
 
        select [param]='secondary_database', value=convert(varchar(500), '$secondary_database' )
 
        EXEC msdb.dbo.sp_processlogshippingmonitorsecondary
            @mode = 1
            ,@secondary_server = N'$secondary_server'
            ,@secondary_database = N'$secondary_database'
            ,@secondary_id = @secondary_id
            ,@primary_server = N'$primary_server'
            ,@primary_database = N'$primary_database'
            ,@restore_threshold = 45
            ,@threshold_alert = 14421
            ,@threshold_alert_enabled = 1
            ,@history_retention_period = 5760
            ,@monitor_server = N'$monitor_server'
            ,@monitor_server_security_mode = 1
            --,@monitor_server_login = @monitor_server_login
            --,@monitor_server_password = @monitor_server_password
            --,@monitor_connection_options = N'Encrypt=Mandatory;TrustServerCertificate=Yes;'
 
        select [param]='secondary monitor', [value]='$monitor_server';
"@


    if( $script.IsPresent ){
        write-output $query2
    }else{
        if( $secondary_id ){

            invoke-sqlcmd `
                -ServerInstance $monitor_server `
                -Database master `
                -Credential $credential `
                -TrustServerCertificate `
                -Query $query2 `
                -Verbose
        }else{
            throw "Error: Could not find [secondary_id] in $secondary_server for $primary_database ..."
        }
    }


    # update secondary with monitory info
    $query3 = @"
        $sqlparams
 
        declare @secondary_id uniqueidentifier = N'$secondary_id';
 
        UPDATE msdb.dbo.log_shipping_secondary SET
             monitor_server = '$monitor_server'
            ,user_specified_monitor = 1
        WHERE secondary_id = @secondary_id;
 
        select [param]='$secondary_server', [value]='monitor updated';
"@

    if( $secondary_id ){
        invoke-sqlcmd `
            -ServerInstance $secondary_server `
            -Database master `
            -Credential $credential `
            -TrustServerCertificate `
            -Query $query3 `
            -Verbose
    }

    out-shipMsg 'Ret' 0

    out-shipMsg -flush
}

# =======================================================================================
# install full process
# =======================================================================================

function install-shipConfig{
    param(
        [string]$primary_server,
        [string]$primary_database,
        [string]$primary_root,
        [string]$secondary_server,
        [string]$secondary_database,
        [string]$secondary_root,
        [string]$data_root,
        [string]$log_root,
        [string[]]$databases,
        [string]$monitor_server,
        [switch]$robocopy
    )

    if ( $primary_database -or $primary_server ) {
        set-shipParam @PSBoundParameters
        $secondary_database = $script:secondary_database
        $log_root = $script:log_root
    }else{
        # remove parameter, suse preset variables,
        remove-variable primary_server, primary_database, primary_root
        remove-variable secondary_server, secondary_database, secondary_root
        remove-variable data_root, log_root, monitor_server, databases
        $params = @{
            primary_server      = $env:ship_primary_server
            primary_database    = $env:ship_primary_database
            primary_root        = $env:ship_primary_root
            secondary_server    = $env:ship_secondary_server
            secondary_database  = $env:ship_secondary_database
            secondary_root      = $env:ship_secondary_root
            data_root           = $env:ship_data_root
            log_root            = $env:ship_log_root
            monitor_server      = $env:ship_monitor_server
            databases           = $env:ship_databases.split(' ')
        }
        set-shipParam @params
    }

    if( !$databases ){
        $databases += $primary_database
    }

    # verify if any missing parameters ???


    foreach( $database in $databases ){
        out-shipMsg 'database' $database
        out-shipMsg 'computername' $env:COMPUTERNAME.ToUpper()
        out-shipMsg 'primary_host' $primary_host.ToUpper()
        out-shipMsg 'secondary_host' $secondary_host.ToUpper()

        $primary_database = $database
        $env:ship_primary_database = $primary_database
        $secondary_database = $database
        $env:ship_secondary_database = $secondary_database

        if( $env:COMPUTERNAME.ToUpper() -eq $primary_host.ToUpper() ){

            # one by one
            add-shipFolder -just_primary

            # set model to full run job
            set-shipPrimary -full -run

            # wait for previous job to finish
            backup-shipPrimary -wait

            if( ![string]::IsNullOrEmpty($monitor_server) ){
                set-shipMonitorPrimary -monitor_server $monitor_server
            }
        }

        if( $env:COMPUTERNAME.ToUpper() -eq $secondary_host.ToUpper() ){
            # one by one
            add-shipFolder -just_primary

            copy-shipBackup -robocopy:$robocopy

            restore-shipSecondary

            set-shipSecondary

            if( ![string]::IsNullOrEmpty($monitor_server) ){
                set-shipMonitorSecondary -monitor_server $monitor_server
            }
        }
    }

    out-shipMsg -flush
}

function sync-shipSchedule{
    param(
        [int]$freq  = 15,      # mins 15 to 60
        [string[]]$databases
    )

    $params = get-shipParam

    if( $freq -lt 10 -or $freq -gt 60  ){
        throw 'frequency must be 15-60'
    }

    if( !$databases ){
        # collect all log shipping backup jobs
        $queryjobs = @"
        $params
        select
            d.[name],
            sum(f.size)[size]
        from sys.master_files AS f
        inner join sys.databases AS d
            ON f.database_id = d.database_id
        inner join(
            select replace( [name], 'LSBackup_', '' ) [name]
            from msdb.dbo.sysjobs
            where [name] like 'LSBackup_%'
                and enabled =1
                    and category_id = 6
        ) j
            on j.name = d.name
        GROUP BY d.[name]
        ORDER BY [size];
"@
        $dbs = invoke-sqlcmd `
            -ServerInstance $primary_server `
            -Database master `
            -Credential $credential `
            -TrustServerCertificate `
            -Query $queryjobs `
            -Verbose

        $dbs | foreach-object{
             $databases += $_.name
        }
    }
    $dbcnt = $databases.Count
    out-shipMsg 'databases' $dbcnt

    if( $dbcnt -gt $freq ){
        # distribute in batches with 1 db per minute gap
        $f = 0
        $t = $freq-1
        while( $f -lt $dbcnt ){
            # write-host " $f $t "

            sync-shipSchedule $freq $databases[$f..$t]

            # next batch
            $f = $t+1
            $t += $freq
        }
    }else{
        # sparse databases evenly in freq time
        [int]$gap = $freq / $dbcnt
        [int]$mid = $freq / 2
        # write-host "gap $gap"
        # @freq_subday_interval
        $fsi = $freq
        # @active_start_time
        [int]$ast = 0
        $databases | foreach-object{
            $database = $_
            out-shipMsg 'database' $database

            # set environment
            $env:ship_primary_database = $database
            get-shipParam -nosql -nomsg

            # here you do the backup job schedule adjustment
            $querysch1 = @"
            execute msdb.dbo.sp_update_schedule
                @name = '$backup_schedule_name',
                @freq_subday_interval = $fsi,
                @active_start_time = $ast;
 
"@
            $querysch1
            out-shipMsg "$backup_schedule_name" $ast
            invoke-sqlcmd `
                -ServerInstance $primary_server `
                -Database msdb `
                -Credential $credential `
                -TrustServerCertificate `
                -Query $querysch1 `
                -Verbose

            # now the jobs in secondary server with offsets
            $astc = $ast + ($mid*100)
            $astr = $ast + ($freq*100)
            $querysch2 = @"
            execute msdb.dbo.sp_update_schedule
                @name = '$copy_schedule_name',
                @freq_subday_interval = $fsi,
                @active_start_time = $astc;
 
            execute msdb.dbo.sp_update_schedule
                @name = '$restore_schedule_name',
                @freq_subday_interval = $fsi,
                @active_start_time = $astr;
 
"@
            $querysch2
            out-shipMsg "$copy_schedule_name" $astc
            out-shipMsg "$restore_schedule_name" $astr
            $res = invoke-sqlcmd `
                -ServerInstance $secondary_server `
                -Database msdb `
                -Credential $credential `
                -TrustServerCertificate `
                -Query $querysch1 `
                -Verbose
            $res
            ( $res | out-string )
            if( ( $res | out-string ).contains('Msg ') ){
                out-shipMsg "$copy_schedule_name" 'Erorr changing schedule...'
                out-shipMsg "$restore_schedule_name" 'Erorr changing schedule...'
            }



            # next jobs
            [int]$ast = $ast+($gap*100)
            if( $ast -ge ($freq*100) ){ $ast = 0 }
        }
        out-shipMsg -flush
    }
}

# =======================================================================================
# CLEANING
# =======================================================================================

function remove-shipConfig{
[System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")]
[CmdletBinding(SupportsShouldProcess)]
    param(
        [switch]$backups,
        [switch]$logs
    )
    $sqlparams = get-shipParam

    # on the primary server
    # =================================
    $query = @"
 
    $sqlparams
 
    -- must run from master
    EXEC master.dbo.sp_delete_log_shipping_primary_secondary
        @primary_database = N'$primary_database'
        ,@secondary_server = N'$secondary_server'
        ,@secondary_database = N'$secondary_database' ;
 
    exec sp_delete_log_shipping_primary_database
        @database = N'$primary_database' ;
 
    select [param]='primary_server', [value]=@@servername
 
"@
    if( $PSCmdlet.ShouldProcess($query, 'delete') ){
        invoke-sqlcmd `
            -ServerInstance $primary_server `
            -Database master `
            -Credential $credential `
            -TrustServerCertificate `
            -Query $query `
            -Verbose
    }

    # on the secondary server
    # =================================
    $query = @"
 
    $sqlparams
 
    EXEC master.dbo.sp_delete_log_shipping_secondary_database
        @secondary_database = N'$secondary_database' ;
 
 
    exec sp_delete_log_shipping_secondary_primary
        @primary_server = N'$primary_server'
        ,@primary_database = N'$primary_database'
 
    select [param]='secondary_server', [value]=@@servername
 
"@
    if( $PSCmdlet.ShouldProcess($query, 'delete') ){
        invoke-sqlcmd `
            -ServerInstance $secondary_server `
            -Database master `
            -Credential $credential `
            -TrustServerCertificate `
            -Query $query `
            -Verbose
    }

    # back at primary server (again)
    # =================================
    $query = @"
 
    $sqlparams
 
    EXEC master.dbo.sp_delete_log_shipping_primary_database
        @database = N'$primary_database' ;
 
    select [param]='primary_server', [value]=@@servername
"@
    if( $PSCmdlet.ShouldProcess($query, 'delete') ){
        invoke-sqlcmd `
            -ServerInstance $primary_server `
            -Database master `
            -Credential $credential `
            -TrustServerCertificate `
            -Query $query `
            -Verbose
    }

    out-shipMsg -flush


    # remove jobs
    remove-shipJob

    # remove backups
    if( $backups.IsPresent ){
        remove-shipFiles -backups -force
    }

    # remove shipping logs
    if( $logs.IsPresent ){
        remove-shipFiles -logs -force
    }

    out-shipMsg -flush
}

function remove-shipJob{
[CmdletBinding(SupportsShouldProcess)]
    param(
        [switch]$nada
    )
    $nada = $null
    get-shipParam > null

    # on the primary server
    # =================================
    $query = @"
    select [param]='@primary_server', value='$primary_server'
    union all select [param]='@backup_job_name', value= N'$backup_job_name' ;
 
    if exists(
        select [name]
        from msdb.dbo.sysjobs
        where name = '$backup_job_name'
    )
        exec msdb.dbo.sp_delete_job @job_name = N'$backup_job_name' ;
    else
        select [param]='$backup_job_name', value='Job not found';
 
"@
    invoke-sqlcmd `
        -ServerInstance $primary_server `
        -Database master `
        -Credential $credential `
        -TrustServerCertificate `
        -Query $query `
        -Verbose

    # on the secondary server
    # =================================
    $query = @"
    select [param]='@secondary_server', value='$secondary_server'
    union all select [param]='@copy_job_name', value='$copy_job_name';
 
    if exists(
        select [name]
        from msdb.dbo.sysjobs
        where name = '$copy_job_name'
    )
        exec msdb.dbo.sp_delete_job @job_name = N'$copy_job_name' ;
    else
        select [param]='$copy_job_name', value='Job not found';
    go
 
    select [param]='@restore_job_name', value= '$restore_job_name' ;
 
    if exists(
        select [name]
        from msdb.dbo.sysjobs
        where name = '$restore_job_name'
    )
        exec msdb.dbo.sp_delete_job @job_name = N'$restore_job_name' ;
    else
        select [param]='$restore_job_name', value='Job not found';
"@

    #if( $PSCmdlet.ShouldProcess($query, 'remove') ){
        invoke-sqlcmd `
            -ServerInstance $secondary_server `
            -Database master `
            -Credential $credential `
            -TrustServerCertificate `
            -Query $query `
            -Verbose
    #}

    out-shipMsg -flush
}

function remove-shipFiles{
[System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")]
    [CmdletBinding(SupportsShouldProcess)]
    param(
        [switch]$force,
        [switch]$backups,
        [switch]$logs
    )
    get-shipParam -nosql > null


    $filter = ".donotfind"
    if( $backups.IsPresent ){
        $filter = "*.bak"
        $switchx = '-backups'
    }elseif( $logs.IsPresent ){
        $filter = "*.trn"
        $switchx = '-logs'
    }else{
        throw "You must specify either -backups or -logs"
    }
    out-shipMsg "remove" $filter

    write-output ( Get-PSCallStack )[1].Command
    if( $logs.IsPresent -and ( Get-PSCallStack )[1].Command -ne 'remove-shipConfig' ){
        throw 'to remove log files run remove-shipConfig -logs '
    }

    if( $env:COMPUTERNAME.ToUpper() -eq $primary_host.ToUpper() ){

        out-shipMsg  'primary_server' $primary_server
        $database_folder = join-AnyPath $primary_root $primary_database
        out-shipMsg  'database_folder' $database_folder

        $baks = (Get-ChildItem -Path $database_folder -Filter $filter -File ).FullName

        foreach( $bak in $baks ){
            out-shipMsg 'removing' $bak
            if( $PSCmdlet.ShouldProcess($bak, 'remove') ){
                if( $force.IsPresent ){
                    remove-item -path $bak -Force
                }else{
                    remove-item -path $bak -ErrorAction Continue
                }
            }
        }
    }else{
        out-shipMsg 'primary_server' "run remove-shipFiles $switchx in $primary_server !!!"
    }

    if( $env:COMPUTERNAME.ToUpper() -eq $secondary_host.ToUpper() ){

        out-shipMsg  'secondary_server' $secondary_server
        $database_folder = join-AnyPath $secondary_root $secondary_database
        out-shipMsg  'database_folder' $database_folder

        $baks = (Get-ChildItem -Path $database_folder -Filter $filter -File ).FullName

        foreach( $bak in $baks ){
            out-shipMsg 'removing' $bak
            if( $PSCmdlet.ShouldProcess($bak, 'remove') ){
                if( $force.IsPresent ){
                    remove-item -path $bak -Force
                }else{
                    remove-item -path $bak -ErrorAction Continue
                }
            }
        }
    }else{
        out-shipMsg 'secondary_server' "run remove-shipFiles $switchx in $secondary_server !!!"
    }

    out-shipMsg -flush
}

function clear-shipHistory{

    $sqlparams = get-shipParam

    # on the primary server
    # =================================
    $query = @"
    $sqlparams
 
    delete from msdb.[dbo].[log_shipping_monitor_primary]
    where primary_database = N'$primary_database' ;
 
    delete from msdb.[dbo].[log_shipping_primary_databases]
    where primary_database = N'$primary_database' ;
 
    delete from msdb.[dbo].[log_shipping_primary_secondaries]
    where secondary_database = N'$secondary_database' ;
"@
    invoke-sqlcmd `
        -ServerInstance $primary_server `
        -Database master `
        -Credential $credential `
        -TrustServerCertificate `
        -Query $query `
        -Verbose


    # on the secondary server
    # =================================
    $query = @"
    $sqlparams
 
    delete from msdb.[dbo].[log_shipping_monitor_secondary]
    where secondary_database = N'$secondary_database' ;
 
    delete from msdb.[dbo].[log_shipping_secondary]
    where primary_database = N'$primary_database' ;
 
    delete from msdb.[dbo].[log_shipping_secondary_databases]
    where secondary_database = N'$secondary_database' ;
"@
    invoke-sqlcmd `
        -ServerInstance $secondary_server `
        -Database master `
        -Credential $credential `
        -TrustServerCertificate `
        -Query $query `
        -Verbose
}

# =======================================================================================

function add-shipAgentRight{
    param(
        [string]$agent
    )
    $rights = "SeServiceLogonRight", "SeAssignPrimaryTokenPrivilege", "SeChangeNotifyPrivilege",  "SeIncreaseQuotaPrivilege", "SeBatchLogonRight"

    ( $rights ) | foreach-object{
        set-UserRights -UserName $agent -UserRight $_ -AddRight
    }
}

function get-shipAgentRight{
    param(
        [string]$agent
    )
    get-userRights -UserName $agent
}

# =======================================================================================
# SECRETS
# =======================================================================================

function add-shipSecret{
[Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidUsingConvertToSecureStringWithPlainText", "")]
    param(
        [string]$name,
        [pscredential]$credential,
        [byte[]]$key,
        [switch]$force,
        [string]$server = ".",
        [string]$database = "master"
    )

    # create vault table
    $query=@"
        if not exists ( select [name] from sys.databases where [name]='$database' )
            select [param]='database', [value]='not found';
        else
        begin
            select [param]='database', [value]='dbaship';
            if not exists( select [name] from sys.tables where object_id = object_id('dbo.dbaship_vault') )
            begin
                create table dbo.dbaship_vault(
                    valult_id int identity primary key,
                    [name] varchar(500),
                    [UserName] varchar(1000),
                    [value] varchar(1000),
                    last_updated datetime default ( getdate() )
                );
                revoke select, insert, update, delete on object::dbaship_vault from public;
                select [param]='table', value='dbaship_vault';
            end
        end
"@
    $res = invoke-sqlcmd `
        -ServerInstance $server `
        -Database master `
        -TrustServerCertificate `
        -Query $query
    $db = get-shipValue $res 'database'

    # this is for database other than master
    if( $db -ne 'dbaship' ){
        throw "Error: database not found..."
        out-shipMsg -flush
    }

    # verify if secret name already exists
    $cred1 = get-shipSecret $name -checkonly
    if( $cred1 -and !$force.IsPresent ){
        throw "Error: secret already exists..."
    }

    # if key is not provided, get a randome one
    if( !$key ){
        $key = get-shipKey
    }
    $keystr = "( $($key -join ',') )"

    # encrypt
    $secure = ConvertTo-SecureString -String $credential.UserName -AsPlainText
    $username = ConvertFrom-SecureString -SecureString $secure -Key $key
    $value = ConvertFrom-SecureString -SecureString $credential.Password -Key $key

    $query = @"
        merge into dbaship_vault tgt
        using (
            select [name]='$name', [username]='$username', [value]='$value'
        )src
            on tgt.[name] = src.[name]
        when matched then
            update set
                [username] = src.[username],
                [value] = src.[value],
                last_updated = getdate()
        when not matched by target then
            insert( [name], [username], [value] )
            values( src.[name], src.[username], src.[value] );
 
        select [param]='$name', [value]='PSCredential'
        union all
        select [param]='`$key =', [value]='$keystr';
"@
    invoke-sqlcmd `
        -ServerInstance $server `
        -Database $database `
        -TrustServerCertificate `
        -Query $query `
        -Verbose

    out-shipMsg -flush
}

function get-shipSecret{
    param(
        [string]$name,
        [byte[]]$key,
        [string]$server = ".",
        [switch]$checkonly,
        [string]$database = "master"
    )

    $query = @"
        declare @username varchar(max), @value varchar(max);
        select
            @username = [username],
            @value = [value]
        from dbo.dbaship_vault
        where [name] = '$name';
 
        select [param] = 'username', [value] = isnull(@username,'')
        union all
        select [param] = 'value', [value] = @value;
"@
    $res = invoke-sqlcmd `
        -ServerInstance $server `
        -Database $database `
        -TrustServerCertificate `
        -Query $query `
        -Verbose
    # collect username
    $encrypted_name = get-shipValue $res 'username'

    # check if exists only
    if( $checkonly.IsPresent ){
        if( $null -eq $encrypted_name -or $encrypted_name -eq "" -or !$encrypted_name ){
            return $false
        }else{
            return $true
        }
    }

    if( !$key ){
        throw 'Error: invalid key...'
    }

    if( !(get-shipSecret $name -checkonly) ){
        throw 'secret not found...'
    }

    # decript username and value
    $secure_username = ConvertTo-SecureString -String $encrypted_name -Key $key -ErrorAction SilentlyContinue
    if( $null -eq $secure_username ){
        throw 'Error: invalid key...'
    }
    $username = ConvertFrom-SecureString -SecureString $secure_username -AsPlainText
    $encrypted_secure = get-shipValue $res 'value'
    $secure = ConvertTo-SecureString -String $encrypted_secure -Key $key

    # create credential from username and value
    [pscredential]$newCredential = New-Object System.Management.Automation.PSCredential( $username, $secure )
    return $newCredential
}

function remove-shipSecret{
[CmdletBinding(SupportsShouldProcess)]
    param(
        [string]$name,
        [byte[]]$key,
        [string]$server = ".",
        [string]$database = "master"
    )

    # verify if secret name already exists
    if( !(get-shipSecret $name -checkonly) ){
        throw "Error: secret not found"
    }

    $cred2 = get-shipSecret $name $key
    if( !$cred2 ){
        throw 'invalid key...'
    }


    $query = @"
        delete v
        from dbo.dbaship_vault v
        where [name] = '$name';
        select [param]='rowcount', [value]=@@rowcount;
"@
    $res = invoke-sqlcmd `
        -ServerInstance $server `
        -Database $database `
        -TrustServerCertificate `
        -Query $query `
        -Verbose

    $rows = get-shipValue $res 'rowcount'
    if( $rows -gt 0 ){
        out-shipMsg $name 'deleted'
    }else{
        #out-shipMsg $name
        write-output "Error: unable to delete $name"
    }
    out-shipMsg -flush
}

function get-shipKey{
    # build a randon 256 (32-byte) array for encryption
    $newKey = New-Object Byte[] 32
    [Security.Cryptography.RNGCryptoServiceProvider]::Create().GetBytes($newKey)

    return $newKey

<#
    $bytes = New-Object byte[] 32
    [System.Security.Cryptography.RandomNumberGenerator]::Create().GetBytes($bytes)
    $key = [BitConverter]::ToString($bytes).Replace("-", [string]::Empty)
    Write-Host "Generated AES Key: $key"
#>
}

# =======================================================================================
# LOGINS
# =======================================================================================
function out-shipLogin{
    param(
        [string[]]$as_is,
        [switch]$push
    )
    #
    get-shipParam -nosql

    # ckeck for sp_help_revlogin, create it
    $querysp = @"
    select [param]=name, [value]=object_id
    from master.sys.procedures
    where name = 'sp_help_revlogin'
"@
    $sp = invoke-sqlcmd `
        -ServerInstance $primary_server `
        -Database master `
        -Credential $credential `
        -TrustServerCertificate `
        -Query $querysp `
        -Verbose
    $found = get-shipValue $sp 'sp_help_revlogin'
    if($found){
        out-shipMsg 'sp_help_revlogin' 'ok'
    }else{
        write-output "not found..."
        $sp_path = join-AnyPath $PSScriptRoot 'private' 'dbo.sp_help_revlogin'
        # $sp_path
        $querysp = Get-Content $sp_path
        invoke-sqlcmd `
            -ServerInstance $primary_server `
            -Database master `
            -Credential $credential `
            -TrustServerCertificate `
            -InputFile $sp_path `
            -Verbose
        out-shipMsg 'sp_help_revlogin' 'Installed'
    }

    # get active non sysadmins ( for exclusions )
    # sysadmins and disabled logins needs to be migrated using as_is parameter
    $querylo = @"
        select p.[name]
        FROM sys.server_principals p
        LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
        WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
            AND p.[name] <> 'sa'
            AND p.[name] not like '##%'
            and p.[name] not like 'NT %'
            and p.is_disabled = 0
            and p.[sid] not in(
                -- sysadmins
                SELECT
                    sp.sid
                from sys.server_role_members AS srm
                join sys.server_principals AS sp
                    ON srm.member_principal_id = sp.principal_id
                WHERE
                    srm.role_principal_id = SUSER_ID('sysadmin')
            )
        ORDER BY p.[name]
"@
    $los = invoke-sqlcmd `
        -ServerInstance $primary_server `
        -Database master `
        -Credential $credential `
        -TrustServerCertificate `
        -query $querylo `
        -Verbose
    #$sals = $sas.loginName

    ("`n--`n-- active non sysadmin logins `n--`n`n") | out-file $login_file
    ( $los ) | foreach-object{
        $login = $_.name
        out-shipMsg 'login' "$login"
        $querylo2 = "exec dbo.sp_help_revlogin '$login' "
        $tempFile = New-TemporaryFile

        invoke-sqlcmd `
            -ServerInstance $primary_server `
            -Database master `
            -Credential $credential `
            -TrustServerCertificate `
            -query $querylo2 `
            -verbose 4> "$tempFile"

        ( get-content $tempFile -raw ) | out-file $login_file -Append
        Remove-Item -Path $tempFile.FullName -ErrorAction SilentlyContinue

    }
    out-shipMsg 'file' "$login_file"
    out-shipMsg -flush

    if( $push.IsPresent ){

        # push to secondary server




    }


}

function out-shipOrphan{
    param(
        [switch]$push
    )
    $queryo = @"
    select dp.name
    FROM sys.database_principals AS dp
    LEFT JOIN sys.server_principals AS sp
        ON dp.sid = sp.sid
    WHERE sp.sid IS NULL
        --AND dp.authentication_type_desc = 'INSTANCE' -- Filters for SQL Server authentication users
        AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'MS_DataCollectorInternalUser') -- Excludes system accounts
        and dp.type_desc not in('DATABASE_ROLE')
"@

    # get a list of databases with primary-secondary matches from ls monitor

    # query each database for orphans

    # create file, create file of distinct orphans


}




# =======================================================================================
# PUBLIC
# =======================================================================================

# Dot-source all .ps1 files in the Public directory
$publicFolderPath = Join-Path -Path $PSScriptRoot -ChildPath 'private'
if (Test-Path -Path $publicFolderPath) {
    Get-ChildItem -Path $publicFolderPath -Filter '*.ps1' | ForEach-Object {
        . $_.FullName
    }
}

# Export-ModuleMember -Function 'get-UserRights', 'set-UserRights'