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.

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

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.

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

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

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

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