Copy-DbaExtendedStoredProcedure

Synopsis

Copies custom Extended Stored Procedures (XPs) and their associated DLL files between SQL Server instances

Description

Migrates custom Extended Stored Procedures from the source server to one or more destination servers. Extended Stored Procedures are DLL-based procedures that extend SQL Server functionality by calling external code, commonly used for custom server operations, legacy integrations, or specialized processing tasks.

This function identifies custom Extended Stored Procedures (excludes system XPs), copies their definitions to the destination, and attempts to copy the associated DLL files to the destination server’s Binn directory. Due to OS and .NET version differences, DLLs may require recompilation when migrating between different Windows versions or SQL Server versions.

By default, all custom Extended Stored Procedures are copied. Use -ExtendedProcedure to copy specific procedures or -ExcludeExtendedProcedure to skip certain ones. Existing procedures on the destination are skipped unless -Force is used to overwrite them.

WARNING: DLL files may not be compatible between different OS versions (e.g., Windows Server 2012 R2 to Windows Server 2019) due to .NET framework differences. The function will attempt to copy DLL files but will warn if the copy fails, allowing for manual intervention.

Syntax

Copy-DbaExtendedStoredProcedure
    [-Source] <DbaInstanceParameter>
    [[-SourceSqlCredential] <PSCredential>]
    [-Destination] <DbaInstanceParameter[]>
    [[-DestinationSqlCredential] <PSCredential>]
    [[-ExtendedProcedure] <String[]>]
    [[-ExcludeExtendedProcedure] <String[]>]
    [[-DestinationPath] <String>]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Copy-DbaExtendedStoredProcedure -Source sqlserver2014a -Destination sqlcluster

Copies all custom Extended Stored Procedures from sqlserver2014a to sqlcluster using Windows credentials. If procedures with the same name exist on sqlcluster, they will be skipped. Attempts to copy
associated DLL files.

Example: 2

PS C:\> Copy-DbaExtendedStoredProcedure -Source sqlserver2014a -SourceSqlCredential $scred -Destination sqlcluster -DestinationSqlCredential $dcred -ExtendedProcedure xp_custom_proc -Force

Copies only the Extended Stored Procedure xp_custom_proc from sqlserver2014a to sqlcluster using SQL credentials. If the procedure already exists on sqlcluster, it will be updated because -Force was
used.

Example: 3

PS C:\> Copy-DbaExtendedStoredProcedure -Source sqlserver2014a -Destination sqlcluster -ExcludeExtendedProcedure xp_old_proc -Force

Copies all custom Extended Stored Procedures found on sqlserver2014a except xp_old_proc to sqlcluster. If procedures with the same name exist on sqlcluster, they will be updated because -Force was
used.

Example: 4

PS C:\> Copy-DbaExtendedStoredProcedure -Source sqlserver2014a -Destination sqlcluster -WhatIf -Force

Shows what would happen if the command were executed using force.

Example: 5

PS C:\> Copy-DbaExtendedStoredProcedure -Source sqlserver2014a -Destination sqlcluster -DestinationPath "C:\CustomPath"

Copies all custom Extended Stored Procedures and attempts to copy DLL files to C:\CustomPath on the destination server instead of the default Binn directory.

Required Parameters

-Source

The source SQL Server instance containing Extended Stored Procedures to copy. Requires sysadmin access to read procedure definitions and access to DLL files in the Binn directory.
Use this to specify which server has the Extended Stored Procedures you want to migrate or standardize across your environment.

-Destination

The destination SQL Server instance(s) where Extended Stored Procedures will be copied. Requires sysadmin access to create procedures and file system access to copy DLL files.
Accepts multiple destinations to deploy Extended Stored Procedures across several servers simultaneously for standardization.

Optional Parameters

-SourceSqlCredential

Credentials for connecting to the source SQL Server instance when Windows authentication is not available or desired.
Use this when you need to connect with specific SQL login credentials or when running under a service account that lacks access to the source server.

-DestinationSqlCredential

Credentials for connecting to the destination SQL Server instance(s) when Windows authentication is not available or desired.
Use this when deploying to servers that require different authentication credentials or when your current context lacks destination access.

-ExtendedProcedure

Specifies which Extended Stored Procedures to copy from the source server instead of copying all available custom XPs.
Use this when you only need specific procedures migrated, such as copying just certain legacy integrations while leaving others behind.

-ExcludeExtendedProcedure

Specifies which Extended Stored Procedures to skip during the copy operation while processing all others from the source.
Use this when most Extended Stored Procedures should be copied but specific ones need to remain server-specific or are problematic.

-DestinationPath

Specifies the destination path where DLL files should be copied. By default, uses the destination SQL Server’s Binn directory.
Use this when you need to copy DLLs to a non-standard location or when the destination Binn directory is not accessible.

-Force

Overwrites existing Extended Stored Procedures on the destination server instead of skipping them when name conflicts occur.
Use this when updating existing procedures with newer versions or when you need to ensure destination procedures match the source exactly.

-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.