SQLServerCentral Article

dbatools - new functions for Database Mail

,

Background

The dbatools module is broadly used and is an ever-growing set of useful commands. While looking for some inspiration I checked the old issues logged on the GitHub repository. Finally, I found this one Remove-DbaDbMail Profile and Account · Issue #4990 · sqlcollaborative/dbatools (github.com). The issue was more than 2.5 years old (32 months). I noticed the functions requested there were still not available in the module. This was, pardon my French, a trigger to create these commands.

This post looks at the two new commands: Remove-DbaDbMailProfile and Remove-DbaDbMailAccount. I have enjoyed writing these and discussing various issues and concepts (thanks to Shawn and Andreas for the support).

Functions

As of today, this is the list of the commands in the module produced using PowerShell code below:

# get a list of commands from dbatools module 
# that can be used to manage the Database Mail
Get-Command -Module dbatools -Name *DbMail*
# # # # # # # # # # #
<#
CommandType     Name                                               Version    Source                            
-----------     ----                                               -------    ------                            
Function        Copy-DbaDbMail                                     1.1.22     dbatools                          
Function        Get-DbaDbMail                                      1.1.22     dbatools                          
Function        Get-DbaDbMailAccount                               1.1.22     dbatools                          
Function        Get-DbaDbMailConfig                                1.1.22     dbatools                          
Function        Get-DbaDbMailHistory                               1.1.22     dbatools                          
Function        Get-DbaDbMailLog                                   1.1.22     dbatools                          
Function        Get-DbaDbMailProfile                               1.1.22     dbatools                          
Function        Get-DbaDbMailServer                                1.1.22     dbatools                          
Function        New-DbaDbMailAccount                               1.1.22     dbatools                          
Function        New-DbaDbMailProfile                               1.1.22     dbatools                          
Function        Remove-DbaDbMailAccount                            1.1.22     dbatools                          
Function        Remove-DbaDbMailProfile                            1.1.22     dbatools
#>

Both functions deal with the Database Mail objects: Accounts and Profiles, respectively. To demonstrate the usage of both functions let's create a small environment using New-DbaDbMailAccount and New-DbaDbMailProfile commands:

# configure dummy database mail accounts
$splatAccount = @{
    SqlInstance = 'localhost'
    DisplayName = 'SQLServerCentral_Mail'
    Description = 'SQLServerCentral Mail Account'
    EmailAddress = 'webmaster@SQLServerCentral.com'
}
New-DbaDbMailAccount @splatAccount -Account AMailAccount
New-DbaDbMailAccount @splatAccount -Account AnotherMailAccount
New-DbaDbMailAccount @splatAccount -Account OneMoreAccount

# configure dummy database mail profiles
$splatProfile = @{
    SqlInstance = 'localhost'
    Description = 'Mail Profile for SQLServerCentral'
}
New-DbaDbMailProfile @splatProfile -Profile MailProfile
New-DbaDbMailProfile @splatProfile -Profile SecondMailProfile
New-DbaDbMailProfile @splatProfile -Profile TheLastMailProfile

Now let's see what we got there, again using dbatools. This time the Get- commands

# list the database mail accounts
Get-DbaDbMailAccount -SqlInstance localhost | Format-Table
# # # # # # # # # # #
<#
ComputerName InstanceName SqlInstance  ID Name               DisplayName           Description                  
------------ ------------ -----------  -- ----               -----------           -----------                  
localhost    MSSQLSERVER  localhost    91 AnotherMailAccount SQLServerCentral_Mail SQLServerCentral Mail Account
localhost    MSSQLSERVER  localhost    87 MailAccount        SqlServerCentral_Mail SqlServerCentral Mail Account
localhost    MSSQLSERVER  localhost    92 OneMoreAccount     SQLServerCentral_Mail SQLServerCentral Mail Account
#>

# list the database mail profiles
Get-DbaDbMailProfile -SqlInstance localhost | Format-Table
# # # # # # # # # # #
<#
ComputerName InstanceName SqlInstance   ID Name               Description                       ForceDeleteForAc
                                                                                                    tiveProfiles
------------ ------------ -----------   -- ----               -----------                       ----------------
localhost    MSSQLSERVER  localhost    221 MailProfile        Mail Profile for SQLServerCentral             True
localhost    MSSQLSERVER  localhost    223 SecondMailProfile  Mail Profile for SQLServerCentral             True
localhost    MSSQLSERVER  localhost    224 TheLastMailProfile Mail Profile for SQLServerCentral             True
#>

Right, all objects created, now we are ready to remove them.

Remove all objects at once

Both functions work in exact same way, so I will be presenting them next to each other. If we simply supply one parameter WhatIf where we can see that all objects will be removed.

Remove-DbaDbMailAccount -SqlInstance localhost -WhatIf
# # # # # # # # # # #
<#
What if: Performing the operation "Removing the database mail account AnotherMailAccount on localhost,1433" on target "localhost,1433".
What if: Performing the operation "Removing the database mail account MailAccount on localhost,1433" on target "localhost,1433".
What if: Performing the operation "Removing the database mail account OneMoreAccount on localhost,1433" on target "localhost,1433".
#>

Remove-DbaDbMailProfile -SqlInstance localhost -WhatIf
# # # # # # # # # # #
<#
What if: Performing the operation "Removing the database mail profile MailProfile on localhost,1433" on target "localhost,1433".
What if: Performing the operation "Removing the database mail profile SecondMailProfile on localhost,1433" on target "localhost,1433".
What if: Performing the operation "Removing the database mail profile TheLastMailProfile on localhost,1433" on target "localhost,1433".
#>

But don't worry, you need to confirm the action before anything gets dropped (unless you supply -Confirm:$false).

Remove selected objects

Removing all objects is not always feasible and we more often want to remove specific objects. Both commands come with parameters to identify the object we would like to remove. These are Account and Profile respectively.

# remove database mail account without prompting for confirmation
Remove-DbaDbMailAccount -SqlInstance localhost -Account MailAccount -Confirm:$false
# # # # # # # # # # #
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance  : localhost
Name         : MailAccount
Status       : Dropped
IsRemoved    : True
#>
# remove database mail profile without prompting for confirmation
Remove-DbaDbMailProfile -SqlInstance localhost  -Profile MailProfile -Confirm:$false
# # # # # # # # # # #
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance  : localhost
Name         : MailProfile
Status       : Dropped
IsRemoved    : True
#>

Remove objects using pipeline from Get- commands

Another way to remove objects in a safe way is to "get" them first and pipe the output to the "remove" command. Let's see how to achieve that in PowerShell.

# save to objects to remove in a variable
# then pipe the output to the remove function
$acc2drop = Get-DbaDbMailAccount -SqlInstance localhost -Account AnotherMailAccount
$acc2drop | Remove-DbaDbMailAccount -Confirm:$false
# # # # # # # # # # #
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance  : localhost
Name         : AnotherMailAccount
Status       : Dropped
IsRemoved    : True
#>
# save to objects to remove in a variable
# then pipe the output to the remove function
$prof2drop = Get-DbaDbMailProfile -SqlInstance localhost -Profile SecondMailProfile
$prof2drop | Remove-DbaDbMailProfile -Confirm:$false
# # # # # # # # # # #
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance  : localhost
Name         : SecondMailProfile
Status       : Dropped
IsRemoved    : True
#>

Conclusion

The functions are there for you to use and get crazy with removing Database Mails and Accounts with PowerShell. Additionally, don't lose your hope if you ever raise an issue for an open-source project. Eventually, someone will look at it and assess it, very often address it too. If you want to contribute to open-source projects, try starting like me, looking for an old issue that could be lost and covered in dust somewhere there.

Thank you,

Mikey

 

 

Rate

4.8 (5)

Share

Share

Rate

4.8 (5)