Skip to content

Why dbatools is awesome

dbotools is an amazing PowerShell module. It lets you maintain a large set of SQL Server instances with quick and easy to use commands. For example, I recently needed to update the domain name attached to the database mail account on over 40 instances. Without dbatools this would take most of the day to update singly in SSMS. However the following one-liner did it in under a minute.

$RSInstance = "";
$OldDomain = "";
$NewDomain = "";
Get-DbaRegisteredServer -SqlInstance $RSInstance | Invoke-DbaSqlQuery -AppendServerInstance -Query "Select account_id, Name, email_address, replyto_address from msdb.dbo.sysmail_account" | Where-Object{ ($.replyto_address -match $OldDomain) -or ( $.email_address -match $OldDomain ) } | Select-Object * ,@{name="replyto_address_fixed";expression={ $.replyto_address -Replace $OldDomain, $NewDomain } } , @{ name="email_address_fixed";expression={ $.email_address -Replace $OldDomain, $NewDomain } } | Select-Object ServerInstance , @{name="UpdateSQL"; expression={ "EXEC msdb.dbo.sysmail_update_account_sp @account_id=$($.account_id), @email_address='$($.email_address_fixed)', @replyto_address='$($.replyto_address_fixed)';" } } | ForEach-Object{ Invoke-DbaSqlQuery -SqlInstance $.ServerInstance -Database msdb -Query $_.UpdateSql

Now that looks like a big mess of code so lets break it down remembering that the | joins everything together on the pipeline.

$RSInstance = "" – The registered server database instance.
$OldDomain = "" – Our old domain
$NewDomain = "" – The new domain

And now the start of the pipeline:

Get-DbaRegisteredServer -SqlInstance $RSInstance |
Get all of our sql servers instances.

Invoke-DbaSqlQuery -AppendServerInstance -Query "Select account_id, Name, email_address, replyto_address from msdb.dbo.sysmail_account" |
Query each instance to pull account information and the sql instance it’s for.

Where-Object{ ($.replyto_address -match $OldDomain) -or ( $.email_address -match $OldDomain ) } |
Only return accounts which need updated.

Select-Object * ,@{name="replyto_address_fixed";expression={ $.replyto_address -Replace $OldDomain, $NewDomain } } , @{ name="email_address_fixed";expression={ $.email_address -Replace $OldDomain, $NewDomain } } |
Here we add 2 columns to the resultset. These columns are the corrected email addresses.

Select-Object ServerInstance , @{name="UpdateSQL"; expression={ "EXEC msdb.dbo.sysmail_update_account_sp @account_id=$($.account_id), @email_address='$($.email_address_fixed)', @replyto_address='$($_.replyto_address_fixed)';" } } |
Generate a SQL statement to update the accounts. At this point we drop unnecessary columns.

ForEach-Object{ Invoke-DbaSqlQuery -SqlInstance $.ServerInstance -Database msdb -Query $.UpdateSql
Finally, loop through each record and update the account using the generated sql.

This took about 15 minutes to write and less than a minute to execute. It’s just another example of how dbatools makes the life of a database administrator so much better.

Published inUncategorized

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *