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 |
