Using PowerShell to Register All SQL Instances Into Central Management Server

By:   |   Comments (14)   |   Related: More > Central Management Servers


Managing multiple SQL Servers has its challenges. One of the big challenges is having to collect data from each of the servers you manage and figure out which servers need attention. SQL Server has introduced a new feature called Central Management repository which can be used to manage multiple instances. One of the challenges to using CMS is that you have to register all the SQL Servers manually into CMS.  The below article will guide you on how to register hundreds of servers in a second for SQL Server CMS.


As a best practice, most organizations maintain the inventory of SQL Server instances so they now what is running where.  To register multiple servers into CMS you will need to have this list of all SQL Server instances. If you don't have an inventory you can create the database inventory using SQLCMD as shown below.

Run the SQLCMD -L to get a list of all SQL Server instances registered in the network and store them into a SQL table.

SQLCMD Command to retrieve the servers in network

Minimum Requirement to register the multiple servers into CMS

  • SQL Server Instance Inventory
  • SQL Server 2008 Instance to act as the CMS

You can follow the below steps to register the SQL Instances into CMS.

Step 1: Copy and Paste the below PowerShell script into NotePad and save the file as "RegSvr.ps1" in "c:\myscripts\" folder. (Please note you can save the file at any location, but in the next step you will need to make that adjustment.)

$cmsservers = invoke-sqlcmd -serverinstance 'Put SQL Instance name here' 
-database 'Put SQL Inventory DB Here'
-query "SELECT SERVERNAME FROM SERVERLISTTABLE" cd 'SQLSERVER:\sqlregistration\Central Management Server Group\CMSServerName\CMSGroupName\
CMSSubGroupName\' foreach ($server in $cmsservers) { $svrname = $server.serverName $regsvrname = $svrname if ($regsvrname -like "*\*") { $regsvrname = $regsvrname.replace("\", "%5C") } $svrname new-item $regsvrname -itemtype registration -value "server= $regsvrname;
integrated security=true" }

Step 2: Modify the PowerShell script and put the SQL Instance Name, Inventory Database Name, Query to Retrieve the SQL Instance Names, and Central Management Server Name and the CMS Path where you want to register the SQL Server Instances. Once you update the script, save it again.

For example:

  • SQL Instance Name is MSSQLCMDB
  • Inventory Database Name is CMDB
  • Table name where the SQL Instances name stored is TBLSERVERLIST
  • and CMS server name and path is WIT\Production\SQL2008

Please take a look at the my CMS server and the path. I want to register the SQL instances into SQL2008 group.

Central Management Server

You have to edit the powershell script as below.

Powershell Script Example

Step 3: Go to object explorer and connect to the Central Management Server. Right click on the CMS Server Name and click on "Start PowerShell" as shown below.

Step to start the powershell

Step 4: Once the PowerShell prompt opens, you have to enter the command SL "Location of PowerShell script file" as shown below.

PS command to change the directory

Step 5: Now here is the last step that will execute the PowerShell script and it will register the servers into CMS. To execute the script you have to run "./regsvr.ps1" on the prompt as shown below.

PS command to execute the power shell script

Step 6: Go to the CMS server and expand the group to check the list of registered servers or you can query the CMS tables as shown below.

/* Servers and groups */
SELECT DISTINCT AS 'Server Group Name'
     ,svr.server_name AS 'Server Name'
FROM msdb.dbo.sysmanagement_shared_server_groups_internal groups 
INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal svr
 ON groups.server_group_id = svr.server_group_id;
Next Steps
  • Create the different groups into CMS and register the SQL Servers into each group.
  • Evaluate your Policies using the CMS registered servers.
  • Maintain the CMS and add or remove SQL instances as needed.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jugal Shah Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, July 24, 2018 - 6:42:14 AM - Vusi Back To Top (76804)

HI Jagul 

this is the error i am getting when running the scrip, please advise . much appriciated fro the script .

PS H:\MSSQL$AIMS_MAIN1_LIVE\MntVol-Remote-BACKUP01\Tumi> .\RegSrv.ps1

- : Missing expression after unary operator '-'.

    + CategoryInfo          : ParserError: (-:String) [], ParseException

    + FullyQualifiedErrorId : MissingExpressionAfterOperator

Wednesday, March 8, 2017 - 3:25:34 AM - Seshatheri Back To Top (47565)

i am getting below error can you please help me


New-Item : Cannot bind argument to parameter 'Path' because it is null.

At T:\Register_Server_automation\RegSrvs.ps1:12 char:10

+ new-item $regsvrname -itemtype registration -value "server= $regsvrname;

+          ~~~~~~~~~~~

    + CategoryInfo          : InvalidData: (:) [New-Item], ParameterBindingVal


    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,M


Wednesday, March 1, 2017 - 4:03:17 PM - Sundar Back To Top (46861)

For below (without - symbol)

cd 'SQLSERVER:\sqlregistration\Local Server Groups\Delta\'


I get following error

PS D:\dummy> ./RegSrvr.ps1

cd : Cannot find path 'SQLSERVER:\sqlregistration\Local Server Groups\D1\' because it does not exist.

At D:\dv\RegSrvr.ps1:2 char:3

+ cd <<<<  'SQLSERVER:\sqlregistration\Local Server Groups\D1\'

    + CategoryInfo          : ObjectNotFound: (SQLSERVER:\sqlr...r Groups\D1

   a\:String) [Set-Location], ItemNotFoundException

    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.SetLocationCommand


new-item : The type is not a known type for the file system. Only "file" and "directory" can be specified.

At D:\dummy\RegSrvr.ps1:12 char:9

+ new-item <<<<  $regsvrname -itemtype registration -value "server= $regsvrname;integrated security=true"

    + CategoryInfo          : InvalidArgument: (:) [New-Item], PSArgumentException

    + FullyQualifiedErrorId : Argument,Microsoft.PowerShell.Commands.NewItemCommand



Wednesday, March 1, 2017 - 3:58:18 PM - Sundar Back To Top (46859)

It fails for

 $cmsservers = invoke-sqlcmd -serverinstance 'C' -database 'D' -query "SELECT SERVERNAME FROM SERVERLISTTABLE"

-cd 'SQLSERVER:\sqlregistration\Local Server Groups\D1\'


with following error message:

PS D:\dummy> ./RegSrvr.ps1

- : Missing expression after unary operator '-'.

    + CategoryInfo          : ParserError: (-:String) [], ParseException

    + FullyQualifiedErrorId : MissingExpressionAfterOperator



Sunday, September 7, 2014 - 2:34:55 PM - Yunus Parvez Back To Top (34427)



This script is working using SQLPS but when i am using the same script in JOB in SQL Server Agent using type as Powershell getting error that 'Cannot find path 'SQLSERVER:\SQLRegistration\Central Management Server Group


Can you please help as daily we have 1-2 server added to our env.

Tuesday, May 27, 2014 - 4:44:23 AM - SD Back To Top (31948)

Does anyone know how one goes about removing registered servers using powershell. The reason for this is to remove registered servers when any get decommissioned.

I was thinking of using the following code, but this does not seem to work.

foreach ($server in $cmsdelservers)
$svrname = $server.SQLInstance
$regsvrname = $svrname
if ($regsvrname -like "*\*")
$regsvrname = $regsvrname.replace("\", "%5C")
cd 'SQLSERVER:\sqlregistration\Central Management Server Group\BBAMINFDEV01\BBAM\PRD\'
remove-item $regsvrname



Thursday, April 17, 2014 - 1:45:45 PM - Crazy DBA Back To Top (30098)

I worked on a similar solution to collect Windows and SQL Server inventory by gathering scripts from your posts and other SQL Family and released it as a free tool at The tool uses powershell to grab data from all servers and stores it in SQL tables and can be viewed using SSRS reports. Can you please check it and give me any suggestions to improve it?


Thursday, April 3, 2014 - 6:23:08 AM - Martin Back To Top (29956)

To solve the issue with named instances I replaced $regsvrname with $svrname in the last line, like this:

new-item $regsvrname -itemtype registration -value "server= $svrname;
integrated security=true" }

Tuesday, July 30, 2013 - 5:00:34 PM - Hawk Back To Top (26063)

This is a great tip, but I've found the registration of named instances is a problem.  Because the backslash is converted to a "%5C" in the "Server Name" value of the registration, but the "Registered Server Name" is correct with the backslash.


I see the script seeks to overcome the issue of a backslash in Powershell with the hexidecimal "%5C", but it does not appear to be working properly.


Has anyone found a means to overcome this?  I've got hundreds of instances to register and many have a backslash in the name, since they're named instances.

Wednesday, July 4, 2012 - 2:53:31 AM - Amey Back To Top (18337)

Hi Jugal, as you see I have just added my servername  , database name and Query and the path of CMS where I wanted to register the SQL instance. I have not changed the code.

I am getting the below error in the powershell window :


New-Item : A parameter cannot be found that matches parameter name 'registration' . At E:\scripts\RegSrv.Ps1:14 char:9

+ new-item <<<< $regsvrname-itemtype registration - value "server='$server.Mach_lpar_nm;integrated security =true"


Please suggest, 




Tuesday, July 3, 2012 - 1:51:53 PM - Jugal Back To Top (18326)

Instead of modifying the code, can you please add your Server Name, Query into script. That will resolve the issue

Monday, July 2, 2012 - 5:02:14 PM - Amey Back To Top (18308)

Hello Jugal , 

Thanks for the script  ! I modified the script  for my environment as below  

-----------modified script--------------------------------------

$cmsservers = invoke-sqlcmd -serverinstance 'ax-siscdcsql821\sql821'-database'ETXDSDBS'-query"select distinct MACH_LPAR_NM from [ETXDSDBS].[dbo].[v_Sql_metrics_extended_no_dl] where MACH_LPAR_NM like '%AX-RISC%'

"cd'SQLSERVER:\sqlregistration\Central Management Server Group\AX-SISCCMSQLPOC\PBM_DEV\test\'


foreach ($server in $cmsservers)


$svrname = $server.serverName

$regsvrname = $svrname

if ($regsvrname -like "*\*")


$regsvrname = $regsvrname.replace("\", "%5C")



new-item $regsvrname -itemtype registration -value "server= $regsvrname;

integrated security=true"



------------------------------------modified script-------------------------------------------


When I ran this script on the CMS , I am getting the following error in powershell 


PS E:\scripts> ./RegSvr.ps1

Incomplete string token.

At E:\scripts\RegSvr.ps1:8 char:25

+ if ($regsvrname -like "*\ <<<< *")


Can you please shed some light on this error message ? Thanks again for your time and the script!


- Amey Thakur 



Saturday, March 31, 2012 - 4:01:49 PM - Jugal Shah Back To Top (16722)

You can try alternative, setSPN -L (Service Account), if the Service Account is common for all the SQL instances...

Thursday, March 29, 2012 - 9:04:52 AM - Karsten Mueller Back To Top (16701)


First of all - thanx for this tip! I think I can use it, because we´ve got a large SQL-Server Environment.

But one question: Do you know how to use the sqlcmd -L command to list also instances on SQL-Server-Failover-Clusters.
I tested it, but I can see all my normal instances, except clustered instances.

Thanks in advence!


Karsten Mueller

get free sql tips
agree to terms