Trick to remember which instance I am working with

Question

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?

Answer 1

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 or not. There are other options also at user interface(UI) level, you may choose different coloring specifications for different options, for instance dev in green, UAT in Yellow and Production in Red. Color code can be configured following below links

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.

Answer 2

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

Answer 3

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

Answer 4

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:

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