This error can occur when Deploying a Database Release (which corresponds to the Use-DatabaseReleaseArtifact if using Powershell)

On this step, SQL Change Automation will deploy an update script generated on the Create Database Release step (corresponds to New-DatabaseReleaseArtifact). Alternatively, this can also happen on an operation that combines Release Creation and Deployment.

After the update is deployed SQL Change automation compares the scripts folder States/Source in the Deployment Resources with the Target Database using SQL Compare and outputs this error if the scripts folder is not identical to the Target Database.

This check compares both the schema and static data.

Changes aren't rolled back when this error occurs.

If you know what's causing this and you want to ignore this error you can use the -SkipPostUpdateSchemaCheck parameter in the Use-DatabaseReleaseArtifact cmdlet (this option is also available in the AzureDevOps add-on)

How to access the database deployment resources

Since the changes aren't rolled back, comparing the States\Source folder in the deployment resources with the Target Database is a very effective way of troubleshooting. If you have static data, bear in mind that you will have to use the SQL Compare command line tool and use the /include:staticdata parameter.

If using:

• Powershell - Use the Export-DatabaseReleaseArtifact cmdlet 
• Octopus Deploy template - The location is set on the Create Database Release step (Export path parameter)
• AzureDevops - You can create these if you use the Create Database Release Artifact operation and are using a Self Hosted Agent (you can set the location where this will be created in the Export path parameter or if you leave this blank you can find the location where they were created on the logs)

Check the logs/console output

At the default logging level you will not get enough information to pinpoint the exact cause, however you can increase the level of the log/console output

 Powershell

         At the start of your script add:

         $DebugPreference = 'Continue'
         $VerbosePreference = 'Continue'

• Octopus Deploy template

         Edit the template and add the same variables listed above for Powershell

• Azure DevOps

         On your release definition, add a process variable named system.debug and set its value to           true

Example of a Powershell console output showing a Post-Update Schema Check Failure with Debug mode on:

Update script successfully applied to database 'DB1_target' on server 'W2K16-1'.
Checking target database schema is in expected state:
Comparing the schema in the Database Release object (created at 10/16/2019 12:58:21 +01:00) to the current schema of database 'DB1_target' on server 'W2K16-1'.
VERBOSE: Calling: C:\Program Files (x86)\Red Gate\SQL Change Automation PowerShell\SC\SQLCompare.exe /options:ConsiderNextFilegroupInPartitionSchemes,DecryptPost2kEncryptedObjects,DoNotOutput
CommentHeader,ForceColumnOrder,IgnoreCertificatesAndCryptoKeys,IgnoreDatabaseAndServerName,IgnoreTSQLT,IgnoreUsersPermissionsAndRoleMemberships,IgnoreUserProperties,IgnoreWhiteSpace,IgnoreWit
hElementOrder,IncludeDependencies,NoDeploymentLogging,ThrowOnFileParseFailed,UseCompatibilityLevel /DataCompareOptions:IgnoreSpaces,IncludeIdentities,DisableKeys,OutputComments,ReseedIdentity
,DontUseTransactions,MissingFrom2AsInclude /include:staticData /OutputWidth:1024 "/scripts1:C:\Users\Sergio\AppData\Local\Temp\1\DLM Automation\2i3kztf2.2cj" /server2:W2K16-1 /database2:DB1_t
arget /username2:sqluser /password2:******** /out:"C:\Users\User\AppData\Local\Temp\1\DLM Automation\4wfmotw5.log"
DEBUG: SQL Compare Command Line for DLM Automation V14.0.2.12921
DEBUG: Copyright c Red Gate Software Ltd 2019
DEBUG: Warning: Unrecognised SQL Data Compare option: OutputComments.
DEBUG: Registering data sources
DEBUG: Creating mappings
DEBUG: Comparing
DEBUG: Applying Command Line Items
DEBUG: Checking for identical databases
DEBUG: Summary Information
DEBUG: DB1 = 2i3kztf2.2cj
DEBUG: DB2 = W2K16-1.DB1_target
DEBUG: Object type Name 
Records DB1 DB2 
DEBUG: 
DEBUG: Table [dbo].[T1] 
                  <> <> 
DEBUG:
VERBOSE: C:\Program Files (x86)\Red Gate\SQL Change Automation PowerShell\SC\SQLCompare.exe ended with exit code 0
VERBOSE: SQLCompare.exe completed successfully.
Use-DatabaseReleaseArtifact : Post-update schema check failed: the schema in the Database Release object (created at 10/16/2019 12:58:21 +01:00) and database 'DB1_target' on server 
'W2K16-1' are different, or static data is different.

Looking at the log you can see that the failure was caused by a schema difference in Table [dbo].[T1]

You can then adapt and run the sqlcompare.exe command displayed at the start of the log above to generate a report with more in-depth information.

SQLCompare.exe /filter:(see note 2) /options:ConsiderNextFilegroupInPartitionSchemes,DecryptPost2kEncryptedObjects,DoNotOutputCommentHeader,ForceColumnOrder,IgnoreCertificatesAndCryptoKeys,IgnoreDatabaseAndServerName,IgnoreTSQLT,IgnoreUsersPermissionsAndRoleMemberships,IgnoreUserProperties,IgnoreWhiteSpace,IgnoreWithElementOrder,IncludeDependencies,NoDeploymentLogging,ThrowOnFileParseFailed,UseCompatibilityLevel /DataCompareOptions:IgnoreSpaces,IncludeIdentities,DisableKeys,ReseedIdentity,DontUseTransactions,MissingFrom2AsInclude /include:staticData  /scripts1:(see note 1) /server2:Target_SQL_Server /database2:Target_Database /report:path_where_you_want_to save_your_report /ReportType:Interactive

1 - You should set the path to the States\Source scripts folder in /scripts1, if you don't have the deployment resources you can unzip the nuget package and use the db\state folder instead

2 - You should either set the path to the filter file on the root of the folder you've used in /scripts1 or if you explicitly have set a filter file in the Create Database Release Artifact step the path to that filter file

3 - Please note that the example above shows how the command would look with default SQL Compare and SQL Data Compare options, if you have enabled additional options or disabled default options you will need to adjust accordingly (refer to your own log to see which options you should be using)

In this case the report shows that the Failure was caused by the Fill Factor issue described in Common Causes:

Common Causes

Post-Deployment Scripts

Code in the Post-Deployment Scripts is not taken into account when determining the expected state of the schema/static data after the deployment, so if your Post-Deployment Script changes the schema and or static data you might get this error.

Non-Default Server Level Fill Factor

Having a non-default Server Level Fill Factor can cause this issue to occur.

The suggested workaround, in this case, is to use the IgnoreFillFactor SQL Compare option when creating the release. However please note that this might result in genuine differences in Fill Factors not being deployed, so depending on how important this is in your case, you might want to use the -SkipPostUpdateSchemaCheck parameter instead.