commands

^

Copy-DbaSystemDbUserObject

Author Chrissy LeMaire (@cl), netnerds.net
Availability Windows, Linux, macOS

 

Aliases : Copy-DbaSysDbUserObject

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

Synopsis

Copies user-created objects from system databases (master, msdb, model) between SQL Server instances.

Description

Migrates custom database objects that DBAs commonly store in system databases like maintenance procedures, monitoring tables, custom triggers, and backup utilities from master and msdb. Also transfers objects from the model database that will be included in new databases created on the destination instance.

This function handles schemas, tables, views, stored procedures, functions, triggers, and other user-defined objects while preserving dependencies and permissions. It's particularly valuable during server migrations or when standardizing DBA tooling across multiple instances.

Syntax

Copy-DbaSystemDbUserObject
    [-Source] <DbaInstanceParameter>
    [[-SourceSqlCredential] <PSCredential>]
    [-Destination] <DbaInstanceParameter[]>
    [[-DestinationSqlCredential] <PSCredential>]
    [-Force]
    [-Classic]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> Copy-DbaSystemDbUserObject -Source sqlserver2014a -Destination sqlcluster

Copies user objects found in system databases master, msdb and model from sqlserver2014a instance to the sqlcluster instance.

Required Parameters

-Source

Specifies the source SQL Server instance containing the user objects to copy from system databases.
Requires sysadmin permissions to access master, msdb, and model databases for object extraction.

Alias
Required True
Pipeline false
Default Value
-Destination

Specifies one or more destination SQL Server instances where the user objects will be copied to.
Requires sysadmin permissions to modify master, msdb, and model databases on the destination servers.

Alias
Required True
Pipeline false
Default Value

Optional Parameters

-SourceSqlCredential

Specifies credentials for connecting to the source SQL Server instance when Windows Authentication is not available.
Required when accessing source instances across domains or using SQL Server Authentication.

Alias
Required False
Pipeline false
Default Value
-DestinationSqlCredential

Specifies credentials for connecting to destination SQL Server instances when Windows Authentication is not available.
Required when accessing destination instances across domains or using SQL Server Authentication.

Alias
Required False
Pipeline false
Default Value
-Force

Drops existing objects on destination instances before creating new ones to resolve naming conflicts.
Only works with the default modern method, not with Classic mode, and may fail with objects that have dependencies.

Alias
Required False
Pipeline false
Default Value False
-Classic

Uses the legacy migration method that copies all object types in bulk using SQL Server Management Objects Transfer class.
The default modern method provides better error handling and granular object control but this option may resolve compatibility issues with older SQL Server versions.

Alias
Required False
Pipeline false
Default Value False
-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.

Alias
Required False
Pipeline false
Default Value False
-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.

Alias wi
Required False
Pipeline false
Default Value
-Confirm

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

Alias cf
Required False
Pipeline false
Default Value