SCA doesn't create new schema in an existing database
ode2joy
Posts: 2 New member
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
TIA
Best Answer
-
StephanieHerr Posts: 174 Gold 1Hi 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!Thank you!
Stephanie M. Herr :-)
Product Manager Database DevOps
Answers
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