SQL Server Schema Syncronization

Question

I am working on a pre-existing MS SQL Server database that was not designed very well. Indexes and primary keys were missing in most cases. There were NO foreign key constraints.

I made a local copy of the DB (so as not to work on the live version), and fixed most of the problems. I now want to syncronize my changes to the production server,

but...

Because there were no previous foreign key constraints there are key mismatches in the tables, thousands of them. I have tried a few synchronization tools, but none of them will allow to ignore or resolve FK mismatches. (in SQL Server Management Studio it's called "Check Existing Data On Creation Or Re-Enabling: No")

Is there a way to auto-resolve the discrepancies of the old database?

Answer 1

Try to use SQL DATA COMPARE from red-gate to syncrhonize data.

https://www.red-gate.com/dynamic/downloads/downloadform.aspx?download=sqldatacompare

You can also try SQL compare to syncrhonize structure, before synchronize data if SQL data compare don't work.

SQL compare

Answer 2

What do you mean by "auto-resolve"? Existing data is "bad" - that is, the constraints you are trying to impose are violated. There is no way to make your data "good" without modifying it. And there is obviously no way to decide automatically how to fix the data.

So, the best thing you could do is to analyze the data, find out how to correct it, do the corrections manually, and then add the constraints. You could also just delete all the inconsistent rows (probably a bad idea, if you need the data), or force the server to ignore the constraints for existing data (definitely a bad idea).

If you just want to drop the inconsistent data, I'd suggest you to write (or generate, if there're lots of foreing keys) SQL scripts like this:

DELETE a FROM a LEFT JOIN b ON a.b_id = b.id WHERE b.id IS NULL

ALTER TABLE a ADD CONSTRAINT FK_a_b_id FOREIGN KEY (b_id) REFERENCES b (id)