TSQL2sday #94 Get-DbaRegisteredServerName | Power of dbatools

I thank Rob Sewell (b|t) for hosting TSQL Tuesday #94. Topic for this month Let’s get all Posh – What are you going to automate today?.


If you are a database professional most likely you heard of dbatools by this time. Dbatools is a free PowerShell module with over 200 SQL server administration, best practice and migration commands; written and managed by a group of highly qualified database professionals lead by Chrissy LeMaire (b|t).

I will mention one of the command from this module and the power of this command when you pipe the output to other commands of this module. That command is Get-DbaRegisteredServerName, explained here.

You can read all the server names listed in your Central Management Server (CMS). Then you can pipe those server names to many other commands from the same module. This gives you the power of finding required information from all your servers across the company with one or two lines of code. You can answer questions about all your servers with in minutes. In case of a crisis/disaster you can quickly get a picture of your environment as long as your CMS server is up and running.

Few examples:

Find all SQL Agent jobs that are enabled with the word index in the name.

Get-DbaRegisteredServerName -SqlInstance CmsServer |Find-DbaAgentJob -JobName *index* Where-Object {$_.isEnabled -eq "True"}|ft -AutoSize -Wrap

Find all database files residing in C drive.

Get-DbaRegisteredServerName -SqlInstance CmsServer | Get-DbaDatabaseFile |Where-Object {$_.PhysicalName -like '*E:\*'}|Select sqlinstance, database, logicalName, PhysicalName

Total number of databases and total size in GB.

$Result=Get-DbaRegisteredServerName -SqlInstance marsqlprd65a -Group tst| Get-DbaDatabase |Measure SizeMB -Sum
Write-Output "Total Number of Databases:$($Result.count)"
Write-Output "Total Size in GB         :$([math]::truncate($Result.sum /1024))"

If you liked these 3 examples read this post by Chrissy where she talk about the “commands i run before going on vacation”. Some cool examples using Get-DbaRegisteredServerName command.

If you ever need help with anything related to dbatools slack channel is the best place. Contributors are always there to help the community.