Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Preventing TSQL code from running on a Production SQL Server


By:   |   Updated: 2007-05-10   |   Comments (2)   |   Related: More > T-SQL

Problem
I have T-SQL code that I cannot have run on my production SQL Server.  How can I be sure that it does not run in production by mistake from a programmatic perspective rather than from a process perspective?  The reason I ask is because if the code ran in production, it would take us hours to get back up and running because we would need to go to our backup and then perform a number of restores.  The code that I am concerned about is responsible for purging particular data.  Although it is needed across our databases due to internal business requirements, I need a way to protect our production environment from this code being executed by mistake.  Any thoughts?

Solution
First and foremost, the best way to handle this item would be via a process, but if you need a quick and dirty option then check out some of them below:

  • SQL Server Instance Name
  • Database Name
  • Time Period

SQL Server Instance Name

If you have T-SQL code that would cause issues if it is run on your production SQL Server, then capturing the SQL Server name and issuing a RETURN command should do the trick.  What is necessary is to change your scripts and stored procedures to incorporate the appropriate logic.  Below are some potential examples:

-- Option 1
IF @@SERVERNAME = 'ProdSQL'
BEGIN
     PRINT '*** This code will NOT execute ***'
     RETURN
END

-- Option 2
IF @@SERVERNAME <> 'ProdSQL'
BEGIN
     PRINT '*** This code will NOT execute ***'
     RETURN
END

-- Option 3
IF @@SERVERNAME = 'ProdSQL'
BEGIN
     PRINT '*** This code will NOT execute ***'
     RETURN
END

ELSE

BEGIN
     PRINT '*** This code will execute ***'
END

Database Name

The same type of logic can be used if you have databases named by environment.  For example, your financial database is called Financial_Prod in your production environment, Financial_Test in the test environment, etc. then check out the following:

-- Option 1
IF DB_NAME() = 'Master'
BEGIN
     PRINT '*** This code will NOT execute ***'
     RETURN
END

-- Option 2
IF DB_NAME() <> 'Master'
BEGIN
     PRINT '*** This code will NOT execute ***'
     RETURN
END

-- Option 3
IF DB_NAME() = 'Master'
BEGIN
     PRINT '*** This code will NOT execute ***'
     RETURN
END

ELSE

BEGIN
     PRINT '*** This code will execute ***'
END

Time Period

If specific code cannot be run during specific time periods i.e. days of the week or hours of the day and for some reason it is not running via SQL Server Agent Job, then here is an option to prevent the code from executing on Saturday and Sunday:

-- Option 1 - If it is Sunday or Saturday, do not execute the code
IF DATEPART(dw,GETDATE()) = 1 OR DATEPART(dw,GETDATE()) = 7
BEGIN
     PRINT '*** This code will NOT execute ***'
     RETURN
END

Additional Information

To learn more about some of these commands, check out the following resources:

Next Steps

  • Identify code that would be problematic to issue in your production environments and consider changing your processes to prevent this code from being promoted to the incorrect environments.  If for business or application reasons that is not feasible immediately, then consider some of the options listed in this tip.
  • Depending on the exact circumstances of your environment dictates exactly how to use the commands listed in this tip in your T-SQL code.  You have a number of options with the native T-SQL functions and a fair amount of flexibility with the conditional logic.
  • Be sure to test and document the code thoroughly when you make a change like preventing code from executing.  If you or someone on your team skip a line or two of code when troubleshooting the code, then the analysis process can be long and frustrating.


Last Updated: 2007-05-10


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, October 20, 2015 - 2:03:21 PM - Christopher Grimaldi Back To Top

I have a similar issue, but I thought of creating a table in the master database (or somewhere) to store the environment name/code.  Then all my sql code can call a function to check that environment and do the appropriate work.  This would also aleviate any issues with upgrading servers where the servername changes all the time.


Monday, December 16, 2013 - 10:54:36 AM - Jeff Stevenson Back To Top

Nice script, thanks for sharing!


Learn more about SQL Server tools