WAY database schemas are not synchronized - need to be updated without data loss

Question

Problem: we have one application that has a part that is used by a very small subset for all users, and this part of the application is also disconnected from a separate database. In an ideal world, the schemas of two databases will be synchronized, but this is not so. Some migrations were performed in a smaller database, most of them do not; and, besides, there is nothing like a revision number to be able to easily identify which ones have and which don't. We would like to solve this problem for future projects. During the discussion, we came up with the following possible plan of action, and I wonder if anyone knows about any project that has already solved this problem:

What we need to do is create an empty database from the schema of a large fully migrated database, and then move all the data from the smaller untranslatable database to this empty one. If this simplifies the situation, we can probably assume precisely this problem that no migrations have ever deleted anything, only added.

Otherwise, if there are other known solutions, I would also like to hear them.

Answer 1

You can use a schema comparison tool such as Red-Gate SQL Compare . You can synchronize changes and not lose any data. I wrote about this and about many alternative tools that were in a wide price range:

http://bertrandaaron.wordpress.com/2012/04/20/re-blog-the-cost-of-reinventing-the-wheel/

The best part is that most tools have trial versions. Thus, you can try them for 14 days (fully functional) and buy them only if it meets your expectations. I can’t talk about other tools, but I have been using RG for many years and it is a very reliable and reliable tool.

(Updated 2012-06-23 to prevent link restriction.)

Answer 2

Red-Gate SQL Compare, as Aaron Bertrand mentions in his answer , is a very good option. However, if you are not allowed to buy something, you can try something like:

1) For each database script print all tables, constraints, indexes, views, procedures, etc.

2) run DIFF and go through all the differences and make sure that a small database can accept them. If you do not make any changes (including data) required for a small database so that they can accept the changes.

3) create a new empty database from a large DB schema

4) import data from a small database into the database.

Answer 3

You can also remake your database in Visual Studio as a database project. Visual Studio Team Suite Database Edition GDR R2 (I know a long name) has the ability to perform schema comparisons and data comparisons, but the beauty of this approach is that you get your entire database into a good database project where you can manage the change and Integrate with source control. This will allow you to build from a common source and deploy consistent changes.