Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















Import and Export Registered SQL Servers To Other Machines

MSSQLTips author Atif Shehzad By:   |   Read Comments (11)   |   Related Tips: More > SQL Server Management Studio
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.


Last Update: 5/24/2010


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

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Monday, May 24, 2010 - 3:59:03 PM - Banyardi Read The Tip

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!

Brandon_Forest@sbcglobal.net


Monday, May 24, 2010 - 8:46:53 PM - ssivaprasad Read The Tip

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

 


Tuesday, May 25, 2010 - 2:04:07 AM - @tif Read The Tip

@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, November 29, 2010 - 2:19:17 PM - Ruth Read The Tip

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


Monday, January 10, 2011 - 4:34:56 AM - Atif Read The Tip

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


Tuesday, January 11, 2011 - 11:25:11 AM - Ruth Read The Tip

Hi Atif,

Thank you very much for getting back!

Please read my mail to MSSQLTips tip@mssqltips.com as I can not send any attachment throug here.

Again, thank you very much for your help!

Ruth 

 


Wednesday, January 12, 2011 - 4:00:44 AM - Atif Read The Tip

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


Wednesday, January 12, 2011 - 10:17:04 AM - Greg Robidoux Read The Tip

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


Friday, December 23, 2011 - 2:26:04 PM - leon Read The Tip

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

 


Sunday, November 11, 2012 - 3:51:33 AM - shamsher Read The Tip

thanks


Wednesday, February 12, 2014 - 9:30:20 AM - Ryan Read The Tip

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????



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.