How can we help you today? How can we help you today?

SCA doesn't create new schema in an existing database

So I'm trying to bring an existing production database under source control.

I created a Visual Studio project and successfully created a baseline from the production database in my local environment.  The db didn't already exist locally so was correctly created by SCA.

When I try and release via Azure DevOps to our "Build" environment (where an older version of the database already exists), the release fails.  It's because the production environment (and therefore my baseline) has a new schema and tables that don't exist in the Build environment.

Msg 2760, Level 16, State 1, Server BUILDSVR, Procedure sp_DoStuff, Line 17
The specified schema name MyNewSchema either does not exist or you do not have permission to use it.

I haven't been able to find any info about this problem online - Any idea what I'm doing wrong? Or is this a known problem that may have been fixed in a later version?   I'm using SCA version 14.10.8.22508.  I know v15 has been out for a while but - unless it fixes this specific problem - I'm not inclined to upgrade as, in all other respects, v14 works just fine.

TIA
ode2joy
0

Comments

3 comments

Sort by
  • EricLamontagne
    Hello ode2joy, 

    Your baseline cannot be more recent than the db you want to migrate/upgrade.

    You'll need to have a baseline based on the version of the db (or even older I guess) and then add migration scripts to upgrade it to the latest version.

    Eric
    EricLamontagne
    0
  • StephanieHerr
    Hi ode2joy,

    Can you create the schema in your build environment to workaround this?

    Or, you may want to create a pre-deployment script that checks if the schema exists?  If not, then add it.  https://documentation.red-gate.com/sca/developing-databases/concepts/migrations/pre-deployment-and-post-deployment-scripts 

    Thanks!
    StephanieHerr
    0
  • ode2joy
    Hi Stephanie, many thanks for your reply.  I realised subsequent to my original post that the problem is wider than just schemas - it seems that any new objects were not being added by SCA.  We worked around it in the end but I think your suggestion of creating a pre-deployment script would probably have sorted the issue.
    ode2joy
    0

Add comment

Please sign in to leave a comment.