SQL Data Compare 14 - How to sync DBs with Foreign Key Dependencies

I'm trying to sync a QA database down to DEV to reproduce an issue, and I'm not sure how to avoid orphaned or dirty keys based on these types of warnings after the compare runs:

 "Undeployed tables have foreign keys to table [dbo].[TableName]; the deployment script may fail when the keys are reinstated.  To skip these integrity checks and leave the database without referential integrity, use "Skip integrity checks for foreign key constraints" under Deployment Behavior." 

In my case I've got 4 tables out of a couple dozen that needs sync'd that show this warning. Is there a way to have the tool also choose the other tables with depends automatically, so that they also get sync'd over? I'd prefer not to skip the integrity checks. 

Thanks,
Jeremy

Answers

  • Alex BAlex B Posts: 905 Diamond 3
    Hi @Jeremy_T,

    If you are using the SQL Compare UI, it should prompt you during the deployment wizard that there are dependent objects that need to be deployed and gives you the option to include them as well - see this page https://documentation.red-gate.com/sc/deploying-data-sources/using-the-deployment-wizard#UsingtheDeploymentWizard-Reviewdependencies (3.Review dependencies) for an image and further information.

    On the command line IncludeDependencies is one of the default options, but if you have specified any options the defaults are no longer used and you would then also need to include the "IncludeDependencies" as well.

    Kind regards,
    Alex

    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Jeremy_TJeremy_T Posts: 2 New member
    Hey @Alex B,

    My question was about moving data using SQL Data Compare, not bringing over schema/structure changes via SQL Compare. Both databases are already identical since dev is just a restore from a database backup of QA. What I'm wondering is how to bring down all new or edited data from qa to dev without having to worry about ending up with dirty FKs. Is there an option in SQL Data Compare to just overwrite all data on the target DB from the source easily so that they match completely, that I'm missing in the UI?

    Thanks,
    Jeremy
  • Alex BAlex B Posts: 905 Diamond 3
    Apologies, not sure where my brain was there.  There isn't an "include dependencies" option in SQL Data Compare.

    As for making sure everything is overwritten, you would just compare all tables and then deploy - this should synchronize the source and target - this would add new data, modify existing different data and drop and data on the target that wasn't on the source. 

    It looks like you may be using the comparison option to "Skip integrity checks for foreign key constraints" which, if you turned it off, I think would cause the process to fail if things were in an inconsistent state when the FK were being re-enabled after the inserts as long as you were also not using the option to not use transactions.
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
Sign In or Register to comment.