TSQL2sday #94 Get-DbaRegisteredServerName | Power of dbatools

September 12, 2017

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 commands 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 the required information from all your servers across the company with one or two lines of code. You can answer questions about all your servers within 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.

<pre class="wp-block-syntaxhighlighter-code">Get-DbaRegisteredServerName -SqlInstance CmsServer | Get-DbaDatabaseFile |Where-Object {$_.PhysicalName -like '*E:\*'}|Select sqlinstance, database, logicalName, PhysicalName</pre>

The total number of databases and total size in GB.

$Result=Get-DbaRegisteredServerName -SqlInstance CmsServer -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 talks 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.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.