Is it possible to script the creation of registered servers in SSMS 2008?

I have about 60 servers that I want to add as R开发者_开发知识库egistered servers for quick access. They are similarly named...is there a way to script this so I don't have to go through the wizard 60 times? Thanks!

P.S. I did check the XML file and it looks like a beast. Not sure if copying and pasting 60 times is what I want to do...

If you're comfortable in PowerShell, it can be done that way. See Registering SQL Servers in 2000 EM, 2005 SSMS, and 2008 SSMS for a starting point.

I have a script that I wrote that will generate the XML to create a .regsrvr file that can then be imported into SSMS (tested in SQL 2008). It assumes you have a source for metadata about your servers. If you have that data in a 'dba' table somewhere, it could work for you. Here's the link to my blog article. http://sqldavel.blogspot.com/2013/03/maintaining-registered-servers.html

Actually, there is a way!

It involves a few steps, but can be done via TSQL:

1) Get your list in a table.

2) Use the following Query to make the list. Make sure you set the @FolderName to match an existing folder:

DECLARE @FolderName VARCHAR(100) = 'PROD'
DECLARE @xml_header VARCHAR(MAX) =  N'<?xml version="1.0"?>
<model xmlns="http://schemas.serviceml.org/smlif/2007/02">
  <identity>
    <name>urn:uuid:96fe1236-abf6-4a57-b54d-e9baab394fd1</name>
    <baseURI>http://documentcollection/</baseURI>
  </identity>
  <xs:bufferSchema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <definitions xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08">
      <document>
        <docinfo>
          <aliases>
            <alias>/system/schema/RegisteredServers</alias>
          </aliases>
          <sfc:version DomainVersion="1" />
        </docinfo>
        <data>
          <xs:schema targetNamespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
            <xs:element name="ServerGroup">
              <xs:complexType>
                <xs:sequence>
                  <xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
            <xs:element name="RegisteredServer">
              <xs:complexType>
                <xs:sequence>
                  <xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
            <RegisteredServers:bufferData xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08">
              <instances xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08">
                <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + /*Folder_Name*/ + @FolderName + '</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:RegisteredServers>
                        <sfc:Collection>'
, @xml_middle VARCHAR(MAX) = '</sfc:Collection>
                      </RegisteredServers:RegisteredServers>
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">' + @FolderName + '</RegisteredServers:Name>
                      <RegisteredServers:Description type="string" />
                      <RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
                    </RegisteredServers:ServerGroup>
                  </data>
                </document>'
/*Ending*/
, @xml_footer VARCHAR(MAX) = '              </instances>
            </RegisteredServers:bufferData>
          </xs:schema>
        </data>
      </document>
    </definitions>
  </xs:bufferSchema>
</model>'
, @xml XML
, @serverDetails VARCHAR(MAX)
, @serverReferences VARCHAR(MAX)

SELECT  /*SERVER SPECIFIC*/
@serverDetails = COALESCE(@serverDetails, '') + 
'                <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + /*Folder_Name*/ + @FolderName + '/RegisteredServer/' + SS.Server + '</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:RegisteredServer xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + /*Folder_Name*/ @FolderName + '</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">' + SS.Server + '</RegisteredServers:Name>
                      <RegisteredServers:Description type="string" />
                      <RegisteredServers:ServerName type="string">' + SS.Server + '</RegisteredServers:ServerName>
                      <RegisteredServers:UseCustomConnectionColor type="boolean">false</RegisteredServers:UseCustomConnectionColor>
                      <RegisteredServers:CustomConnectionColorArgb type="int">-986896</RegisteredServers:CustomConnectionColorArgb>
                      <RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
                      <RegisteredServers:ConnectionStringWithEncryptedPassword type="string">data source=' + SS.Server + ';integrated security=True;pooling=False;multipleactiveresultsets=False;connect timeout=30;encrypt=False;trustservercertificate=False;packet size=4096</RegisteredServers:ConnectionStringWithEncryptedPassword>
                      <RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">None</RegisteredServers:CredentialPersistenceType>
                      <RegisteredServers:OtherParams type="string" />
                      <RegisteredServers:AuthenticationType type="int">0</RegisteredServers:AuthenticationType>
                      <RegisteredServers:ActiveDirectoryUserId type="string" />
                      <RegisteredServers:ActiveDirectoryTenant type="string" />
                    </RegisteredServers:RegisteredServer>
                  </data>
                </document>
'
, @serverReferences = COALESCE(@serverReferences, '') + 
'                          <sfc:Reference sml:ref="true">
                            <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + /*Folder_Name*/ + @FolderName + '/RegisteredServer/' + SS.Server + '</sml:Uri>
                          </sfc:Reference>
'
        FROM /*Table with Server List*/ServerList SS
        WHERE EnvironmentTypeValue IN ('PROD')      

SET @xml = CAST( (@xml_header + @serverReferences + @xml_middle + @serverDetails  + @xml_footer) AS xml)
SELECT @xml

3) You may need to open the XML and then copy and paste into a text file with the extension regsrvr

4) Open SSMS, go to the Registered Servers, Select the Group and First Header you wish the list to go.

5) Import and be done! :)

Note you cannot import into SQL Server 2017 from a lower version. Sadly.