What are the challenges you face when working across database platforms? Take the survey

Restrict Objects getting dropped accidently

Is there a way we can force the Deployment step to throw a warning or throw error if it finds any drop statements? This is to make sure there are no accidental "drops" / "deletes" going into production through pipeline .


  • Options
    You can use the abort flag from SQL Compare for high level warnings, which includes drops. It has other items, so you want to be careful  and experiment in QA/staging to understand the things you do that might trigger this. It might be too gross for you.

    What I'd probably do is look for the specific items I want to avoid and use a regex to scan the update.sql script. That way I'd get a note of problems. That being said, you need a way to get around this, or disable this at times, since I will almost guarantee that you will find exceptions over time.
  • Options
    There's more information on what Steve's talking about "AbortOnWarnings" at https://documentation.red-gate.com/sc13/using-the-command-line/switches-used-in-the-command-line.  Data loss is a high warning.

    There's information about using SQL Compare options in SQL Change Automation at https://documentation.red-gate.com/sca3/automating-database-changes/automated-deployments/using-sql-compare-options-with-sql-change-automation-powershell-module.  You can also put option parameters in the SQL Change Automation Build/Release extensions (TFS/VSTS/Octopus Deploy).
    Thank you!
    Stephanie M. Herr    :-)
    Product Manager Database DevOps
  • Options
    sasankjsasankj Posts: 23 New member
    I tried using it in my Deployment step, but it gave a warning saying  
    [warning]Cmdlet parameter SQLCompareOptions is not supported for SQL Change Automation projects. This option will be ignored.
    [warning]Cmdlet parameter AbortOnWarningLevel is not supported for SQL Change Automation projects. This option will be ignored.

    I am attaching a screen shot of my Deployment step and attaching release log.
  • Options
    sasankjsasankj Posts: 23 New member
    Did you get a chance to look into this?
  • Options
    Apologies, I haven't. Can I ask if this is a SQL Source Control project at the client or a SQL Change Automation in VS project?
  • Options
    sasankjsasankj Posts: 23 New member
    This is SQL Change Automation project in VS.
  • Options
    Ah, so in the release steps, there is no analysis of the code, just a review of which migration scripts need to be deployed. The scripts are already set and the parsing of changes is done in development to build those scripts. A pre-deployment to intermediate environments is what should be used to catch this type of issue.

    I'm looking for something that would help here, but I think this is problematic from our view in that we do perform some drops for procedures in some scripting. Anything that's pre-SQL2016 can't handle CREATE OR ALTER, and the standard pattern we have is: if exists() drop, create.

    I think that the solution I'd lean towards here is to fail builds that contain drops, not wait until the release stage. My initial thought is to capture a list of tables and store this in my db. This would essentially be a way of auditing the state of the db. If you maintained this in your existing prod (and qa/staging/UAT, etc), then you'd know what objects exist. After a build, I'd run a left join from this table to sys.objects, and if things were missing, I'd then fail the build or return an error to the user. This would help me detect drops.

    You could easily expand this type of check into views, procs, functions, users, etc.
Sign In or Register to comment.