By: Jeremy Kadlec | Comments (2) | Related: > TSQL
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 |
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.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
