Invoke-DbaDbLogShipping: @restore_threshold not being passed when configuring monitor server causing syntax error #5308

garyhampson · 2019-03-26T13:51:47Z

Before submitting a bug report:

Collect output of following command and paste below:

& {"### PowerShell version:`n$($PSVersionTable | Out-String)"; "`n### dbatools Module version:`n$(gmo dbatools -List | select name, path, version | fl -force | Out-String)"}

Running latest release of dbatools
Verified errors are not related to permissions
If issue is with Copy-DbaDatabase, replicate issue using Backup-DbaDatabase ... | Restore-DbaDatabase ...

Note that we do not have the resources to make Copy-DbaDatabase work in every environment. Instead, we try to ensure Backup & Restore work in your environment.

Environmental data

Name                           Value
----                           -----
PSVersion                      5.1.14393.2828
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.14393.2828
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1




### dbatools Module version:


Name    : dbatools
Path    : C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.795\dbatools.psd1
Version : 0.9.795

SQL Server:

Microsoft SQL Server 2016 (SP2-CU5) (KB4475776) - 13.0.5264.1 (X64)   Jan 10 2019 18:51:38   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

Steps to Reproduce

/*
    Any T-SQL commands involved or used to produce test objects/data.
*/

$params = @{
SourceSqlInstance = 'SQL01'
DestinationSqlInstance = 'SQL02'
Database = 'test_dbtest'
BackupNetworkPath = '\\SQL01\LogShipping'
BackupLocalPath = 'Z:\LogShipping'
BackupScheduleFrequencySubdayType = 'Minutes'
BackupScheduleFrequencySubdayInterval = 15
CompressBackup = $true
CopyScheduleFrequencySubdayType = 'Minutes'
CopyScheduleFrequencySubdayInterval = 15
GenerateFullBackup = $true
RestoreScheduleFrequencySubdayType = 'Minutes'
RestoreScheduleFrequencySubdayInterval = 15
CopyDestinationFolder = '\\SQL02\LogShipping'
Force = $true
NoRecovery = $true
}
$params
Invoke-DbaDbLogShipping @params -verbose

Expected Behavior

Actual Behavior

VERBOSE: [09:22:24][New-DbaLogShippingSecondaryDatabase] Configuring monitor server for secondary database test_enlinktest.
VERBOSE: [09:22:24][New-DbaLogShippingSecondaryDatabase] Executing query:
EXEC msdb.dbo.sp_processlogshippingmonitorsecondary @mode = 1
                    ,@secondary_server = 'SQL02'
                    ,@secondary_database = 'test_dbtest'
                    ,@secondary_id = ''
                    ,@primary_server = ''
                    ,@primary_database = ''
                    ,@restore_threshold = 
                    ,@threshold_alert = 0
                    $params = @{
SourceSqlInstance = 'SQL01'
DestinationSqlInstance = 'SQL02'
Database = 'test_dbtest'
BackupNetworkPath = '\\SQL01\LogShipping'
BackupLocalPath = 'Z:\LogShipping'
BackupScheduleFrequencySubdayType = 'Minutes'
BackupScheduleFrequencySubdayInterval = 15
CompressBackup = $true
CopyScheduleFrequencySubdayType = 'Minutes'
CopyScheduleFrequencySubdayInterval = 15
GenerateFullBackup = $true
RestoreScheduleFrequencySubdayType = 'Minutes'
RestoreScheduleFrequencySubdayInterval = 15
CopyDestinationFolder = '\\SQL02\LogShipping'
Force = $true
NoRecovery = $true
}
$params
Invoke-DbaDbLogShipping @params -verbose
                    ,@history_retention_period = 0
                    ,@monitor_server = 'SQL01'
                    ,@monitor_server_security_mode = 1 
WARNING: [09:22:24][Invoke-DbaQuery] [SQL01] Failed during execution | Incorrect syntax near ','.
VERBOSE: [09:22:24][New-DbaLogShippingSecondaryDatabase] Updating monitor information for the secondary database test_dbtest.
VERBOSE: [09:22:24][New-DbaLogShippingSecondaryDatabase] Executing query:

                UPDATE msdb.dbo.log_shipping_secondary
                SET monitor_server = 'SQL01', user_specified_monitor = 1
                WHERE secondary_id = ''
                
WARNING: [09:22:24][New-DbaLogShippingSecondaryDatabase] Conversion failed when converting from a character string to uniqueidentifier.
WARNING: [09:22:24][New-DbaLogShippingSecondaryDatabase] Error executing the query.
Exception calling "query" with "1" argument(s): "Exception calling "ExecuteWithResults" with "1" argument(s): "Execute with results failed for Database 'master'. ""

                UPDATE msdb.dbo.log_shipping_secondary
                SET monitor_server = 'SQL01', user_specified_monitor = 1
                WHERE secondary_id = ''
                 | Conversion failed when converting from a character string to uniqueidentifier.
VERBOSE: [09:22:24][Invoke-DbaDbLogShipping] Finished setting up log shipping.

The text was updated successfully, but these errors were encountered:

garyhampson · 2019-03-26T14:58:20Z

Here's the full -verbose output:
`PS D:\Code\dbatoolkit> $params

Name Value

RestoreScheduleFrequencySub... Minutes
SourceSqlInstance SQL01
CopyDestinationFolder \SQL02\LogShipping
GenerateFullBackup True
NoRecovery True
CopyScheduleFrequencySubday... 15
DestinationSqlInstance SQL02
BackupNetworkPath \SQL01\LogShipping
Database test_dbtest
BackupLocalPath Z:\LogShipping
CompressBackup True
BackupScheduleFrequencySubd... 15
RestoreScheduleFrequencySub... 15
BackupScheduleFrequencySubd... Minutes
Force True
CopyScheduleFrequencySubday... Minutes

PS D:\Code\dbatoolkit> Invoke-DbaDbLogShipping @params -verbose
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Started log shipping for SQL01 to SQL02
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Connection timeout of [SQL01] is set to 0
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Testing backup network path \SQL01\LogShipping
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Setting backup compression to 1.
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Destination database status set to NO RECOVERY
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Backup retention set to 4320
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Backup Threshold set to 60
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Copy retention set to 4320
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] History retention set to 14420
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Restore alert Threshold set to 45
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Restore delay set to 0
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Restore retention set to 4320
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Restore Threshold set to 0
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Primary monitor server security mode set to 1
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Secondary monitor server security mode set to 1
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Backup frequency type set to Daily
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Backup frequency interval set to EveryDay
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Backup frequency relative interval set to Unused
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Backup frequency recurrence factor set to 0
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Copy frequency type set to Daily
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Copy frequency interval set to EveryDay
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Copy frequency relative interval set to Unused
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Copy frequency recurrence factor set to 0
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Restore frequency type set to Daily
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Restore frequency interval set to EveryDay
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Restore frequency relative interval set to Unused
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Restore frequency recurrence factor set to 0
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Backup start date set to 20190326
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Backup start time set to 000000
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Backup end time set to 235959
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Copy start date set to 20190326
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Copy start time set to 000000
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Copy end time set to 235959
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Restore start date set to 20190326
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Restore start time set to 000000
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Restore end time set to 235959
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Connection timeout of [SQL02] is set to 0
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Testing copy destination path \SQL02\LogShipping
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Backup local path set to Z:\LogShipping\test_dbtest.
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Backup network path set to \SQL01\LogShipping\test_dbtest.
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Testing database backup network path \SQL01\LogShipping\test_dbtest
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Backup job name set to LSBackup_test_dbtest
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Backup job schedule name set to LSBackupSchedule_test_dbtest
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Restore data folder or restore log folder are not set. Using server defaults
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Restore data folder set to E:\Data\MSSQL
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Restore log folder set to E:\Log\MSSQL
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Testing database restore data path E:\Data\MSSQL
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Testing database restore log path E:\Log\MSSQL
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Copy destination folder set to \SQL02\LogShipping\test_dbtest.
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Copy job name set to LSCopy_SQL01_test_dbtest
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Copy job schedule name set to LSCopySchedule_SQL01_test_dbtest
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Testing database copy destination path \SQL02\LogShipping\test_dbtest
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Restore job name set to LSRestore_SQL01_test_dbtest
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Restore job schedule name set to LSRestoreSchedule_SQL01_test_dbtest
VERBOSE: Performing the operation "Backing up database [test_dbtest]" on target "SQL01".
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Generating full backup.
VERBOSE: [09:21:31][Invoke-DbaDbLogShipping] Backing up database [test_dbtest] to \SQL01\LogShipping\test_dbtest
VERBOSE: [09:21:31][Backup-DbaDatabase] Setting Default timestampformat
VERBOSE: [09:21:31][Backup-DbaDatabase] 1 database to backup
VERBOSE: [09:21:31][Backup-DbaDatabase] Backup database [test_dbtest]
VERBOSE: [09:21:31][Backup-DbaDatabase] Creating full backup
VERBOSE: [09:21:31][Backup-DbaDatabase] Building file name
VERBOSE: [09:21:31][Backup-DbaDatabase] Building backup path
VERBOSE: [09:21:31][Backup-DbaDatabase] Devices added
VERBOSE: [09:21:58][Get-DbaBackupHistory] Processing test_dbtest
VERBOSE: [09:21:58][Get-DbaBackupHistory] Executing sql query.
VERBOSE: [09:21:58][Get-DbaBackupHistory] Processing as grouped output.
VERBOSE: [09:21:58][Get-DbaBackupHistory] 1 result-groups found.
VERBOSE: [09:21:58][Invoke-DbaDbLogShipping] Backup completed.
VERBOSE: [09:21:58][Invoke-DbaDbLogShipping] Backup is located at \SQL01\LogShipping\test_dbtest\FullBackup_test_dbtest_PreLogShipping_20190326092131.bak
VERBOSE: [09:21:58][Invoke-DbaDbLogShipping] Setting monitor server for primary server to SQL01.
VERBOSE: [09:21:58][Invoke-DbaDbLogShipping] Setting secondary monitor server for SQL02 to SQL01.
VERBOSE: Performing the operation "Restoring database [test_dbtest] to test_dbtest on SQL02" on target "SQL02".
VERBOSE: [09:21:58][Invoke-DbaDbLogShipping] Start database restore
VERBOSE: [09:21:58][Restore-DbaDatabase] Changing statement timeout to infinity
VERBOSE: [09:21:58][Restore-DbaDatabase] ParameterSet = Restore
VERBOSE: [09:21:58][Restore-DbaDatabase] Unverified input, full scans - \SQL01\LogShipping\test_dbtest\FullBackup_test_dbtest_PreLogShipping_20190326092131.bak
VERBOSE: [09:21:58][Get-DbaBackupInformation] Testing a single file @{Length=100; FullName=\SQL01\LogShipping\test_dbtest\FullBackup_test_dbtest_PreLogShipping_20190326092131.bak}
VERBOSE: [09:21:59][Get-DbaBackupInformation] Reading backup headers of 1 files
VERBOSE: [09:21:59][Read-DbaBackupHeader] 1 unique files to scan.
VERBOSE: [09:21:59][Read-DbaBackupHeader] Checking accessibility for all the files.
VERBOSE: [09:21:59][Read-DbaBackupHeader] Scanning file \SQL01\LogShipping\test_dbtest\FullBackup_test_dbtest_PreLogShipping_20190326092131.bak.
VERBOSE: [09:22:00][Restore-DbaDatabase] Processing DatabaseName - test_dbtest
VERBOSE: [09:22:00][Format-DbaBackupInformation] Starting
VERBOSE: [09:22:00][Format-DbaBackupInformation] String passed in for DB rename
VERBOSE: [09:22:00][Format-DbaBackupInformation] New DbName (String) = test_dbtest
VERBOSE: [09:22:00][Format-DbaBackupInformation] 1 PhysicalName = E:\Data\MSSQL\test_dbtest_data.mdf
VERBOSE: [09:22:00][Format-DbaBackupInformation] PhysicalName = E:\Data\MSSQL\test_dbtest_data.mdf
VERBOSE: [09:22:00][Format-DbaBackupInformation] 1 PhysicalName = E:\HDT\MSSQL\test_dbtest_secondary.ndf
VERBOSE: [09:22:00][Format-DbaBackupInformation] PhysicalName = E:\Data\MSSQL\test_dbtest_secondary.ndf
VERBOSE: [09:22:00][Format-DbaBackupInformation] 1 PhysicalName = E:\FTS\MSSQL\job_search\test_dbtest_ftrow.ndf
VERBOSE: [09:22:00][Format-DbaBackupInformation] PhysicalName = E:\Data\MSSQL\test_dbtest_ftrow.ndf
VERBOSE: [09:22:00][Format-DbaBackupInformation] 1 PhysicalName = E:\Log\MSSQL\test_dbtest_log.ldf
VERBOSE: [09:22:00][Format-DbaBackupInformation] PhysicalName = E:\Log\MSSQL\test_dbtest_log.ldf
VERBOSE: [09:22:00][Select-DbaBackupInformation] Filtering by DatabaseName
VERBOSE: [09:22:00][Select-DbaBackupInformation] processing diffs
VERBOSE: [09:22:00][Select-DbaBackupInformation] Found LogBaseLsn: 1952323000001750400001 and FirstRecoveryForkID:
VERBOSE: [09:22:00][Restore-DbaDatabase] VerifyOnly = False
VERBOSE: [09:22:00][Test-DbaBackupInformation] Testing restore for test_dbtest
VERBOSE: [09:22:00][Test-DbaBackupInformation] VerifyOnly = False
VERBOSE: [09:22:00][Test-DbaLsnChain] Testing LSN Chain
VERBOSE: [09:22:00][Test-DbaLsnChain] Testing LSN Chain - Type Type
VERBOSE: [09:22:00][Test-DbaLsnChain] Passed LSN Chain checks
VERBOSE: [09:22:00][Test-DbaBackupInformation] Marking test_dbtest as verified
VERBOSE: [09:22:00][Restore-DbaDatabase] test_dbtest passed testing
VERBOSE: [09:22:00][Restore-DbaDatabase] Passing in to restore
VERBOSE: [09:22:00][Invoke-DbaAdvancedRestore] Performing restore action
VERBOSE: [09:22:22][Invoke-DbaAdvancedRestore] Succeeded, Closing Server connection
VERBOSE: [09:22:22][Invoke-DbaDbLogShipping] Restore completed.
VERBOSE: Performing the operation "Configuring logshipping for primary database [test_dbtest] on SQL01" on target "SQL01".
VERBOSE: [09:22:23][Invoke-DbaDbLogShipping] Configuring logshipping for primary database
VERBOSE: [09:22:23][New-DbaLogShippingPrimaryDatabase] Setting backup compression to 1.
VERBOSE: [09:22:23][New-DbaLogShippingPrimaryDatabase] Setting Threshold alert to 0.
VERBOSE: [09:22:23][New-DbaLogShippingPrimaryDatabase] Configuring logshipping for primary database test_dbtest.
VERBOSE: [09:22:23][New-DbaLogShippingPrimaryDatabase] Executing query:

    DECLARE @LS_BackupJobId AS uniqueidentifier;
    DECLARE @LS_PrimaryId AS uniqueidentifier;
    EXEC master.sys.sp_add_log_shipping_primary_database
        @database = N'test_dbtest'
        ,@backup_directory = N'Z:\LogShipping\test_dbtest'
        ,@backup_share = N'\\SQL01\LogShipping\test_dbtest'
        ,@backup_job_name = N'LSBackup_test_dbtest'
        ,@backup_retention_period = 4320
        ,@backup_threshold = 60
        ,@history_retention_period = 14420
        ,@backup_job_id = @LS_BackupJobId OUTPUT
        ,@primary_id = @LS_PrimaryId OUTPUT 
        ,@monitor_server = N'SQL01'
        ,@monitor_server_security_mode = 1
        ,@threshold_alert = 14420
        ,@threshold_alert_enabled = 0
        ,@overwrite = 1;

VERBOSE: [09:22:23][New-DbaLogShippingPrimaryDatabase] Finished adding the primary database test_dbtest to log shipping.
VERBOSE: [09:22:23][Set-DbaAgentJob] Setting job to enabled
VERBOSE: [09:22:23][Set-DbaAgentJob] Changing the job
VERBOSE: [09:22:23][Set-DbaAgentJob] Finished changing job(s)
VERBOSE: [09:22:23][Invoke-DbaDbLogShipping] Enabling backup job LSBackup_test_dbtest
VERBOSE: [09:22:23][Invoke-DbaDbLogShipping] Create backup job schedule LSBackupSchedule_test_dbtest
VERBOSE: [09:22:23][New-DbaAgentSchedule] Setting job schedule to enabled
VERBOSE: [09:22:23][New-DbaAgentSchedule] Setting job schedule frequency interval to 1
VERBOSE: [09:22:23][New-DbaAgentSchedule] Setting job schedule frequency to 4
VERBOSE: [09:22:23][New-DbaAgentSchedule] Setting job schedule frequency subday type to 4
VERBOSE: [09:22:23][New-DbaAgentSchedule] Setting job schedule frequency subday interval to 15
VERBOSE: [09:22:23][New-DbaAgentSchedule] Setting job schedule start date to 2019-03-26
VERBOSE: [09:22:23][New-DbaAgentSchedule] Setting job schedule end date to 9999-12-31
VERBOSE: [09:22:23][New-DbaAgentSchedule] Setting job schedule start time to 00:00:00
VERBOSE: [09:22:23][New-DbaAgentSchedule] Setting job schedule end time to 23:59:59
VERBOSE: [09:22:23][New-DbaAgentSchedule] Adding the schedule LSBackupSchedule_test_dbtest to job LSBackup_test_dbtest
VERBOSE: [09:22:23][New-DbaAgentSchedule] Job schedule created with UID ca0f4c23-9032-4964-a250-c0c414f9f837
VERBOSE: [09:22:23][New-DbaAgentSchedule] Finished creating job schedule(s).
VERBOSE: [09:22:23][Invoke-DbaDbLogShipping] Configuring logshipping from primary to secondary database.
VERBOSE: [09:22:23][New-DbaLogShippingPrimarySecondary] Executing query:

    SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases WHERE primary_database = 'test_dbtest'

VERBOSE: [09:22:23][New-DbaLogShippingPrimarySecondary] Configuring logshipping connecting the primary database test_dbtest to secondary database test_dbtest on SQL01.
VERBOSE: [09:22:23][New-DbaLogShippingPrimarySecondary] Executing query:

    EXEC master.sys.sp_add_log_shipping_primary_secondary
    @primary_database = N'test_dbtest'
    ,@secondary_server = N'SQL02'
    ,@secondary_database = N'test_dbtest' ,@overwrite = 1;

VERBOSE: [09:22:23][New-DbaLogShippingPrimarySecondary] Finished configuring of primary database test_dbtest to secondary database test_dbtest.
VERBOSE: Performing the operation "Configuring logshipping for secondary database test_dbtest on SQL02" on target "SQL02".
VERBOSE: [09:22:23][Invoke-DbaDbLogShipping] Configuring logshipping from secondary database test_dbtest to primary database [test_dbtest].
VERBOSE: [09:22:23][New-DbaLogShippingSecondaryPrimary] Configuring logshipping making settings for the primary database.
VERBOSE: [09:22:23][New-DbaLogShippingSecondaryPrimary] Executing query:

    DECLARE @LS_Secondary__CopyJobId AS uniqueidentifier
    DECLARE @LS_Secondary__RestoreJobId     AS uniqueidentifier
    DECLARE @LS_Secondary__SecondaryId AS uniqueidentifier
    EXEC master.sys.sp_add_log_shipping_secondary_primary
            @primary_server = N'SQL01'
            ,@primary_database = N'test_dbtest'
            ,@backup_source_directory = N'\\SQL01\LogShipping\test_dbtest'
            ,@backup_destination_directory = N'\\SQL02\LogShipping\test_dbtest'
            ,@copy_job_name = N'LSCopy_SQL01_test_dbtest'
            ,@restore_job_name = N'LSRestore_SQL01_test_dbtest'
            ,@file_retention_period = 4320
            ,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT
            ,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT
            ,@secondary_id = @LS_Secondary__SecondaryId OUTPUT ,@monitor_server = N'SQL01'
            ,@monitor_server_security_mode = 1 ,@overwrite = 1;

VERBOSE: [09:22:24][New-DbaLogShippingSecondaryPrimary] Finished configuring of secondary database to primary database test_dbtest.
VERBOSE: [09:22:24][Invoke-DbaDbLogShipping] Create copy job schedule LSCopySchedule_SQL01_test_dbtest
VERBOSE: [09:22:24][New-DbaAgentSchedule] Setting job schedule to enabled
VERBOSE: [09:22:24][New-DbaAgentSchedule] Setting job schedule frequency interval to 1
VERBOSE: [09:22:24][New-DbaAgentSchedule] Setting job schedule frequency to 4
VERBOSE: [09:22:24][New-DbaAgentSchedule] Setting job schedule frequency subday type to 4
VERBOSE: [09:22:24][New-DbaAgentSchedule] Setting job schedule frequency subday interval to 15
VERBOSE: [09:22:24][New-DbaAgentSchedule] Setting job schedule start date to 2019-03-26
VERBOSE: [09:22:24][New-DbaAgentSchedule] Setting job schedule end date to 9999-12-31
VERBOSE: [09:22:24][New-DbaAgentSchedule] Setting job schedule start time to 00:00:00
VERBOSE: [09:22:24][New-DbaAgentSchedule] Setting job schedule end time to 23:59:59
VERBOSE: [09:22:24][New-DbaAgentSchedule] Adding the schedule LSCopySchedule_SQL01_test_dbtest to job LSCopy_SQL01_test_dbtest
VERBOSE: [09:22:24][New-DbaAgentSchedule] Job schedule created with UID c6fc4893-5460-4f90-8029-aaaf405475be
VERBOSE: [09:22:24][New-DbaAgentSchedule] Finished creating job schedule(s).
VERBOSE: [09:22:24][Invoke-DbaDbLogShipping] Create restore job schedule LSRestoreSchedule_SQL01_test_dbtest
VERBOSE: [09:22:24][New-DbaAgentSchedule] Setting job schedule to enabled
VERBOSE: [09:22:24][New-DbaAgentSchedule] Setting job schedule frequency interval to 1
VERBOSE: [09:22:24][New-DbaAgentSchedule] Setting job schedule frequency to 4
VERBOSE: [09:22:24][New-DbaAgentSchedule] Setting job schedule frequency subday type to 4
VERBOSE: [09:22:24][New-DbaAgentSchedule] Setting job schedule frequency subday interval to 15
VERBOSE: [09:22:24][New-DbaAgentSchedule] Setting job schedule start date to 2019-03-26
VERBOSE: [09:22:24][New-DbaAgentSchedule] Setting job schedule end date to 9999-12-31
VERBOSE: [09:22:24][New-DbaAgentSchedule] Setting job schedule start time to 00:00:00
VERBOSE: [09:22:24][New-DbaAgentSchedule] Setting job schedule end time to 23:59:59
VERBOSE: [09:22:24][New-DbaAgentSchedule] Adding the schedule LSRestoreSchedule_SQL01_test_dbtest to job LSRestore_SQL01_test_dbtest
VERBOSE: [09:22:24][New-DbaAgentSchedule] Job schedule created with UID e8ee8064-6955-4c80-ab8e-6b4209b8de18
VERBOSE: [09:22:24][New-DbaAgentSchedule] Finished creating job schedule(s).
VERBOSE: [09:22:24][Invoke-DbaDbLogShipping] Configuring logshipping for secondary database.
VERBOSE: [09:22:24][New-DbaLogShippingSecondaryDatabase] Setting Threshold alert to 0.
VERBOSE: [09:22:24][New-DbaLogShippingSecondaryDatabase] Setting disconnect users to 0.
VERBOSE: [09:22:24][New-DbaLogShippingSecondaryDatabase] Configuring logshipping for secondary database test_dbtest on SQL02.
VERBOSE: [09:22:24][New-DbaLogShippingSecondaryDatabase] Executing query:

    EXEC master.sys.sp_add_log_shipping_secondary_database
    @secondary_database = 'test_dbtest'
    ,@primary_server = 'SQL01'
    ,@primary_database = 'test_dbtest'
    ,@restore_delay = 0
    ,@restore_all = 1
    ,@restore_mode = 0
    ,@disconnect_users = 0
    ,@restore_threshold = 0
    ,@threshold_alert = 14420
    ,@threshold_alert_enabled = 0
    ,@history_retention_period = 14420 ;

VERBOSE: [09:22:24][New-DbaLogShippingSecondaryDatabase] Configuring monitor server for secondary database test_dbtest.
VERBOSE: [09:22:24][New-DbaLogShippingSecondaryDatabase] Executing query:

    EXEC msdb.dbo.sp_processlogshippingmonitorsecondary @mode = 1
                ,@secondary_server = 'SQL02'
                ,@secondary_database = 'test_dbtest'
                ,@secondary_id = ''
                ,@primary_server = ''
                ,@primary_database = ''
                ,@restore_threshold = 
                ,@threshold_alert = 0
                ,@threshold_alert_enabled = 0
                ,@history_retention_period = 0
                ,@monitor_server = 'SQL01'
                ,@monitor_server_security_mode = 1

WARNING: [09:22:24][Invoke-DbaQuery] [SQL01] Failed during execution | Incorrect syntax near ','.
VERBOSE: [09:22:24][New-DbaLogShippingSecondaryDatabase] Updating monitor information for the secondary database test_dbtest.
VERBOSE: [09:22:24][New-DbaLogShippingSecondaryDatabase] Executing query:

            UPDATE msdb.dbo.log_shipping_secondary
            SET monitor_server = 'SQL01', user_specified_monitor = 1
            WHERE secondary_id = ''

WARNING: [09:22:24][New-DbaLogShippingSecondaryDatabase] Conversion failed when converting from a character string to uniqueidentifier.
WARNING: [09:22:24][New-DbaLogShippingSecondaryDatabase] Error executing the query.
Exception calling "query" with "1" argument(s): "Exception calling "ExecuteWithResults" with "1" argument(s): "Execute with results failed for Database 'master'. ""

            UPDATE msdb.dbo.log_shipping_secondary
            SET monitor_server = 'SQL01', user_specified_monitor = 1
            WHERE secondary_id = ''
             | Conversion failed when converting from a character string to uniqueidentifier.

VERBOSE: [09:22:24][Invoke-DbaDbLogShipping] Finished setting up log shipping.`

garyhampson · 2019-03-26T14:58:58Z

Sorry, hit the wrong button above.

Changed assignment of variables in sp_processlogshippingmonitorsecondary Fixes #5308

sanderstad · 2019-04-16T08:18:25Z

I changed the way the internal command handles the setting of the monitor server.

@garyhampson Can you test the change using the development version of dbatools and the branch "fix-logshipping" in particular.

potatoqualitee · 2019-06-07T20:09:02Z

thanks @sanderstad - will close as assumed fixed

potatoqualitee · 2019-06-17T09:20:30Z

@garyhampson you there? Can you please let us know your locale as well? like en-us.

potatoqualitee · 2019-06-17T12:09:22Z

if it was a locale issue, the fix is included in #5753 which will be released with dbatools 1.0 on Thursday morning 💯

pgriffith1 · 2020-07-30T16:51:51Z

I think this problem is still open.

`PowerShell version:
Name Value

PSVersion 5.1.18362.752
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.18362.752
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

dbatools Module version:
Name : dbatools
Path : C:\Program Files\WindowsPowerShell\Modules\dbatools\dbatools.psd1
Version : 1.0.115 `

Microsoft SQL Server 2016 (SP2-CU13) (KB4549825) - 13.0.5820.21 (X64) May 22 2020 07:24:35 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

`WARNING: [11:37:47][Invoke-DbaQuery] [hostname\tfs2_d] Failed during execution | Incorrect syntax near ','.
WARNING: [11:37:48][New-DbaLogShippingSecondaryDatabase] Conversion failed when converting from a character string to uniqueidentifier
.
WARNING: [11:37:48][New-DbaLogShippingSecondaryDatabase] Error executing the query.
Exception calling "Query" with "1" argument(s): "Exception calling "ExecuteWithResults" with "1" argument(s): "Execute with results failed for Database 'master'. ""

            UPDATE msdb.dbo.log_shipping_secondary
            SET monitor_server = 'hostname\tfs2_d', user_specified_monitor = 1
            WHERE secondary_id = ''
             | Conversion failed when converting from a character string to uniqueidentifier.`

`Get-WinSystemLocale

LCID Name DisplayName

1033 en-US English (United States) `

I'm pathetically bad at Markdown styling, sorry.

sanderstad · 2020-07-30T19:36:46Z

I will take a look at it

sanderstad · 2020-08-06T10:30:06Z

@pgriffith1 I have made a change after looking through a lot of code.
Can you extract the attached zip package and replace it in the following directory:
<dbatopols powershell module folder>\internal\functions

I believe this should solve the problem

New-DbaLogShippingSecondaryDatabase.zip

github-actions · 2020-11-05T06:09:57Z

🚧🚨 This issue is being marked as stale due to 90 days of inactivity. If you would like this issue to remain open:

Verify the issue/bug is reproduced in the latest version of the module
Verify the environmental info provided is still accurate
Add any additional steps you followed to reproduce if necessary
🚨🚧
⌛️ This issue will be closed in 30 days ⌛️

andreasjordan · 2021-05-25T16:40:23Z

@pgriffith1 Are you still around? Please leave a message.

andreasjordan · 2021-06-04T22:20:33Z

I will close this for now, but you can reopen it if you have time to work on this.

potatoqualitee · 2021-06-22T11:53:02Z

I'll ask @sanderstad if he can turn his change into a PR

potatoqualitee · 2021-06-22T12:10:23Z

Will auto-close once the PR has been merged 🥳 Thanks everyone for your followups.

pgriffith1 · 2021-06-22T21:16:42Z

I apologize for not being more helpful. I've not had the opportunity to work with log shipping recently.

…

On Tue, Jun 22, 2021 at 8:10 AM Chrissy LeMaire ***@***.***> wrote: Will auto-close once the PR has been merged 🥳 Thanks everyone for your followups. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#5308 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACJ4LNH3G577ZXQH6VOFUNDTUB4T7ANCNFSM4HBN7Z2A> .

potatoqualitee · 2021-06-23T06:27:01Z

No problem, @pgriffith1 ! thanks for filing the issue and letting us know.

potatoqualitee added the bugs life label Mar 26, 2019

potatoqualitee assigned sanderstad Mar 26, 2019

garyhampson closed this as completed Mar 26, 2019

garyhampson reopened this Mar 26, 2019

sanderstad added a commit that referenced this issue Apr 16, 2019

Changed message to user

3a1965e

Changed assignment of variables in sp_processlogshippingmonitorsecondary Fixes #5308

potatoqualitee closed this as completed Jun 7, 2019

potatoqualitee mentioned this issue Jun 17, 2019

Emergency Issue: Backup-DbaDatabase conversion problem #5749

Closed

GeorgePalacios mentioned this issue Jun 17, 2019

Fix backup dbadatabase dateconversion #5753

Merged

10 tasks

jarnoj mentioned this issue Apr 1, 2020

[Bug] Backup-DbaDatabase | Conversion failed #6452

Closed

4 tasks

sanderstad reopened this Jul 30, 2020

github-actions bot added the stale Stale Bot label used to identify issues that are no longer active and bot has closed them label Nov 5, 2020

andreasjordan added the pending OP feedback Waiting for feedback from the OP of the issue label May 25, 2021

github-actions bot removed the stale Stale Bot label used to identify issues that are no longer active and bot has closed them label May 26, 2021

andreasjordan closed this as completed Jun 4, 2021

sanderstad mentioned this issue Jun 22, 2021

Invoke-DbaDbLogShipping - Fixed restore threshold parameter not being used #7457

Merged

10 tasks

potatoqualitee reopened this Jun 22, 2021

potatoqualitee closed this as completed in #7457 Jun 23, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Invoke-DbaDbLogShipping: @restore_threshold not being passed when configuring monitor server causing syntax error #5308

Invoke-DbaDbLogShipping: @restore_threshold not being passed when configuring monitor server causing syntax error #5308

garyhampson commented Mar 26, 2019 •

edited by wsmelton

Loading

garyhampson commented Mar 26, 2019

garyhampson commented Mar 26, 2019

sanderstad commented Apr 16, 2019

potatoqualitee commented Jun 7, 2019

potatoqualitee commented Jun 17, 2019

potatoqualitee commented Jun 17, 2019

pgriffith1 commented Jul 30, 2020

sanderstad commented Jul 30, 2020

sanderstad commented Aug 6, 2020

github-actions bot commented Nov 5, 2020

andreasjordan commented May 25, 2021

andreasjordan commented Jun 4, 2021

potatoqualitee commented Jun 22, 2021

potatoqualitee commented Jun 22, 2021

pgriffith1 commented Jun 22, 2021 via email

potatoqualitee commented Jun 23, 2021

Invoke-DbaDbLogShipping: @restore_threshold not being passed when configuring monitor server causing syntax error #5308

Invoke-DbaDbLogShipping: @restore_threshold not being passed when configuring monitor server causing syntax error #5308

Comments

garyhampson commented Mar 26, 2019 • edited by wsmelton Loading

Before submitting a bug report:

Environmental data

SQL Server:

Steps to Reproduce

Expected Behavior

Actual Behavior

garyhampson commented Mar 26, 2019

garyhampson commented Mar 26, 2019

sanderstad commented Apr 16, 2019

potatoqualitee commented Jun 7, 2019

potatoqualitee commented Jun 17, 2019

potatoqualitee commented Jun 17, 2019

pgriffith1 commented Jul 30, 2020

sanderstad commented Jul 30, 2020

sanderstad commented Aug 6, 2020

github-actions bot commented Nov 5, 2020

andreasjordan commented May 25, 2021

andreasjordan commented Jun 4, 2021

potatoqualitee commented Jun 22, 2021

potatoqualitee commented Jun 22, 2021

pgriffith1 commented Jun 22, 2021 via email

potatoqualitee commented Jun 23, 2021

garyhampson commented Mar 26, 2019 •

edited by wsmelton

Loading