warning : No schema snapshot could be retrieved from the target database?

MondayMonday Posts: 77 Silver 3
in SQL Change Automation
I get warnings in my build and deployment scripts for this.

warning : No schema snapshot could be retrieved from the target database; no report will be generated until your next deployment. If you continue to see this message, then it is possible that a schema snapshot file is not being created during build. To resolve, specify the ShadowServer property in your build configuration. See https://www.red-gate.com/sca/continuous-integration

I do not see mention of this on the link that is produced in the warning. Where / How do I configure this schema snapshot?

Thanks
Tagged:
· Share on Twitter

Best Answer

  • Alex BAlex B Posts: 1,158 Diamond 4
    Answer ✓
    Hi @Monday,

    I think the link may be wrong; it should probably be this page.  It says the following under step "1. Package deployment script" under the Build artifacts section:

    Also, it will save the build artifact ProjectName_Snapshot.nupkg.bin into the dbo.__SchemaSnapshot table to record the schema that was deployed and to enable reporting in the future.


    And this under step "3. Preview/Diff report" in the same section:

    If a shadow database and a target database is specified, then a report containing the schema differences between the project state and the current version of the database is generated (as stored within your target database's [__SchemaSnapshot] table). This effectively gives you a preview of the changes that are to be made to the target database during deployment.

    The patch deployment script is shown in the diff report, which includes a delta of migrations, programmable objects etc that have yet to be deployed to the database.

    The diff report will only be produced if the target database was deployed using either the package deployment PowerShell script, the MSBuild CLI or the Octopus Deploy package . This is due to the fact that the schema snapshot is not inserted into the target database unless one of these methods is used.

    If the target database was deployed using SSMS, as an Embed change script into application or directly with SQLCMD.EXE, then the schema snapshot will not be available, therefore report generation will not be available.


    And in the first note under Shadow database section:

    As part of the shadow deployment process, a file containing a snapshot of your schema is produced and, upon deployment, will be inserted into the target database. During subsequent builds, the snapshot will be retrieved from the target database and used to generate the deployment preview and drift reports mentioned above. If at deployment time a snapshot file cannot be not found (i.e. because the shadow was not deployed at build time), a warning will be raised to indicate the impact to report generation.


    I have let the team know the link is either wrong, or the information needs to be available on that page as well!

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter

Answers

  • PeterDanielsPeterDaniels Posts: 89 Bronze 3
    Thanks, Alex.  So, I see that page under the "SQL Automation Core".  I guess I assumed that it was not applicable to the case where I'm using the SCA "full tooling" and the PS cmdlets.  I'm getting the sense that I should look at both folders of documentation to get a full picture.  Correct?
    · Share on Twitter
  • Alex BAlex B Posts: 1,158 Diamond 4
    Hi @PeterDaniels,

    I spoke to the development team and they have indicated that we recommend using the SQL Change Automation (SCA) addons (that utilize the PowerShell cmdlets) for users who have the full version of SCA in Visual Studio.

    For those who have SCA Core in VS the instructions for MSBuild exist, which is why it appears in that section.  They have agreed and are going to change the link to point to the page I sent you as it has the pertinent information for MSBuild which is where that error will derive from.

    I hope the information on that page has helped and if you have any further questions do let us know!

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
  • PeterDanielsPeterDaniels Posts: 89 Bronze 3
    Just to be clear, I'm hearing you say that the recommendation is to NOT use the MSBuild when we have full version of SCA in VS. For full version of SCA, we should use the CI add-ons?  What about scripting with the PC cmdlets?  I'm guessing you mean that we use those, too - not JUST the CI add-ons (e.g. for Bamboo).  It's late in the day -I'm not having an easy time expressing myself here...
    · Share on Twitter
  • Jessica RJessica R Posts: 1,319 Rose Gold 4
    Hi @PeterDaniels,

    Sorry for the late reply!

    That is correct that the recommendation is not NOT use the MSBuild when you have the full version of SCA (MsBuild is mainly meant for VS Core users as well as users that haven't migrated to SCA Powershell yet).

    If you have the full version of SCA, it is best to use either the addons (which again use the Powershell cmdlets), or the Powershell cmdlets directly as needed.

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


    · Share on Twitter
Sign In or Register to comment.