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.
Jeremy Kadlec is a Founder, Editor and Author at MSSQLTips.com with more than 300 contributions and 25+ years of SQL Server experience. Jeremy leads a team of more than 300 authors helping millions of SQL Server professionals around the globe every second of the day for the last 20 years. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP based on his community contributions. Jeremy brings 25+ years of SQL Server DBA and Developer knowledge to the community and holds a bachelor’s degree from SSU and master’s degree from UMBC.
