Comparing two SQL Server database schema in C#

Question

I am releasing a newer version of my Windows application. There are DB schema changes in the new version. Also I do not want to lose the data.

So the approach that I have taken is to replace the dlls while preserving the database. In order to upgarde the database, I am planning to compare the database schema of the old database and make necessary changes.

So how can I compare the database structure (schema) of old one to new one and how can I detect the changes and correct it. What I have tried so far is try and get the database schema using GetSchema method.

But since the new schema is a predefined one, How can I inject the new schema into the program and compare with the existing one in the site where older was installed.

Answer 1

We use SQL Compare from RedGate, but it's not particularly cheap.

SQL Compare

This lets us compare the structure of two databases, and creates a SQL script to update one of the databases to match the other.

Answer 2

Create your Database Migration scripts and run them with a tool such as Db Up to keep track of schema changes. SQL Scripts migrate your database from version 1 to 2, 2 to 3, etc. Schema Compare is another option mentioned in a previous question.

• Modify Customer Tables.sql
• Update Settings.sql