Copy-DbaAgentJobStep

Synopsis

Copies job steps from one SQL Server Agent job to another, preserving job history by synchronizing steps without dropping the job itself.

Description

Synchronizes SQL Server Agent job steps between instances by copying step definitions from source jobs to destination jobs. Unlike Copy-DbaAgentJob with -Force, this command preserves job execution history because it only drops and recreates individual steps rather than the entire job. This is essential for maintaining historical job execution data in Always On Availability Group scenarios, disaster recovery environments, or when deploying step modifications across multiple servers.

The function removes all existing steps from the destination job before copying source steps, ensuring a clean synchronization. Job metadata like ownership, schedules, and alerts remain unchanged on the destination.

Syntax

Copy-DbaAgentJobStep
    [[-Source] <DbaInstanceParameter>]
    [[-SourceSqlCredential] <PSCredential>]
    [-Destination] <DbaInstanceParameter[]>
    [[-DestinationSqlCredential] <PSCredential>]
    [[-Job] <Object[]>]
    [[-ExcludeJob] <Object[]>]
    [[-Step] <String[]>]
    [[-InputObject] <Job[]>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Copy-DbaAgentJobStep -Source PrimaryAG -Destination SecondaryAG1, SecondaryAG2 -Job "MaintenanceJob"

Copies all job steps from the “MaintenanceJob” on PrimaryAG to the same job on SecondaryAG1 and SecondaryAG2, preserving job history on the destination servers.

Example: 2

PS C:\> Get-DbaAgentJob -SqlInstance PrimaryAG -Job "BackupJob" | Copy-DbaAgentJobStep -Destination SecondaryAG1

Retrieves the BackupJob from PrimaryAG and synchronizes its steps to the same job on SecondaryAG1 using pipeline input.

Example: 3

PS C:\> Copy-DbaAgentJobStep -Source sqlserver2014a -Destination sqlcluster -Job "DataETL" -SourceSqlCredential $cred

Copies job steps for the “DataETL” job from sqlserver2014a to sqlcluster, using SQL credentials for the source server and Windows credentials for the destination.

Example: 4

PS C:\> Copy-DbaAgentJobStep -Source Primary -Destination Replica1, Replica2, Replica3

Synchronizes all job steps from Primary to multiple AG replicas, ensuring all replicas have identical job step definitions while preserving their individual job execution histories.

Required Parameters

-Destination

Destination SQL Server instance(s) where job steps will be synchronized. You must have sysadmin access and the server must be SQL Server 2000 or higher.
Supports multiple destinations to copy job steps to multiple servers simultaneously, such as syncing all AG replicas or DR servers.

Optional Parameters

-Source

Source SQL Server instance containing the jobs with steps to copy. You must have sysadmin access and server version must be SQL Server 2000 or higher.
Use this when copying job steps from a specific instance rather than piping job objects with InputObject.

-SourceSqlCredential

Alternative credentials for connecting to the source SQL Server instance. Accepts PowerShell credentials (Get-Credential).
Use this when the source server requires different authentication than your current Windows session, such as SQL authentication or cross-domain scenarios.
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.

-DestinationSqlCredential

Alternative credentials for connecting to the destination SQL Server instance. Accepts PowerShell credentials (Get-Credential).
Use this when the destination server requires different authentication than your current Windows session, such as SQL authentication or cross-domain scenarios.
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.

-Job

Specifies which SQL Agent jobs to process by name. Accepts wildcards and multiple job names.
Use this to synchronize steps for specific jobs, such as copying modified steps from a primary AG replica to secondary replicas.
If unspecified, all jobs will be processed.

-ExcludeJob

Specifies which SQL Agent jobs to skip during the copy operation. Accepts wildcards and multiple job names.
Use this to exclude specific jobs from bulk operations, such as skipping environment-specific jobs that shouldn’t be synchronized.

-Step

Specifies which job steps to copy by name. If not specified, all steps are copied.
Use this to synchronize specific steps rather than all steps from a job.

-InputObject

Accepts SQL Agent job objects from the pipeline, typically from Get-DbaAgentJob.
Use this to copy steps for pre-filtered jobs or when combining with other job management cmdlets for complex workflows.

-EnableException

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

-WhatIf

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

-Confirm

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