Post-update schema check failed: the schema in the Database Release object

Bas_K

Hi,

We use SQL Change Automation for some years in combination with Octopus. Normally no problem, but now suddenly it has an problem. On one of the stages we have an problem with the database. 

Our current release does not have any database changes and our test en acceptation enviroments are OK.
But now we are deploying to our demo enviroment this is the step before production. It is a pretty simple and light database.

I do not have a clue why this problem occurs. We have done some manual compare and Data compare with the redgate desktop tooling. But no result.

Does anyone have an idea?

Update script successfully applied to database 'App_Demo' on server 'x.x.x.131'.

July 30th 2019 14:10:22 Info Checking target database schema is in expected state:

July 30th 2019 14:10:22 Info Comparing the schema in the Database Release object (created at 7/30/2019 2:09:10 PM +02:00) to the current schema of database 'App_Demo' on server 'x.x.x.131'.

July 30th 2019 14:10:22 Info VERBOSE: Calling: C:\Octopus\Work\20190730120853-74937-428\Modules\SqlChangeAutomation\SC\SQLCompare.exe

July 30th 2019 14:10:22 Info "/filter:C:\Users\ik\AppData\Local\Temp\DLM Automation\0nxx15ku.scpf"

July 30th 2019 14:10:22 Info /options:ConsiderNextFilegroupInPartitionSchemes,DecryptPost2kEncryptedObjects,DoNotOutputCommentHeader,ForceColumnOrde

July 30th 2019 14:10:22 Info r,IgnoreCertificatesAndCryptoKeys,IgnoreDatabaseAndServerName,IgnoreTSQLT,IgnoreUsersPermissionsAndRoleMemberships,Igno

July 30th 2019 14:10:22 Info reUserProperties,IgnoreWhiteSpace,IgnoreWithElementOrder,IncludeDependencies,NoDeploymentLogging,ThrowOnFileParseFailed

July 30th 2019 14:10:22 Info ,UseCompatibilityLevel,IgnoreFillFactor,IgnoreFileGroups

July 30th 2019 14:10:22 Info /DataCompareOptions:IgnoreSpaces,IncludeTimestamps,IncludeIdentities,DisableKeys,OutputComments,ReseedIdentity,MissingF

July 30th 2019 14:10:22 Info rom2AsInclude /include:staticData /OutputWidth:1024 "/scripts1:C:\Users\ik\AppData\Local\Temp\DLM

July 30th 2019 14:10:22 Info Automation\r2fm35ob.cvb" /server2:x.x.x.131 /database2:App_Demo /username2:App_Demo /password2:********

July 30th 2019 14:10:22 Info /out:"C:\Users\ik\AppData\Local\Temp\DLM Automation\ejvbm0eg.log"

July 30th 2019 14:10:22 Info VERBOSE: C:\Octopus\Work\20190730120853-74937-428\Modules\SqlChangeAutomation\SC\SQLCompare.exe ended with exit code 0

July 30th 2019 14:10:22 Info VERBOSE: SQLCompare.exe completed successfully.

July 30th 2019 14:10:22 Error Use-DatabaseReleaseArtifact : Post-update schema check failed: the schema in the Database Release object (created at 

July 30th 2019 14:10:22 Error 7/30/2019 2:09:10 PM +02:00) and database 'App_Demo' on server 'x.x.x.131' are different, or static data is

July 30th 2019 14:10:22 Error different.

July 30th 2019 14:10:22 Error At C:\Octopus\Work\20190730120853-74937-428\Script.ps1:290 char:12

July 30th 2019 14:10:22 Error + $release | Use-DatabaseReleaseArtifact -DeployTo $targetDB -SkipPreUpdateSchemaC ...

July 30th 2019 14:10:22 Error + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

July 30th 2019 14:10:22 Error + CategoryInfo : InvalidResult: (:) [Use-DatabaseReleaseArtifact], TerminatingException

July 30th 2019 14:10:22 Error + FullyQualifiedErrorId : SchemasAndStaticDataAreNotIdentical,RedGate.Versioning.Automation.PowerShell.Commands.Us

July 30th 2019 14:10:22 Error eDatabaseReleaseArtifactCommand

July 30th 2019 14:10:23 Fatal The remote script failed with exit code 1

Thanks in advance!!

Bas_K

Can someone help me?

Sergio R

These can have various causes. To troubleshoot this more accurately I suggest that you edit the SQL Change Automation Octopus Deploy template and add the following at the start:

$DebugPreference = 'Continue'

$VerbosePreference = 'Continue'

This should provide more detailed logs and should tell you what are the objects that are causing this to fail.