Microsoft SQL Server Management Studio (SSMS) is a powerful tool for managing and developing on SQL Servers. It offers several features that can save you time and effort when connecting to SQL Server instances. In this article, we will explore two key features: the Connect to Server Box Dropdown and Registered Servers, and discuss how to back up and migrate the connection information stored in these features.
Connect to Server Box Dropdown
When you connect to a SQL Server instance using SSMS, the connection information is automatically stored locally. This allows you to easily access previously connected servers from the Server name dropdown in the Connect to Server box. The location and file format of the stored connection information vary depending on the version of SSMS you are using. Here is a table that 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 |
If you want to delete all the server names from the list, you can simply delete the corresponding file in the folder that matches your SSMS version. The file will be recreated with an empty list when SSMS starts.
Registered Servers
Registered Servers is another useful feature in SSMS that allows you to organize SQL Server connection information into groups. Unlike the automatic saving of connection info in the Connect to Server Box Dropdown, you need to manually enter the information under Registered Servers. To access Registered Servers, you can go to View > Registered Servers or use the Ctrl+Alt+G keyboard shortcut.
To create a new Registered Server group, you can right-click on the Database Engine, select Local Server Groups, and then choose New Server Group. You can enter a name and description for the group. To add a new server registration, right-click on the group, click New Server Registration, and enter the server name in the Server name box. You can test the connection and save the registration. Repeat these steps to add more servers to the group.
Exporting and Importing Registered Servers
If you have a large list of Registered Servers that you want to back up or migrate to another machine, SSMS provides built-in Export and Import tasks. To export all items, right-click on the top group, select Tasks, and choose Export. Choose a folder to export to and name the file. By default, the export file does not include user names and passwords for SQL Authentication logins. You can choose to include them, but keep in mind that the saved credentials will be accessible to anyone who imports the file.
To import the Registered Servers, right-click on the top group, select Tasks, and choose Import. Navigate to the folder where the .regsrvr file is located and select it. The groups and servers will be restored.
By understanding how the connection information is stored and utilizing the features in SSMS, you can easily back up and migrate your SQL Server connections. Whether you prefer using the Connect to Server Box Dropdown or Registered Servers, SSMS provides convenient options to manage your connections efficiently.
Article Last Updated: 2022-06-15