How to Import and Export Connections from SQL Server Management Studio

By:   |   Updated: 2022-06-15   |   Comments (2)   |   Related: > SQL Server Management Studio


Problem

Microsoft SQL Server Management Studio (SSMS) is a feature rich environment to manage and / or develop on SQL Servers. There are two related features that save you from having to reenter connection information every time you connect to a SQL Server. The first is SSMS automatically stores SQL Server connections you've successfully made and are accessible from the Server name dropdown in the Connect to Server box. The other way is a very handy feature called Registered Servers where you have the ability to create organized groups and lists of SQL Servers.

You use SSMS and make use of one or possibly both of these features and would like to know where the connection info is stored and how to back it up or migrate it to another installation of SSMS.

Solution

Each of these features are closely related function-wise, but work quite differently so we're going to look at each, one at a time.

Connect to Server Box Dropdown

SSMS automatically stores the connection info for any SQL Server instances you've connected to. When you start SSMS or click Connect in Object Explorer you're presented with the Connect to Server box.

Connect

The list of SQL Servers are visible by clicking the Server Name dropdown.

'Server name' Dropdown

And here we see two SQL Server names, SERVER1 and SERVER2.

List of Servers

The connection info is stored locally in a file. The location and file format of where it's stored is different based on what version of SSMS you're using. This table shows the file name and location for each version.

SSMS Version File
18 %APPDATA%\Microsoft\SQL Server Management Studio\18.0\UserSettings.xml
17 %APPDATA%\Microsoft\SQL Server Management Studio\14.0\SqlStudio.bin
16 %APPDATA%\Microsoft\SQL Server Management Studio\13.0\SqlStudio.bin
2014 %APPDATA%\Microsoft\SQL Server Management Studio\12.0\SqlStudio.bin
2012 %APPDATA%\Microsoft\SQL Server Management Studio\11.0\SqlStudio.bin
2008 %APPDATA%\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin
2005 %APPDATA%\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

Note: If you're simply trying to delete all the server names from the list you can just delete the file in the folder that matches your corresponding SSMS version. It will be recreated with an empty list when SSMS starts.

SSMS 18 x supports all supported SQL Server versions of SQL Server so that's what is used in this tip. The following examples were created using SSMS 18.11.1 which is the latest version as of this writing.

When you successfully connect to a SQL Server with SSMS 18.x the connection info is stored in the file %APPDATA%\Microsoft\SQL Server Management Studio\18.0\UserSettings.xml. If we open the file, we'll see SERVER1 and SERVER1 between the <Instance> tags. Note, this is only applicable to SSMS 18.x and up. Connection info in earlier SSMS version isn't stored in text files.

ssms user settings xml file

This file could be used to migrate the server list to another machine if you wanted to. However, if you are connecting to more than a couple of SQL Servers, it would be much more practical to use Registered Servers and export out a list that can be imported elsewhere. We'll look at how to do that in the next section.

Delete Unwanted Servers in List

You may be tempted to edit this file to clean up any old or unwanted entries in the list. Here's an easier way to do it.

Simply hover over the SQL Server name you want to delete with your mouse pointer and hit the Del key. I was able to test successfully going back as far as SSMS 2012.

Deleting Entries

And the highlighted server entry is gone.

Edited List

Register Servers

What are Registered Servers? If you're unfamiliar with this very useful piece of functionality in SSMS, it's simply an organized list of SQL Server connection information stored in SSMS. It lets you organize SQL Servers by group and run a query against every member of a group. Unlike the automatic saving of connection info when you successfully connect, you need to enter your info under Registered Servers.

To open Registered Servers

  1. View
  2. Registered Servers

Or alternatively use the Ctrl+Alt+G keyboard shortcut.

Open Registered Servers

To illustrate, we'll create a Registered Server group.

  1. Expand Database Engine and right click on Local Server Groups
  2. New Server Group…
Registered Servers
  1. Name Group
  2. Enter a description (optional)
  3. OK
Add Group

Next, we'll add a new Registration.

  1. Right click on group
  2. Click New Server Registration…
Register Server
  1. Enter server name in Server name box
  2. Test to test connection
  3. Save

Repeat steps to add more servers.

Add Server

Here is my MyServers group with SERVER1 and SERVER2.

Registered Servers List

Export Registered Servers

If you only had two Registered Server like here in our example it would be easy enough to just re-enter them. But what if you have an extensive list of every SQL Server you work with, and it's organized just the way you want it? It would be inconvenient, time consuming and just plain annoying to have to re-enter them all in the event of a failure or just to move it to another machine.

Not to worry, this problem is easily solved with the built in Export and Import Registered Server Tasks.

  1. Right click on the top group to export all items
  2. Tasks
  3. Export…
Export Group
  1. Ellipse (…)
Choose Export File
  1. Choose folder to export to (I chose OneDrive just so it's backed up)
  2. Name file
  3. Save
Export File Location
  1. OK
Complete Export

Note: "Do not include user names and passwords in the export file" is enabled by default and refers to SQL Authentication logins and passwords. Unchecking the box will save the login and password with the password encrypted. However, anyone who could import the file would have access with the saved credentials.

Import Registered Servers

To simulate reinstalling SSMS after a failure or to move migrate the Registers Servers to another machine, I've deleted the group and here we see it's gone.

Empty List

To restore

  1. Right click on the top group
  2. Tasks
  3. Import…
Import
  1. Ellipse(…)
File to Import
  1. Navigate to folder
  2. Select .regsrvr file to import
  3. Open
File Location
  1. OK
Complete Import

And your Groups and Servers are back.

List is Imported
Next Steps
  • We've seen how to work with the list of server names in the Server name dropdown in the Connect to Server box and how to export and import a list of Registered Servers in SSMS.
  • This link will bring you to an extensive list of SSMS Tips: SQL Server Management Studio Tips



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips


Article Last Updated: 2022-06-15

Comments For This Article




Monday, June 20, 2022 - 12:32:19 PM - Joe Gavin Back To Top (90173)
Todd, I typically would only login to SQL Servers with AD auth so the export/import would work. I'd doubt what you're hoping for would get changed.

Monday, June 20, 2022 - 9:02:46 AM - Todd Chittenden Back To Top (90171)
I tried EXPORTING my Registered Servers list from one machine and IMPORTING it into another. All the SQL Authentication passwords were hosed. Turns out, the file was encrypted with a Machine Key, so the IMPORT could only be decrypted on the same machine. Total epic failure. I put in a User Voice feedback item about this but have not heard anything back.

In your recent experience, would I be able to accomplish this now (June of 2022)?
Thanks


download














get free sql tips
agree to terms