Problem
SQL Server Management Studio is a very useful tool to administer SQL Server instances. With SQL Server Management Studio installed on your machine or server, you can connect to many SQL Server instances and work on them in a single interface. SQL Server Management Studio caches the instances that you recently connected to in the Server Name drop down list in the Connect to Server window. Is it possible to remove some of the instances, clear the list or take a backup of this list in order to use it on another machine? Check out this tip to learn more.
Solution
SQL Server Management Studio (SSMS) caches the list of servers that you recently connected to. You can easily add a new server to the list by connecting to that server from Management Studio. In this tip, we will demonstrate how to:
- Delete a SQL Server instance from the Connect to Server drop down list in SSMS
- Clear all SQL Server instances from the Connect to Server drop down list in SSMS
- Move the SQL Server instances from the Connect to Server drop down list in SSMS to another machine
Delete a SQL Server instance from the Connect to Server drop down list in SSMS
In order to delete a server from the list, open SQL Server Management Studio and start the Connect to Server window, then click on the Server Name drop down list as shown below:
Use the arrow keys on your keyboard to select the server name that you need to remove from the list:
Once you highlight the instance (i.e. shaded blue), click on the Delete button from your keyboard and the instance will be removed from the list (the bottom instance in the screen shots):
Clear all SQL Server instances from the Connect to Server drop down list in SSMS
In order to clear the list using the method from the previous section, you would need to go through all the servers one by one and delete them, as there are no multiple selection options.
A better way to drop the entire list at once is to clear the instances from the file (i.e. SqlStudio.bin) where SQL Server Management Studio saves the data.
In SQL Server 2012 and later, the file (i.e. SqlStudio.bin) can be found in the following path in the user’s profile. Where the last number indicates the SQL Server Management Studio version, 12.0 here indicates SQL Server 2014:
- %UserProfile%\AppData\Roaming\Microsoft\SQL Server Management Studio\12.0
You should close Management Studio before working on the file.
Open the folder then add an underscore as a prefix to the SqlStudio.bin file as shown below:
Now, when you open SQL Server Management Studio, a new SqlStudio.bin file will be created with no instances populated in the drop down list as shown below:
Move the SQL Server instances from the Connect to Server drop down list in SSMS to another machine
To move the SQL Server instances from the Connect to Server drop down list in SSMS to another machine all you need to do is copy the SqlStudio.bin file as a backup and paste it on another machine in order to use the same instance names.
Next Steps
- You should make sure that you really need to drop the entire list by renaming the SqlStudio.bin file prior to making changes.
- Read more SQL Server Management Studio Tips
Ahmad has a Bachelor’s Degree in Computer Engineering from the University of Jordan and five years of experience working as a SQL DBA, gaining valuable knowledge of database structures, practices, principles and theories. His experience also includes.NET development, working with database applications, scripting and creating SQL queries and views. His personal abilities include having very strong communication and interpersonal skills, the ability to prioritize and to make good sound decisions that benefit the company. He has experience in upgrading, configuring, securing, tuning and monitoring SQL Servers since SQL Server 2005. This includes SQL Server performance tuning, SQL Server resource governor management, SQL Server maintenance plans, SQL Server data collection (Reports) analyzing and SQL databases design, developing, indexing and query optimization. In addition, he is familiar with installing and configuring SSRS, SSIS and SSAS. When it comes to disaster recovery and high availability, he has a solid foundation in SQL backup and recovery scenarios, mirroring, replication, log shipping, SQL clustering and AlwaysOn technology.
- MSSQLTips Awards: Author Contender – 2016-2017 | Trendsetter (25+ tips) – 2016 | Rookie Contender – 2015
