Skip to content

Invoke-DbaDbLogShipping command is failing on SQL 2008 R2 #9657

Open
@ant24x7

Description

@ant24x7

Verified issue does not already exist?

No, I did not search

What error did you receive?

`VERBOSE: [16:03:08][New-DbaLogShippingPrimaryDatabase] Setting backup compression to 1.
VERBOSE: [16:03:08][New-DbaLogShippingPrimaryDatabase] Setting Threshold alert to 1.
VERBOSE: [16:03:08][New-DbaLogShippingPrimaryDatabase] Configuring logshipping for primary database EPOS.
VERBOSE: [16:03:08][New-DbaLogShippingPrimaryDatabase] Executing query:

        DECLARE @LS_BackupJobId AS uniqueidentifier
            ,@LS_PrimaryId AS uniqueidentifier
            ,@SP_Add_RetCode AS INT;
        EXEC @SP_Add_RetCode = master.sys.sp_add_log_shipping_primary_database
            @database = N'YY'
            ,@backup_directory = N'G:\logshipping\YY'
            ,@backup_share = N'\\XXX\Logshipping\YY'
            ,@backup_job_name = N'LSBackup_YY'
            ,@backup_retention_period = 4320
            ,@backup_threshold = 60
            ,@history_retention_period = 14420
            ,@backup_job_id = @LS_BackupJobId OUTPUT
            ,@primary_id = @LS_PrimaryId OUTPUT ,@backup_compression = 1,@threshold_alert = 14420
            ,@threshold_alert_enabled = 1,@overwrite = 1;
    IF (@SP_Add_RetCode <> 0)
    BEGIN
        DECLARE @msg VARCHAR(1000);
        SELECT @msg = 'Unexpected result executing sp_add_log_shipping_primary_database ('
            + CAST (@SP_Add_RetCode AS VARCHAR(5)) + ').';
        THROW 51000, @msg, 1;
    END
WARNING: [16:03:08][New-DbaLogShippingPrimaryDatabase] 
WARNING: [16:03:08][New-DbaLogShippingPrimaryDatabase] Error executing the query.
Exception calling "Query" with "1" argument(s): "An exception occurred while executing a Transact-SQL statement or batch.Incorrect syntax near 'THROW'."

        DECLARE @LS_BackupJobId AS uniqueidentifier
            ,@LS_PrimaryId AS uniqueidentifier
            ,@SP_Add_RetCode AS INT;
        EXEC @SP_Add_RetCode = master.sys.sp_add_log_shipping_primary_database
            @database = N'YY'
            ,@backup_directory = N'G:\logshipping\YY'
            ,@backup_share = N'\\XXX\Logshipping\YY'
            ,@backup_job_name = N'LSBackup_YY'
            ,@backup_retention_period = 4320
            ,@backup_threshold = 60
            ,@history_retention_period = 14420
            ,@backup_job_id = @LS_BackupJobId OUTPUT
            ,@primary_id = @LS_PrimaryId OUTPUT ,@backup_compression = 1,@threshold_alert = 14420
            ,@threshold_alert_enabled = 1,@overwrite = 1;
    IF (@SP_Add_RetCode <> 0)
    BEGIN
        DECLARE @msg VARCHAR(1000);
        SELECT @msg = 'Unexpected result executing sp_add_log_shipping_primary_database ('
            + CAST (@SP_Add_RetCode AS VARCHAR(5)) + ').';
        THROW 51000, @msg, 1;
    END
VERBOSE: [16:03:08][Invoke-DbaDbLogShipping] Finished setting up log shipping.`

Steps to Reproduce

# provide your command(s) executed pertaining to dbatools
# please include variable values (redacted or fake if needed) for reference
$params = @{
SourceSqlInstance = 'A'
DestinationSqlInstance = 'B
Database = 'YY'
SharedPath= '\\A\Logshipping'
LocalPath= 'G:\logshipping'
BackupScheduleFrequencyType = 'daily'
BackupScheduleFrequencyInterval = 1
CompressBackup = $true
CopyScheduleFrequencyType = 'daily'
CopyScheduleFrequencyInterval = 1
CopyScheduleFrequencySubdayType = 'Minutes'
CopyScheduleFrequencySubdayInterval = 15
BackupScheduleFrequencySubdayType = 'Minutes'
BackupScheduleFrequencySubdayInterval = 15
GenerateFullBackup = $true
RestoreScheduleFrequencyType = 'daily'
RestoreScheduleFrequencyInterval = 1
RestoreScheduleFrequencySubdayType = 'Minutes'
RestoreScheduleFrequencySubdayInterval = 15
CopyDestinationFolder = '\\B\Logshipping'
PrimaryThresholdAlertEnabled = $true
SecondaryThresholdAlertEnabled = $true
}
Invoke-DbaDbLogShipping @params -Verbose

Please confirm that you are running the most recent version of dbatools

2.1.30

Other details or mentions

Post review of the function New-DbaLogShippingPrimaryDatabase
It looks like THROW is being used which is not available for SQL Server 2008 R2

What PowerShell host was used when producing this error

Windows PowerShell ISE (powershell_ise.exe)

PowerShell Host Version

5.1.20348.2849

SQL Server Edition and Build number

Primary Server --> SQL Server 2008 R2
Secondary Server --> SQL Server 2019

.NET Framework Version

PSChildName Version


Client 4.8.04161
Full 4.8.04161
Client 4.0.0.0

Metadata

Metadata

Assignees

Labels

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions