Provisioning and Baselining scripts not run

As a PoC, I setup a simple database in SQL Source Control that using SCA for migrations and deployments. I was able to create the migration and baseline scripts. I am trying to deploy this using Octopus Deploy. During deployment I find that if the DB doesn't exist the provisioning scripts aren't being run to create the database, instead I get an error ResourceUnavailable: Couldn't connect to the database specified by the InputObject parameter: Cannot open database "TestingCI" requested by the login. The login failed. This obviously because the database doesn't exist yet. It happens in the Redgate - Create Database Release step that uses a Redgate step template for OD. 

If I manually create an empty database on the target server, it is able to connect and deploy the migrations but does not run the baseline script present in the first folder 1.0.0-Baseline. I've followed your documentation on ensuring the baseline settings are correct from here, but that doesn't seem to resolve the issue. 

My questions are:
1. how do I get the provisioning script to run and create the new database if it doesn't exist as part of the deployment?
2. how do I get the baseline script to execute on a newly created empty database?

Thank you!

Answers

  • Hello Karthik,

    Thanks for reaching out and sorry that you're having trouble with SQL Change Automation.

    1. how do I get the provisioning script to run and create the new database if it doesn't exist as part of the deployment?
    If using SQL Change Automation projects, default provisioning settings, I would expect the database should be created. We'll need to see the project in order to try and troubleshoot why this is happening.

    2. how do I get the baseline script to execute on a newly created empty database?
    The baseline script should run on a newly created database, if it doesn't it's probably because it "thinks" the target database is not empty. Can you verify if the target is an empty database?

    3. how to enable post-schema check on deployment?
    Not sure if the post-schema check is run on SCA projects as this is different from Source Control projects. I'll need to check with the team since there's also no option to enable this in the setting.

    Can you please provide a copy of your SCA project for review?

    Kind regards,

    Peter Ruiz
    Product Support Engineer


Sign In or Register to comment.