VSTS deploy fails with "No schema snapshot could be retrieved from the target database"

IbexMark

This is an Azure deployment.

• Attached: log of error messages I receive during deployment.  
• Attached: Release configuration
• Attached: Build configuration
• Attached: Project Debug Properties.
• Attached: Project target platform

The error message seems to imply that I need to set the "ShadowServer" property somewhere but that does not seem to exist on a VSTS task.  Also, some forum entries seem to imply that we need a shadow database during deploy.  That would be undesirable yet I cannot see where I would turn that off.

Thanks, 
- Mark

MikielAgutu

Hello

This warning is common, usually seen on your first deployment since there is no snapshot on the target database. Unless you have told VSTS to fail the deployment on warnings then this shouldn't present any issues. The mention of the shadow server is to ensure the snapshot is actually created during build. As far as I can see your build configuration is correct.

The actual problem seems to be something wrong in your baseline migration script. In the log you can see (from line 19):

##[warning]C:\Users\VssAdministrator\AppData\Local\Temp\DLM Automation\cejzu05d.04o\Migrations\1.0.0-Baseline\001_20180730-1608_Baseline.sql: An error occurred while attempting to deploy project scripts: 

##[warning]Msg 2714, Level 16, State 6, Server phsa-csbc-pcr-test-sql, Line 2

##[warning]There is already an object named 'defn' in the database.

##[warning]Msg 2759, Level 16, State 1, Server phsa-csbc-pcr-test-sql, Line 2

##[warning]CREATE SCHEMA failed due to previous errors.

It seems the baseline script cannot be deployed because the 'defn' object already exists. Can you check if the MigrationLog table exists on your deployment target. If it does try deleting it, then deploying again with VSTS. If that doesn't work can you check the .sqlproj file to see if the 'Baselining' setting is set to 'FolderBased'.

Thanks

IbexMark

Yes, I was blinded by the first warnings.  I have corrected the error and all is fine.