Import and Export Registered SQL Servers To Other Machines

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


Problem

In a previous tip we discussed how to register servers in SQL Server Management Studio.  Now that I have my registered servers setup the way I like, I need to setup other computers the same way that are used to manage my SQL Servers.  Instead of having to manually add the registered servers on each computer in this tip I show you how you can import and export registered servers between computers.

Solution

Fortunately SSMS provides an import and export option for registered database servers. Registered servers can easily be exported to a file and then imported to SSMS on the same or different machine.  The export process allows you to export one server, one server group or all registered servers.  There is not an option to pick multiple servers or multiple server groups.  In this tip, I do show how you can manipulate the XML file that is created to customize the file.

Let's go through the export/import process of SQL Server registered servers using SSMS.

Prepare SQL Server registered server groups

First of all we need to create SQL Server registered servers to work with. As you may know a server instance can be registered even without verification of login credentials. So to simplify the process of creating registered servers, let's register servers with fake IPs for simulation.  I have registered 9 servers in 4 server groups as shown below.  Registered server groups for both SSMS 2008 and SSMS 2005 are presented below.

SSMS provides an import and export option for registered database servers

Export one registered server group

Starting with a simple scenario we will export a single server group.

  • Right click on the server group required to export and choose the Export... option.  Note: the right click menu may be slightly different in SSMS 2008 and SSMS 2005 as shown below.
Note: the right click menu may be slightly different in SSMS 2008 and SSMS 2005 as shown below.
  • A frame will appear, reconfirm the required server group is selected, in my case DevelopmentServers.
reconfirm the required server group is selected

Browse the path and give the export file a name. By default the check box is checked, so user names and passwords will not be included in the export file.  If user names and passwords are included in the export, passwords will be encrypted.  Also, when you connect you will still need to provide login credentials.  When done clicked OK to generate the export file.

Export all registered servers group

Now to export all server groups. The same process is used as in the single server group export. This time right click on the root folder and select the export option.   To select all servers select "Local Server Groups" folder and if you are using SSMS 2008 or select "Database Engine" folder if you are using SSMS 2005. Click OK to generate the export file.

Export selected registered servers groups

In order to export selected server groups or selected servers we need to edit the .regsrvr file generated from the export process.  The other option is to delete servers groups after they have been imported into the target SSMS.

Before proceeding with the edit process of the generated export file,  let's take a look at the internal structure of the export files generated using SSMS 2005 and SSMS 2008.   Below I have presented a collapsed view of tags to clarify the structure in which server groups and servers are arranged

take a look at the internal structure of the export files generated using SSMS 2005 and SSMS 2008
The other option is to delete servers groups after they have been imported into the target SSMS

Our nine servers managed in the four server groups are stored in files in the above format.   Here we can edit the file to remove either entire servers groups or any server inside the groups.  For this example I am going to remove the server group 'ProductionServers'.

For SSMS 2008 I would remove the following marked tags and also the entry of ProductionServers.

For SSMS 2008 I would remove the following marked tags and also the entry of ProductionServers

For SSMS 2005 I would remove the whole server group as shown below.

For SSMS 2005 I would remove the whole server group as shown below

At this point the file no longer contains production servers that were stored in the ProductionServers group and we can now import into the target SSMS.

Import Servers Groups

To import we use the same menu in SSMS that we used for the export, but select import instead.   The import frame will appear as shown below.  Just choose the file that you just created to import, along with the root folder either 'Local Server Groups' in SSMS 2008 or 'Database Engine' in SSMS 2005.  You can also choose any other sub folder and then all groups and servers will be imported to the selected sub folder.  Click OK to process the import.

To import we use the same menu in SSMS that we used for the export, but select import instead

Next Steps
  • Once imported you can connect to any of the registered servers.
  • Cross SSMS import-export between SSMS 2005 and SSMS 2008 has some issues.  Read this article for further details.
  • Use the export option to create a backup of your registered servers.
  • Use any text or XML editor to modify the .regsrvr file.
  • You can also follow the same steps above to export and import one registered server.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

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




Wednesday, February 12, 2014 - 9:30:20 AM - Ryan Back To Top (29419)

This has been a problem for me for years.   We don't use windows authentication so we need to export with passwords.   If we do this, we can't import the server list anywhere.    Our only work around is to export without passwords and then manually add the username/passwords to every server which takes so long.

Why does SSMS allow you to export with passwords but never let you import with passwords????

Sunday, November 11, 2012 - 3:51:33 AM - shamsher Back To Top (20281)

thanks

Friday, December 23, 2011 - 2:26:04 PM - leon Back To Top (15440)

Hello Ruth, I had to do a search on my laptop as well, and this is where I located the"RegSrvr.xml" file, which I renamed, then reopened MS-SMS, imported an Export from another Client, but only works when saving WITHOUT the passwords, as will default to your Windows Authentication after Importing and navigating to each of the servers.

C:\Documents and Settings\<userID>\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml

 

Kind regards,

Leon

PS. Here is where I [finally] found it...

1. RegSrvr.xml – rename and reopen MS-SMS

 

 

http://sql.richarddouglas.co.uk/archive/2010/06/registered-servers-error-in-ssms.html

 

Solution

The way to solve the issue is to hunt down the file RegSrvr.xml and rename it. The problem is that the old file could not be read by the new installation of SQL Server, once this has been renamed or deleted the feature will work again.

I found the file in:
C:\Users\[USERNAME]\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml
but if you have a local profile you will no doubt find it at:
C:\Users\[USERNAME]\AppData\Local\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml

If you’re running SQL 2005, substitute the 100 directory for 90

 

Location of Local “RegSrvr.xml” file:

C:\Documents and Settings\e111829\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml

 

Wednesday, January 12, 2011 - 10:17:04 AM - Greg Robidoux Back To Top (12587)

If you are using an older version of Windows (not Windows 2008 or Windows 7) then try looking in this folder for the XML files mentioned above:

C:\Documents and Settings\[USERNAME]\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell

Wednesday, January 12, 2011 - 4:00:44 AM - Atif Back To Top (12584)

Hi Ruth,

I have tried the import process by even deleting the Shell folder and its contents. Import process got successful and it created Shell folder along with RegSrvr.xml and other content in it. By deleting RegSrvr.xml, registered servers may be lost but it does not harm the functionality of importing these later.

I think removing the previous service except the latest is not going to work.  Please consider couple of points

  • Cryptography class may stop working for any user if you have reset the password of windows login administratively  (recommended method is to change through Alt+Cntrl+Del). If you have mani***ted  windows login or security then also consider it that way.
  • Just for troubleshooting, if you can play with instance on your laptop then try to delete the Tools folder when SSMS is not open. And later open it again, Tools folder is expected to be created with mandatory files in it. It may reset any security issues on newly created files. 
  • Also consider that either there was any kind of encryption or security on file system from where you have picked the . This may  have corrupt, or may prevent the content access while trying to import. 

I would update you with any further progress on this issue and please share any progress on your side.

Thanks for provided details

Tuesday, January 11, 2011 - 11:25:11 AM - Ruth Back To Top (12571)

Hi Atif,

Thank you very much for getting back!

Please read my mail to MSSQLTips [email protected] as I can not send any attachment throug here.

Again, thank you very much for your help!

Ruth 

 

Monday, January 10, 2011 - 4:34:56 AM - Atif Back To Top (12549)

Hi Ruth,

It looks that the mentioned error message is dute to problem with system.security.cryptography class. Despite several trials i was not able to generate the error on my laptop, so you should also verify the updation of sql server patches and version. About cryptography error mssage, here is a discussion at msdn forum

If problem persists even on updated and patched instance then simply delete the registered servers from your registered servers folders. RegSrvr.xml should be there even if there are no registered servers on that instance. Searching for this file may be failed as it is present in hidden folder. Just confirm its presence manualy by going to the folder that you mentioned.

Also i would suggest to manualy register couple of servers and analyze the functionality through them. To look at the problem in more detail, please tell about the message and scenario where import of registered servers failed initially.

 

Thanks

Monday, November 29, 2010 - 2:19:17 PM - Ruth Back To Top (10405)

Hi Atif,

Thank you very much for the article, this is something I always wanted.

I've tried the method recently after  my laptop re-imaged.

I installed SQL 2008 client on my laptop and imported my registered servers (exported from SQL Server 2008)
The import failed.

Now I get the following error everytime I display registered servers.
I have uninstalled the sql server 2008 client and deleted all directores from my computer (Program Files and Program Files for Microsoft SQL Server), and installed the SQL server 2008 client again.
However, the error continutes and renders the Resisterd Servers window totally usless (i.e. there are no entires in the tree list of server types so I cannot add any).

See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.Security.Cryptography.CryptographicException: Key not valid for use in specified state.

   at System.Security.Cryptography.ProtectedData.Unprotect(Byte[] encryptedData, Byte[] optionalEntropy, DataProtectionScope scope)
   at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer.ProtectData(String input, Boolean encrypt)
   at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer.get_SecureConnectionString()
   at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer.get_ConnectionString()
   at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer.get_ServerName()
   at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServerTree.AddRegisteredServerNode(RegisteredServer regSrv, TreeNodeCollection nodes)
   at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServerTree.AddServerGroupToNodeCollection(ServerGroup group, TreeNodeCollection nodes, Boolean createRecursively)
   at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServerTree.AddServerGroupToNodeCollection(ServerGroup group, TreeNodeCollection nodes, Boolean createRecursively)
   at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServerTree.Initialize(ServerGroup localServerGroup, ServerType serverType, IServerType connDlgServerType, AddCentralManagementServerDelegate onAddCentralManagementServer, AddNewCentralManagementServerDelegate onAddNewCentralManagementServer, DeleteCentralManagementServerDelegate onDeleteCentralManagementServer, GetServiceProviderDelegate onGetService)
   at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServerControl.RegisteredServerControl_Load(Object sender, EventArgs e)
   at System.Windows.Forms.UserControl.OnLoad(EventArgs e)
   at System.Windows.Forms.UserControl.OnCreateControl()
   at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
   at System.Windows.Forms.Control.CreateControl()
   at System.Windows.Forms.Control.WmShowWindow(Message& m)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ContainerControl.WndProc(Message& m)
   at System.Windows.Forms.UserControl.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

----

I wanted to try to delete or rename the files below, but I can not find it from my laptop.

C:\Users\[USERNAME]\AppData\Local\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml

OR

C:\Users\[USERNAME]\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml

Can you please help me out?

Thank you very much for your help!!!

Ruth

Tuesday, May 25, 2010 - 2:04:07 AM - @tif Back To Top (5503)

@ssivaprasad.

As mentioned in the tip, Importing registerd servers from SSMS 2005 to SSMS 2008 has some issues. In following link it is injdicated by Microsoft that this issuue would not be fixed.

http://connect.microsoft.com/SQLServer/feedback/details/366121/ssms-2008-fails-to-import-sql-instances-registered-in-ssms-2005

"Unfortunately, this issue was triaged as "won't fix" since this is not a common enough customer scenario",

so you can not have any standard solution for this issue. Just try various solutions suggested by indivuals and some method may work fine in your scenario. You can look at http://blogs.msdn.com/b/buckwoody/archive/2008/08/18/importing-sql-server-2005-registered-servers-to-sql-server-2008.aspx

 Thanks


Monday, May 24, 2010 - 8:46:53 PM - ssivaprasad Back To Top (5495)

How to import Registered Servers on SQL Server 2005 to SQL Server 2008?

 

Monday, May 24, 2010 - 3:59:03 PM - Banyardi Back To Top (5484)

This is good stuff, and I have been using registered servers for years.  I leave my computer on all the time, but occasionally I have to reboot for one reason or another.  I have 25 registered servers that I maintain, and I like to open them in the Object Explorer in a particulare order... Prod/Test/Dev/ etc.  Presently, I have to open each server one at a time from the Registered Server Tab into the Object Explorer window, click on the Registered Server Tab again and so-on 25 times. 

How can I programmatically open my Registered Servers in the Object Explorer window, in a pre-selected order?  What I want to do is run a Powershell SMO Script that opens the registered servers in the order that I choose.  Is there anyone out there with a similar problem that has used this approach to solve?

 Cheers!














get free sql tips
agree to terms