Connection properties save location
-
Possibly a bit off topic, but does anyone know where the properties are saved when you create a connection to a SQL instance via SSMS? I like to set the colour of the connection based on function or location and am often disheartened when these properties are lost. If there is a file or registry setting somewhere then I could back it up and restore it at will.
- MikeRen - Tuesday, August 14, 2018 4:22 AM
I believe it is a Registry setting, but I cannot remember the key.
Try creating a new connection with a really unusual name and then search the Registry for that. Shouldn't take long.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Just remembered an easier way. You can export and import your connection settings from within SSMS.
Just right click 'Local Server Groups' in your Registered Servers window and the rest should be easy to work out.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Nice thought Phil, but unfortunately these aren't registered servers (I did use them for a while but my database names are different on most servers so I couldn't make a lot of use from them).
What I'm dealing with is when you connect to a Database Engine and press the 'Options >>' button:
I know it has to be saved somewhere since the settings are used the next time I connect, I just don't know where. -
OK. How about selecting a weird custom colour and then searching the Registry for its int value?
You can get the int value by setting up a registered server with the same custom colour, exporting as above and then viewing the exported file in a text editor:
<RegisteredServers:CustomConnectionColorArgb type="int">-16711936</RegisteredServers:CustomConnectionColorArgb>The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Phil,
I gave it a go (created a new registered server with a weird colour and exported the servers to get the code and then searched for that in my registry) but did not find it. I think therefore that the settings must be saved in a file somewhere but still have no idea where that might be.
Mike - MikeRen - Tuesday, August 14, 2018 5:15 AMNice thought Phil, but unfortunately these aren't registered servers (I did use them for a while but my database names are different on most servers so I couldn't make a lot of use from them).
What I'm dealing with is when you connect to a Database Engine and press the 'Options >>' button:
I know it has to be saved somewhere since the settings are used the next time I connect, I just don't know where.How to remove “Server name†items from history of SQL Server Management Studio is about fixing the decade+ old problem of the SSMS connection dialog suddenly forgetting passwords, but does indicate the location of the file that stores information from the connection dialog. It's a binary file @ C:\Users\%USERNAME%\AppData\Roaming\Microsoft\SQL Server Management Studio\XX.y\SqlStudio.bin (for SQLServer 2012+).
.
Given the flakiness of the connection-dialog data, & the binary data, & the reliability & ease of editing registered servers, I would also recommend using registered servers. This file is an XML file located @ %APPDATA%\Microsoft\Microsoft SQL Server\XXX\Tools\Shell\RegSrvr.xml (where XXX is the version number 80-140). I have no idea how passwords are encrypted. - MikeRen - Tuesday, August 14, 2018 5:15 AMNice thought Phil, but unfortunately these aren't registered servers (I did use them for a while but my database names are different on most servers so I couldn't make a lot of use from them).
What I'm dealing with is when you connect to a Database Engine and press the 'Options >>' button:
I know it has to be saved somewhere since the settings are used the next time I connect, I just don't know where.This probably doesn't help for your particular case, but the cached list of recent connections is stored in your user AppData in a file named "SqlStudio.bin".
For SSMS 2017, that'd be at C:\Users\<user ID>\AppData\Roaming\Microsoft\SQL Server Management Studio\14.0
Following link has some more info
This is what I've used this file for in the past...when SSMS conveniently "forgets" my recent connections, I've used this to restore the list.
Really, using "Registered Servers" is the better way to go...SSMS doesn't forget those and you can set color on the connection and it gets saved, and if you get a new version/install you can transfer your old registered ones to the new install fairly easily.
Your registered servers (as of SSMS 2017) are stored in AppData, in a file called RegSrvr.xml, that's here:
C:\Users\<user ID>\AppData\Roaming\Microsoft\Microsoft SQL Server\140\Tools\Shell
-
Ok, you've convinced me - I'll switch back to registered servers.
I started using them a while ago and liked the ability to run a script on multiple servers in one go. I also liked the fact that I could save them back to a central server which meant that other users could benefit from by configurations. Over time I've fallen out of the habit of using them as our Central servers were updated and the lists weren't migrated and the server landscape changed without list maintenance.
Part of my reason for wanting to know where the file was was so that I could take a backup for when the 'remembered' list got lost. If the Registered Servers list dosn't get lost as readily that'll solve my root issue.
Thanks,
Mike
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply