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

ptrubs

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?

ptrubs

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

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?

Pete_Ruiz

@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

ptrubs

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?

Pete_Ruiz

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

ptrubs

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)

Pete_Ruiz

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