Automate Registering and Maintaining Servers in SSMS

By:   |   Comments (38)   |   Related: 1 | 2 | 3 | 4 | > SQL Server Management Studio Configuration


When you have a lot of SQL Server instances to manage and servers are retired and new ones are added, it becomes a headache to manually maintain these registered servers in SQL Server Management Studio (SSMS).  In this tip we look at how to use a control file that lists the structure to use within SSMS and a PowerShell script that will automatically maintain the list of registered servers within SSMS. 


An easy way to solve this issue is to list all folders and the SQL Server instance names in a spreadsheet or a plain text file, like an INI file in the following format:




;AC_Server2 #comment out



The [HR\REPORT] section indicates there is a sub-folder REPORT under the HR folder. You can create multiple subfolder levels as needed.  With this information, we can automatically recreate the registered servers and folders in SSMS using a PowerShell cmdlet and the final results in SSMS will look like the following:


Technical Challenges Registering the Servers in Management Studio

There are a few challenges in this solution:

  • We need to extract the folder information from the INI file, i.e. we need to know [HR] is a folder name
  • We need to extract the subfolder information if needed, i.e. we need to know that [HR\REPORT] means [REPORT] is a subfolder under parent folder [HR]
  • We need to know any item without square brackets, like HR_Server1, is a server name.

To solve these challenges and make the code elegant, I choose to use Regular Expressions with PowerShell to "interpret" the INI file. Each item in the INI file will have a corresponding PSObject to save its information. The PSObject has three properties:

  1. ParentFolder, where '.' means the root folder
  2. Type, where 'Directory' means a folder and 'Registration' is a server under a folder
  3. Value, is the name of a folder or a server

All items in the INI file will finally be translated into a PSObject array, and this array will be looped through to generate the corresponding folder/subfolder/registered servers in SSMS.

So here is the source code (it should be run in SQL Server 2008 or later environments, where SQLPS is installed)

#ensure the script is run under SQL Server PowerShell window
if ((get-process -id $pid).MainWindowTitle -ne 'SQL Server PowerShell')
        write-error "`r`n`r`nScript should be run in SQL Server PowerShell Window. `r`n`r`nPlease start SQLPS from SSMS to ensure the correct SQLPS version loaded";


[string]$choice = 'Database Engine Server Group' # 'Central Management Server Group\CentralServerName';
$srv = @();

#part 1: Interpret the INI file
SWITCH -Regex -File C:\TEMP\server_list.TXT # change to your own INI file path
    "^\s*\[(\w+[^\\])]$" #folder, the format is [folder]
        $srv +=New-Object -TypeName psobject -Property @{ParentFolder='.'; Type='Directory'; Value=$Matches[1]; };
        $Pf = $matches[1];       

    "^\s*\[(\w+\\.+)]$" #sub-folder, the format is [folder\subfolder]
        $pf = split-path -Path $matches[1];
        [string]$leaf = split-path $matches[1] -Leaf;
        $srv +=New-Object -TypeName psobject -Property @{ParentFolder=$pf; Type='Directory'; Value=$leaf; };
        $pf = $matches[1];

    '^\s*(?![;#\[])(.+)' # if you want to comment out one server, just put ; or # in front of the server name    
        $srv += New-Object -TypeName PSObject -Property @{ParentFolder=$pf; Type='Registration'; value=$matches[1];}


#part 2: create the folder/registered server based on the info in $srv

Set-Location "SQLServer:\SqlRegistration\$($choice)";
dir -Recurse | Remove-Item -force; #clean up everything
foreach ($g in $srv)
    if ($g.Type -eq 'Directory')
        if ($g.ParentFolder -eq '.')
           Set-Location -LiteralPath "SQLServer:\SqlRegistration\$($choice)"
           Set-Location -LiteralPath "SQLServer:\SqlRegistration\$($choice)\$($g.ParentFolder)";
        New-Item -Path $g.Value -ItemType $g.type;
    else # it is a registered server
        $regsrv = $g.value.replace("%5C","\")
        New-Item -Name $(encode-sqlname $g.value) -path "sqlserver:\SQLRegistration\$($choice)\$($g.parentfolder)" -ItemType $g.type -Value ("Server=$regsrv ; integrated security=true");

Set-Location "SQLServer:\SqlRegistration\$($choice)";

How To Use The Code to Register Servers in SSMS

1. Copy and paste the code into Notepad or a PowerShell ISE window, and save it as a ps1 file, let's say, it is saved as:


2. Create an INI file with your required folder/server information and save it to a folder, let's say, it is saved as:


3. From SSMS, right-click on any SQL Server instance in "Object Explorer" and start the SQLPS environment as shown below:


4. In the new opened SQLPS window, run the following two lines of code:

cd c:\temp


5. Go back to your Registered Servers in SSMS and right click on Database Engine and select Refresh.  You should now see the new list of servers.

I have tested the solution with SSMS for SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014 and they are working. Note: In the screenshot above, \TP_W520\ is my computer name and this will be your machine name when you run this script.

Next Steps

Read more articles on on similar topics and the use of registered servers

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

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

Thursday, March 17, 2022 - 9:56:04 AM - q Support Back To Top (89897)
i saved my text file like this








but in the registered server result i am getting
like this. no folder name or no sub folder name. i am testing it on sqlserver 2016





Thursday, March 17, 2022 - 9:14:20 AM - q Support Back To Top (89894)
PS SQLSERVER:\SQL\SQL101S-01\SQL101S> cd c:\temp
PS C:\temp> .\SQLInventoryex.ps1
It : Cannot process argument transformation on parameter 'test'. Cannot convert the "be" value of type "System.String"
to type "System.Management.Automation.ScriptBlock".
At C:\temp\SQLInventoryex.ps1:1 char:23
+ ource code (it should be run in SQL Server 2008 or later environments ...
+ ~~
+ CategoryInfo : InvalidData: (:) [It], ParameterBindingArgumentTransformationException
+ FullyQualifiedErrorId : ParameterArgumentTransformationError,It

New-Item : SQL Server PowerShell provider error: Item 'sql101s-01\sql101s' already exists.
At C:\temp\SQLInventoryex.ps1:61 char:9
+ New-Item -Name $(encode-sqlname $g.value) -path "sqlserver:\S ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (SQLSERVER:\SQLR...1s-01%5Csql101s:SqlPath) [New-Item], GenericProvide
+ FullyQualifiedErrorId : ItemAlreadyExists,Microsoft.PowerShell.Commands.NewItemCommand

Thursday, August 15, 2019 - 6:10:25 PM - jeff_yao Back To Top (82070)

Hi @George, 

Thanks for reading the article and make the comment. I have reviewed the script again (the script has been run multiple times before), I think The only code that may cause c:\temp\ is the following two lines with specific scenario (described below)  

Set-Location "SQLServer:\SqlRegistration\$($choice)";
dir -Recurse | Remove-Item -force; #clean up everything

So if your current defalut folder is c:\temp\ folder and somehow the first line, i.e. set-location line, does not work for whatever reason (usually it will error out and not continue). To debug this is to run this line separately (of course, you need to replace $($choice) to a proper value

Set-Location "SQLServer:\SqlRegistration\$($choice)";

and see whether it can succeed, and if not, the environment is not set up properly (such as SQL Server PowerShell provider is not installed) I have a feeling something is not right in your environment and it caused such issue. Please also state your PowerShell version and sql server version (including OS version) so I may take some further investigation. 


Thursday, August 8, 2019 - 11:26:32 AM - George Back To Top (82004)

Script deleted everything in my c:\temp directory, somehow.   Be very careful if you need anything in the directory.

Friday, May 17, 2019 - 9:45:33 PM - Paresh C. Motiwala Back To Top (80118)

This was superb! thanks for sharing such valuable information!

Friday, April 13, 2018 - 5:21:19 AM - Srinivas Back To Top (75691)

Hello All,


I faced issue like the CMS path is not valid.

later i came to know that we need to save both server list and powershel file in C:\ Drive and run the ps1 file using SQL Server powershell.


Friday, December 2, 2016 - 10:13:32 AM - Prasad Back To Top (44888)

This is a great script. Thanks !

Also, Do you have any idea where I can get the property names for the configuration other than server name. like Description, Default database and color scheme. 


Thanks in Advance 

Prasad Chillara


Monday, April 18, 2016 - 4:15:21 PM - jeff_yao Back To Top (41261)


Hi @K,

If you really want to change the Register server name, the key point is in this line

    New-Item -Name $(encode-sqlname $g.value) -path "sqlserver:\SQLRegistration\$($choice)\$($g.parentfolder)" -ItemType $g.type -Value ("Server=$regsrv ; integrated security=true");

If you give the expected value to -Name parameter, the registered server name will be changed.

But in this tip, I only use the real server instance name in the configuration file, i.e. there is no additioal Registered Server name matching for each real server instance name.

One potential change to the configuration ini file is that we create a dictionary like mapping

RegisterServer1 = RealServerName_1

RegisterServer2 = RealServerName_2


But this means, this tip's PS parsing function need to be revised to accomodate the new content format.




Monday, April 18, 2016 - 6:23:00 AM - K Back To Top (41254)

 Greetings Jeff,

I would like to ask you the same question as Ajay Babu did. I am willing to rewrite script in order to fill in registered server name (display name) different from Server Name. However I failed to find what parameter to add for it. 

 "Server=$regsrv; Regsvrname='My own display name' ; integrated security=true"

Do you know what should be the proper name of parameter instead of Regsrvname to set up display name?

with best regards,


Sunday, December 20, 2015 - 12:57:22 PM - jeff_yao Back To Top (40283)


You are very welcome @Kannan G, and I am glad this tip is of help to you.

Saturday, December 19, 2015 - 1:10:56 PM - Kannan G Back To Top (40282)

Hi Jeff,


Very helpful script.. Thank you so much!!! 


Tuesday, October 13, 2015 - 3:13:34 PM - jeff_yao Back To Top (38881)

@Ajay, in theory, yes. However, this tip cannot do this without a big change because we need to introduce the information of the "display name" for each real server name. This will lead to the format change of the ini file and then the parsing logic in the PS script. But if I do see some high interests of such requirement from readers, I can try to refactor the whole process to make this happen.

Tuesday, October 13, 2015 - 11:44:24 AM - Ajay Babu Back To Top (38877)

Is there a way I can use registered Server name(display name) different from servername?

Not sure how to modify the script. but if the registered servername also  can be given the ini file that would be great.

Tuesday, July 21, 2015 - 1:07:48 PM - jeff_yao Back To Top (38269)

Thanks @Arvind, glad it is of help to you, and also thanks for confirming that using SQL standard authentication works (as I did not try it myself)

Tuesday, July 21, 2015 - 11:42:58 AM - ARVIND RAVISH Back To Top (38267)

Wonderful Sir. I modified the PS script to even include sa authentication. It works great!!

Wednesday, September 17, 2014 - 5:53:31 PM - jeff_yao Back To Top (34568)

Thanks @Jimit and I am glad it is of some help to you.

Wednesday, September 17, 2014 - 4:46:09 AM - Jimit Back To Top (34557)

This script works like a miracle.You are awesome. 

Tuesday, August 5, 2014 - 1:31:21 PM - jeff_yao Back To Top (34012)

Thanks for your update, @PC, and very glad to hear it works for you now.

Tuesday, August 5, 2014 - 5:06:51 AM - PC Back To Top (34002)

Hi Jeff, thank you very much for detail explanation. Now I fixed the issue with the format of config file and able to create sub-folders.

Wednesday, July 30, 2014 - 1:14:29 PM - jeff_yao Back To Top (33941)

Hi @PC,

Can you please post a sample part of your configuration file here? and also the exact error message?

In my test (yes, I did very extensive test on sql2008 to sql2012, on various computers in my environment), and I have not encountered any issue. Just some quick notice

The format of the configuration file should be:

Parent Folder to be listed first before sub folder, even if there is no direct server under the parent folder.

For example, the following is accepted:









The following is NOT accepted:





The reason it is not accepted is because there is no parent folder [Production] in the configuration file. You should put [Production] before [Production\ABC], i.e.












Wednesday, July 30, 2014 - 9:07:40 AM - PC Back To Top (33931)


Hi Jeff, thank you very much for this valubale tip. Here I am also facing the similar issue by George. I am using SS2008 R2. Its working perfect with parent folder and if you speciofy the sub folders its throwing similar errors specified by George. Could you please help me to get rid of this issue. 

Monday, July 21, 2014 - 1:30:30 PM - jeff_yao Back To Top (32806)

Hi George,

I am "glad" you encounter this issue too, as I believe this is indeed a bug which I logged it myself. This happens when sql server 2012 and 2014 are installed side by side. 

I have a pretty detailed blog about its root cause. Please see here.




Sunday, July 20, 2014 - 4:02:52 PM - George Back To Top (32797)

I have Sql Server 2012 Express + Management Studio installed, plus 2014 Management Studio (standalone).  When I ran this from powershell within Sql Server 2014 Management Studio; a few things happened:


1.  The "[Production/A]" lines had the 'production' part omitted in SQL 2014; it just created a folder based on the sub folder name (in this case "A")

2.  In some instances, the directories were created, but the servers were not created at all.

3. The following error occured when running the powershell script:

Set-Location : Cannot find path 'SQLServer:\SqlRegistration\Database Engine Server Group\PRODUCTION' because it does not exist. At C:\temp\Register_SQL_Servers.ps1:59 char:12 + Set-Location -LiteralPath "SQLServer:\SqlRegistration\$($choice)\$($g ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~ + CategoryInfo : ObjectNotFound: (SQLServer:\SqlR...roup\PRODUCTI ON:String) [Set-Location], ItemNotFoundException + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.SetLo cationCommand New-Item : SQL Server PowerShell provider error: Invalid Path: 'SQLSERVER:\SQLRegistration\Database Engine Server Group\PRODUCTION\A'. At C:\temp\Register_SQL_Servers.ps1:66 char:9 + New-Item -Name $(encode-sqlname $g.value) -path "sqlserver:\SQLRegistrat ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~ + CategoryInfo : InvalidData: (SQLSERVER:\SQLR...ODUCTION\A: SqlPath) [New-Item], GenericProviderException + FullyQualifiedErrorId : InvalidPath,Microsoft.PowerShell.Commands.NewIte mCommand 
Incidentally, if I run it in SSMS 2014; the folders show up when I open SSMS 2012; but they don't show in SSMS 2014.

Friday, July 18, 2014 - 2:32:43 PM - jeff_yao Back To Top (32777)

Hi Wei,

I used your config file





(note: I only changed your first [xxxxx] to [xxxx], i.e. 4 'x' instead of 5 'x because your 2nd [xxxxx] has 5 'x' already)

and then I run the PS script, and I can create the registered servers in both sql2k8 R2 and sql 2012 without any problems.



Thursday, July 17, 2014 - 7:34:02 PM - WEI Back To Top (32762)

Thanks Jeff,

You are right, there was blank space in the config. file.

After clearing the blank space, I re-run and refresh CMS, all good.

However, I still getting the same error message but working.

Below is partial of the config file:







Thursday, July 17, 2014 - 1:17:37 PM - jeff_yao Back To Top (32760)

Hi Wei,

From the error msg,  Item ' ' already exists, It seems you have a blank space some where in your configuration file, I'd recommend you to do two things:

1. Clean up your registered servers in SSMS

2. Put one or two items only in your configuration file and test it.

If there is still problem, please update here and I'll contact you directly to solve your issue.




Wednesday, July 16, 2014 - 9:52:02 PM - WEI Back To Top (32745)

Hi Jeff,

got the below error, can you help?

PS C:\temp> ./register_svr.ps1
New-Item : SQL Server PowerShell provider error: Item ' ' already exists.
At C:\temp\register_svr.ps1:59 char:17
+         New-Item <<<<  -Name $(encode-sqlname $g.value) -path "sqlserver:\SQL
Registration\$($choice)\$($g.parentfolder)" -ItemType $g.type -Value ("Server=$
regsrv ; integrated security=true");
    + CategoryInfo          : OperationStopped: (SQLSERVER:\SQLR...L05-TEST\TE
   ST\ :SqlPath) [New-Item], GenericProviderException
    + FullyQualifiedErrorId : ItemAlreadyExists,Microsoft.PowerShell.Commands.

Wednesday, July 16, 2014 - 12:45:32 PM - jeff_yao Back To Top (32737)

@Mark, thanks for your question / suggestion.

So first to your question: Yes, you need to maintain a text file (or spreadsheet) AND make changes (manual or automatic is another topic) when servers get added/deleted before running the script.

As for automation of the text file, it is actually not complex in automation itself, but "complex" in your business practice on how to record the changes when a server is added/deleted/renamed etc. 

IMHO, this tip is more for individual DBA according to his/her own taste. For example, I may choose a group name [HR] for a bunch of servers I manage, while my colleage may prefer [HumanResource] as his preferred group name.

There can be quite a few revised ways to use the tip:

1st scenario: You have a team of several DBAs, and if you all agree to the naming conentions (like Group names, Group structures, Group members), you can create the configuration text file in a shared folder, and in the PS script, change to this shared config file before running the script. This way, you only maintain one file, and then share all changes by whole team.

2nd scenario: assuming you maintain all your server inventory in tables, then depending on your data modelling, you can write your own customized solution to dump the data out to a configuration file, you can do it via SSIS package, PowerShell script or anything of your choice. 







Wednesday, July 16, 2014 - 10:55:09 AM - Mark Back To Top (32735)

Hi Jeff,

Thanks for the solution, one question before I test it in my environment - this assumes that we maintain a text file/spreadsheet containing the list of SQL servers we manage correct? If servers get added/deleted, it becomes imperative that the text file/spreadsheet be up to date MANUALLY before running the powershell script, correct?

I think you see where i'm getting :) (a suggestion for the next version would be to automatically generate a list of SQL servers in your environment (automate that!), without manual maintenance - but hey, just a simple suggestion)

Thanks again!

Friday, July 11, 2014 - 12:51:19 PM - jeff_yao Back To Top (32657)

Glad it works for you, Scott, and appreciate your update/feedback.

Friday, July 11, 2014 - 1:19:58 AM - Scott_Davis Back To Top (32646)
Jeff, Thanks. Your solution solved the problem. I'm modifying the script to pull this info from a View with folder hierarchies pre-defined

Thursday, July 10, 2014 - 1:44:32 PM - jeff_yao Back To Top (32637)

Hi @Scott Davis, thanks for reading my article. 

From the error msg  Invalid Path: 'SQLSERVER:\SQLRegistration\Central Management Server Group\XXCLLEVEL1\XXCLLEVEL2'

I guess, you did not REGISTER a  "Central Management Server" first.

From SSMS (I am using Sql server 2012 as an example), click menu View -> Registered Servers, right click "Central Management Servers", and click "Register Central Management Server..", let's say your CMS name is CMS_Svr1

Then you need to edit the PS script line as follows:

[string]$choice =  'Central Management Server Group\CMS_Svr1';

In your error msg, there is no central server in the path (I assume XXCLLEVEL1 is not your CMS server name).
I just tested the script in my SQL SERVER 2012 envrionment, it works as expected.
Please let me know if you still have issues.


Thursday, July 10, 2014 - 4:12:30 AM - Scott Davis Back To Top (32621)

Thanks for this code.


I am having trouble with running this when trying to use it to create registrations for a central management server

regardless of whether I update the path or use the script as is, I get an error in SQL2012 suggesting invalid path

** I have sanitized this **

New-Item : SQL Server PowerShell provider error: Invalid Path: 'SQLSERVER:\SQLRegistration\Central Management Server Group\XXCLLEVEL1\XXCLLEVEL2'.At D:\scripts\populatecms\CMS_SvrReg_Grouping.ps1:59 char:17
+         New-Item <<<<  -Name $(encode-sqlname $g.value) -path "sqlserver:\SQLRegistration\$($choice)\$($g.parentfolder)" -ItemType $g.type -Value ("Server=$regsrv ; integrated security=true");    + CategoryInfo          : InvalidData: (SQLSERVER:\SQLR...0\XXSERVERNAME:
   SqlPath) [New-Item], GenericProviderException    + FullyQualifiedErrorId : InvalidPath,Microsoft.PowerShell.Commands.NewItemCommand

I have edited the ps1 to reflect the CMS path

[string]$choice = 'Central Management Server Group' # 'Central Management Server Group\SQLOPS';
$srv = @();

 can you shed some light?  



Wednesday, July 2, 2014 - 7:58:34 PM - jeff_yao Back To Top (32521)

Thanks Erik for your comment.

I think registered server in SSMS cannot take property such as applicationIntent=Readonly in its connection string. In SSMS\Registered Servers window, if you right-click any registered server and select "Properties", you can see all the configurable items a registered server can take, such as Windows login or regular SQL Login / password, and the database to be connected to (by default) etc.

In my test environment, I try to add ApplicationIntent=Readonly in the -value () clause, the registered server can be created, but if I open a new query window connecting to the new registered server, and then run select @@servername, it still goes to the primary node of HAG. (in the -value connectinon string, I use HAG group listener in the Server-"" not the primary node name). So this seem to prove, applicationintent value is ignore. 

Wednesday, July 2, 2014 - 8:27:33 AM - Erik Back To Top (32510)

Thanks for the great script, worked great!  I have one question though that has prevented us from using registered servers in our environment.  Is there a way to include optional parameters for the connection?  We have AAG and in order to connect to the read only instances you have to include applicationIntent = ReadOnly or you get errors.



Tuesday, July 1, 2014 - 4:17:59 PM - jeff_yao Back To Top (32502)

Thanks Hiren and heslous for yur comments. for

I'd also like to thank Greg Robidoux for reviewing and improving this tip.

To heslour, if you want to register a server using SQL account instead of a Windows one, you can simply modify the 2nd bottom line (not counting the "}"), where you can replace 

integrated security = true


user id=<sql_login_acct>; password='password'


for example, say I am using sa as the sql account, I will change

-Value ("Server=$regsrv ; integrated security=true");
-Value ("Server=$regsrv ; user id=sa; password='sa_password' ");
However because I am doing a batch registration, and if you need to use sql login account, it means, all the registered servers need to have the same sql login account with the same password. 

Tuesday, July 1, 2014 - 9:59:57 AM - heslous Back To Top (32493)

Great  !!This is a pretty fast method

But how do you handle the fact to get name registered using a SQL account ??  vs a Windows one

Tuesday, July 1, 2014 - 8:46:28 AM - Hiren Patel Back To Top (32492)

Awesome!! script, i was able to get my server register in no time. Thank you!!

get free sql tips
agree to terms