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

Redgate SQL Change Automation: Build - Predeploy executes after temporary database is dropped

I must be missing something pretty basic here.

I've got a SQL Source Control project that I'm building using Redgate SQL Change Automation: Build.

it builds the database (using a temporary name), and then drops it before pre or post deploy script are run?

in my case the pre-deploy just adds a couple of rows to a table.   but the table no longer exists.  Obviously, this breaks the build as there are no objects or even a database?

what am I missing?


  • Options
    ptrubsptrubs Posts: 15 Bronze 3
    edited December 6, 2021 10:33PM
    a little bit more information.   I only had a pre-deploy script when I came across this problem.   I moved the script to the post deploy and the build was successful.   I can see the temporary database was dropped after the post deploy ran.

    Could it be a bug in Redgate SQL Change Automation: Build that occurs only when there is a pre-deploy script?
  • Options

    Thanks for your query about SQL Source Control. By default the temporary database should be created first, then the pre-deployment scripts would be deployed followed by the schema objects and lastly the post-deployment script. Are you able to provide a copy of what you included in the pre-deployment script? Also, do you have a copy of the log files for this build?

    Kind regards,

    Pete Ruiz

  • Options
    ptrubsptrubs Posts: 15 Bronze 3
    OK - it seems your explanation is what actually happens (and my original hypothesis was wrong) - but the logic still causes issues.

    if the temp db is created first  - it will be empty
    The pre-deploy script will run - if it references any object in the database, it will fail
    the schema will be updated to the latest version

    Which I think now answers my question, and raises other issues - please confirm

    if the pre-deploy references any object, you have to check for existence of that object before using it in the pre-deploy, whether it be a table, proc, view etc, just so the build step will work...

    is that in the documentation anywhere you can point to?
  • Options
    Hello @ptrubs

    What sort of objects did you add the pre-deployment script and why did you choose to use a pre-deployment script in this way?

    Can you please send over a copy of the pre-deployment script for review?

    Kind regards,

    Pete Ruiz

  • Options
    ptrubsptrubs Posts: 15 Bronze 3
    pre-deployment script?
      insert into dbo.x values ('y')

    The above statement fails during the build phase - because the db has no objects in it when the pre-deploy is execute.

    Why use the pre-deployment script for this?   
    There is nothing explicit in your documentation (that I've found) that states what should not be included in the pre-deploy

    I personally don't think I would the pre-deploy for this - but I can imagine use-cases that might require it, I'm just trying to find the explanation why this pattern failed for one of my developers (who has updated to use the post-deploy) - and if it is required - the work around (ie - test for existence)

  • Options
    Hello @ptrubs,

    The default behavior is that the the Pre-Deployment script is executed before the section of the script that contains the schema changes generated by SQL Compare, and the Post-Deployment script is executed after. This means that it would probably make more sense to include any INSERT statements in a Post-Deployment script rather than a Pre-Deployment script.

    I hope this helps. Cheers,

    Pete Ruiz

    Redgate Product Support

Sign In or Register to comment.