Copy-DbaDbMail

Synopsis

Copies Database Mail configuration including profiles, accounts, mail servers and settings between SQL Server instances.

Description

Migrates the complete Database Mail setup from a source SQL Server to one or more destination servers. This includes mail profiles (which group accounts for specific purposes), mail accounts (SMTP configurations), mail servers (SMTP server details and credentials), and global configuration values like account retry attempts and maximum file size.

Database Mail is commonly used for automated alerts, backup notifications, job failure reports, and maintenance notifications. This function saves significant manual configuration time when setting up new servers, standardizing mail configurations across environments, or migrating to new hardware.

The function preserves all SMTP authentication details including encrypted passwords, handles name conflicts with optional force replacement, and can enable Database Mail on the destination if it’s enabled on the source. You can migrate specific component types or the entire configuration in one operation.

Decrypting the stored passwords requires DAC access to the source instance and Windows administrator access to the source host. On SQL Server Express, the DAC is not available unless the instance is started with trace flag 7806.

The command opens or reuses the dedicated admin connection (DAC) from the machine it runs on. When that machine is different from the source host, enable remote admin connections on the source instance (Set-DbaSpConfigure -SqlInstance -Name RemoteDacConnectionsEnabled -Value 1) and make the DAC TCP listener reachable from that machine. SQL Server listens for the DAC on TCP port 1434 when that port is available; otherwise it assigns a port during startup. Check the SQL Server error log for the active DAC port.

The service master key is read and unprotected on the source Windows host. When the source host is remote, this uses PowerShell remoting. When the command runs directly on the source host, everything runs locally, so remote admin connections and PowerShell remoting are not required.

Use -ExcludePassword to skip password decryption entirely; no DAC is opened and the Windows host is not accessed then.

Syntax

Copy-DbaDbMail -Source <DbaInstanceParameter>
    [-SourceSqlCredential <PSCredential>]
    -Destination <DbaInstanceParameter[]>
    [-DestinationSqlCredential <PSCredential>]
    [-Credential <PSCredential>]
    [-ExcludePassword]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Copy-DbaDbMail -Source <DbaInstanceParameter>
    [-SourceSqlCredential <PSCredential>]
    -Destination <DbaInstanceParameter[]>
    [-DestinationSqlCredential <PSCredential>]
    [-Credential <PSCredential>]
    [-Type <String[]>]
    [-ExcludePassword]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example 1: Copies all database mail objects from sqlserver2014a to sqlcluster using Windows credentials

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

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

Example 2: Copies all database mail objects from sqlserver2014a to sqlcluster using SQL credentials for sqlserver2014a...

PS C:\> Copy-DbaDbMail -Source sqlserver2014a -Destination sqlcluster -SourceSqlCredential $cred

Copies all database mail objects from sqlserver2014a to sqlcluster using SQL credentials for sqlserver2014a and Windows credentials for sqlcluster.

Example 3: Shows what would happen if the command were executed

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

Example 4: Performs execution of function, and will throw a terminating exception if something breaks

PS C:\> Copy-DbaDbMail -Source sqlserver2014a -Destination sqlcluster -EnableException

Required Parameters

-Source

Specifies the source SQL Server instance containing the Database Mail configuration to copy. The function reads all mail profiles, accounts, mail servers, and configuration values from this instance.
You must have sysadmin privileges to access the MSDB database where Database Mail settings are stored.

-Destination

Specifies one or more destination SQL Server instances where the Database Mail configuration will be copied. Accepts an array to migrate to multiple servers simultaneously.
You must have sysadmin privileges on each destination to create mail profiles, accounts, and server configurations in MSDB.

Optional Parameters

-SourceSqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-DestinationSqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Credential

Login to the target OS using alternative credentials. Accepts credential objects (Get-Credential)
Only used when passwords are being exported, as it requires access to the Windows OS via PowerShell remoting to decrypt the passwords.

-Type

Limits migration to specific Database Mail component types instead of copying everything. Choose ‘ConfigurationValues’ for global settings like retry attempts and file size limits, ‘Profiles’ for
mail profile definitions, ‘Accounts’ for SMTP account configurations, or ‘MailServers’ for SMTP server details.
Use this when you only need to sync specific components or when troubleshooting individual Database Mail layers.

-ExcludePassword

Copies credential definitions without the actual password values.
Use this in security-conscious environments where password decryption is restricted or when passwords should be manually reset after migration.
Also use it when the dedicated admin connection or PowerShell remoting described above is not available, because the copy then needs neither.

-Force

Overwrites existing Database Mail objects on the destination that have matching names from the source. Without this switch, existing profiles, accounts, or mail servers are skipped to prevent
accidental data loss.
Use this when updating existing Database Mail configurations or when you need to replace outdated settings with current ones from the source server.

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

Outputs

PSCustomObject (MigrationObject)

Returns one object per Database Mail component migrated (configuration, profile, account, or mail server). Each object tracks the migration status of a single component.

Properties:

• DateTime: Timestamp when the migration operation was performed (Dataplat.Dbatools.Utility.DbaDateTime)
• SourceServer: The source SQL Server instance name
• DestinationServer: The destination SQL Server instance name
• Name: The name of the Database Mail component being migrated (profile name, account name, server name, or “Server Configuration”)
• Type: Category of the component migrated - “Mail Configuration”, “Mail Profile”, “Mail Account”, or “Mail Server”
• Status: Migration result status - “Successful”, “Skipped”, or “Failed”
• Notes: Additional details about the migration outcome (reason for skip, error message, etc.). Null if no additional notes.