UPDATE: Microsoft’s acknowledged the bug…
We had a scenario (several weeks back) where a co-worker was connecting/changing connection between different sql servers and ended up in a state where the Microsoft SQL Server Management Studio said he was connected to one server however when executing queries it was connected to a different server.
This issue has been in the back of my mind ever since as a “what if this happens when connected to a production database and not my local dev box” issue. (WARNING: SERIOUS DATA CORRUPTION MAY OCCUR, when connected to the wrong server unknowingly)
NOTE: Luckily, we recently gave read-only rights to our dev AD user accounts so this situation couldn’t happen. I know, I know, “why didn’t we have that from the beginning?”……It happens, anyway…
Well, today I had the issue when working between my Dev box and our Test server and it took another developer (Thanks Keith Craig from Vertigo Software) to help me figure out the data I was looking at was not actually the data from the server I thought I was connected to.
Screenshot of the problem:
How to get into that state…
- Start with a query connected to a remote database.
NOTE: The two servers in red are the same. (Yay, we’re in a good state)
- Click the “New Query” button to open a new query window.
- Right click in the new empty query window, while it’s loading, and you (might) get the option to Connect. (if you’re fast enough)
NOTE: This happens if you right click before the window finishes initializing it’s connection to the original server (in this case the remote database server). Usually this dialog gives you the “Change Connection” option when connected, but if you’re fast enough you can get the “Connect” option and the “Change Connection” will be disabled.
- Click “Connect” and choose to the new desired server. In this case I’m going to connect to (local)
- That’s it, I’m now connected to the original server FILES, even though it says I'm connected to the server entered in the dialog above (local).
Current Work Around to Avoid this Problem:
To avoid the issue, wait till the query window has time to finish it’s initialization and has connect to the previous server. Then you can Right-Click –> Connection –> Change Connection…
Hope this helps someone else… because, like I stated earlier… (WARNING: SERIOUS DATA CORRUPTION MAY OCCUR, when connected to the wrong server unknowingly)