Copy-DbaDbAssembly

Want to see the source code for this command? Check out Copy-DbaDbAssembly on GitHub.
Want to see the Bill Of Health for this command? Check out Copy-DbaDbAssembly.

Synopsis

Copies CLR assemblies from source databases to destination SQL Server instances

Description

Migrates custom CLR assemblies from databases on a source SQL Server to corresponding databases on destination instances. This function scans all accessible databases for user-created assemblies and recreates them on the target servers, automatically handling security requirements like setting the TRUSTWORTHY property for external assemblies.

Designed for database migration scenarios where applications rely on custom .NET assemblies registered in SQL Server. If assemblies already exist on the destination, they're skipped unless you use -Force to drop and recreate them.

The function does not copy assembly dependencies or dependent objects like CLR stored procedures, functions, or user-defined types that reference the assemblies.

Syntax

Copy-DbaDbAssembly
    [-Source] <DbaInstanceParameter>
    [[-SourceSqlCredential] <PSCredential>]
    [-Destination] <DbaInstanceParameter[]>
    [[-DestinationSqlCredential] <PSCredential>]
    [[-Assembly] <Object[]>]
    [[-ExcludeAssembly] <Object[]>]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

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

Copies all assemblies from sqlserver2014a to sqlcluster using Windows credentials. If assemblies with the same name exist on sqlcluster, they will be skipped.

Example: 2

PS C:\> Copy-DbaDbAssembly -Source sqlserver2014a -Destination sqlcluster -Assembly dbname.assemblyname, dbname3.anotherassembly -SourceSqlCredential $cred -Force

Copies two assemblies, the dbname.assemblyname and dbname3.anotherassembly from sqlserver2014a to sqlcluster using SQL credentials for sqlserver2014a and Windows credentials for sqlcluster. If an
assembly with the same name exists on sqlcluster, it will be dropped and recreated because -Force was used.
In this example, anotherassembly will be copied to the dbname3 database on the server sqlcluster.

Example: 3

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

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

Required Parameters

-Source

Source SQL Server instance containing the CLR assemblies to copy. Requires sysadmin access to scan all accessible databases for user-created assemblies.
The function will inventory all custom assemblies across every database on this instance for migration.

-Destination

Target SQL Server instance(s) where CLR assemblies will be created. Accepts multiple destinations to copy assemblies to several servers simultaneously.
Requires sysadmin access and corresponding databases must already exist on the destination for assembly migration to succeed.

Optional Parameters

-SourceSqlCredential

Alternative credentials for connecting to the source SQL Server instance. Use this when your current Windows credentials don't have sysadmin access to the source server.
Accepts PowerShell credential objects created with Get-Credential and supports SQL Server Authentication or Active Directory authentication methods.

-DestinationSqlCredential

Alternative credentials for connecting to the destination SQL Server instance(s). Use this when your current Windows credentials don't have sysadmin access to the target servers.
Accepts PowerShell credential objects created with Get-Credential and supports SQL Server Authentication or Active Directory authentication methods.

-Assembly

Specific CLR assemblies to copy instead of migrating all assemblies. Use the format 'DatabaseName.AssemblyName' to target assemblies in specific databases.
This is useful when you only need to migrate certain assemblies rather than performing a full assembly migration across all databases.

-ExcludeAssembly

CLR assemblies to skip during the migration process. Use the format 'DatabaseName.AssemblyName' to exclude specific assemblies from specific databases.
This is helpful when you want to migrate most assemblies but need to skip problematic or obsolete ones that shouldn't be copied to the destination.

-Force

Drops existing assemblies on the destination before recreating them from the source. By default, assemblies that already exist are skipped.
Use this when you need to overwrite destination assemblies with updated versions from the source, but be aware that assemblies with dependencies cannot be dropped.

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