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