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

• 
  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?

  ptrubs December 06, 2021 22:32

  - 0 +
• 
  @ptrubs
  

  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
  

  
  
  

  Pete_Ruiz December 09, 2021 01:24

  - 0 +
• 
  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
  post-deploy...
  
  
  
  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?

  ptrubs December 09, 2021 21:21

  - 0 +
• 
  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
  

  
  

  Pete_Ruiz December 11, 2021 00:27

  - 0 +
• 
  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)
  
  

  ptrubs December 12, 2021 20:54

  - 0 +
• 
  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
  

  
  

  
  

  
  

  
  

  
  
  
  

  Pete_Ruiz January 26, 2022 19:01

  - 0 +
• 
  I've just come across this issue again, and the salient part of this problem (that may not be clear from the above) is that this issue occurs in the BUILD task  (rather than the release task) - So the pre-deploy script has to be valid for an empty database (ie a database with no objects in it)

  ptrubs October 16, 2024 22:34

  - 0 +

Add comment

Please sign in to leave a comment.