6

I work as a BI developer and always switch between different instances on a same server. However, sometimes I forget the right instance and for example may cause to truncate a table on the other. I know this is not a technical question but rather experimental. I would be so thankful if you , dear DBAs, could tell me what do you do to always work on the right instance?

12

Since you are working on different instance on the same server, first thing would be to see the header of SSMS as it mentions server name along with instance name as well as port number(if it is specified explicitly).

In case, you want to address this through query, you may use below commands to check server name as well as instance name:

select @@SERVERNAME -- server name\instance name
go

select serverproperty('instancename') -- instance name
go

You may make this query as your first query to check whether you are at right instance. There are other options also at UI level, you may choose different coloring specs for different options, for instance dev in green, UAT in Yellow and Production in Red. Color code can be configured following below:

https://solutioncenter.apexsql.com/how-to-set-different-connection-colors-in-ssms/

https://www.mssqltips.com/sqlservertip/1871/customizing-the-status-bar-for-each-sql-server-ssms-connection/

Hope this helps.

13

You can colour code the status bar in SQL Server Management Studio Easiest if you use the Registered Servers or Central Management Servers feature and then allocate colours at a group level i.e. Dev (Green) / Test (Yellow) / Prod (Red)

https://documentation.red-gate.com/sp/ssms-tab-management/working-with-ssms-tab-management-quick-ref

btw You may need to spell it color to get a match

2
  • 2
    Add to your answer that that this is a third-party commercial product (Red Gate SQL Prompt) and not available in a default SSMS installation. – Dan Guzman Mar 2 at 10:56
  • This is very much available in vanilla SSMS - If you add the instance into a Registered server, select Properties > Connection Properties, there is a a 'Use Custom Color:' check box and 'Select' window which will allow you to chose the colour for the status bar! – SeanR Mar 3 at 10:15
4

If you are working with script files, then you can have the habit of adding something like below in the beginning of the file. I frequently have "scratchpad" files, never meant to execute everything. I.e., I always select the SQL to be executed and want to protect when I by mistake don't mark any text (and hence execute everything).

So, I just add a RAISERROR at the top to terminate the connection. You can of course condition this with an IF to check instance name (using SERVERPROPERTY()) or whatever your needs are. Thing is, you need to be sysadmin to use the combo of options to RAISERROR so it terminates the session. There's no "good" way to terminate a session/script AFAIK, that doesn't resort to this type of ugliness.

Below check the login name, but, as I said, adapt to your own needs.

--Verify the login
IF ORIGINAL_LOGIN() <> 'Kalle'
  RAISERROR('Nej du, din rackare', 21, 1) WITH LOG --Terminates session
GO
1

Brent Ozar once said you have to:

Go to Tools > Options >Text Editor > Editor Tab and Status Bar > Tab Text, and set it as in picture:

enter image description here

You will now see where are you pointing the gun, is right at the top of your tab in a yellow strip

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.