C# and SQL Server 2008 - script differences between dev and production database?

Question

I have a database that I use for all my development and testing and a second database that serves as my production database. What is the best way to script the differences in tables (structure only) between the two? My dev database might have new columns that my production does not have and I need to get the differences into the production side without (obviously) affecting the data in the production database.

Answer 1

Many ways to do this, but one of the most popular is with a tool like Red-Gate SQL Compare (www.red-gate.com). Our development group would be lost without a tool like this.

Answer 2

You want to check out Red Gate SQL Compare. This will handle it all for you.

Answer 3

I create the ALTER/CREATE scripts as I go in dev, and then run them as part of my update package during push to prod.

There are also tools to do this. RedGate Compare is a great one! Also MS TFS DB edition has a schema compare, but RedGate's is better, IMO.

Answer 4

I usually create the new fields (and tables) in production first. Then use a backup of production for my dev database. This way you never make the mistake of deploying code that uses fields that have not yet been deployed. But I guess this approach is best when working in a small company without strict deployment procedures (and an angry DBA).

Answer 5

If you use Visual Studio 2010 Ultimate/Premium Edition or Visual Studio Team/Database Edition 2008 you will find all you need in the Visual Studio. (See http://stackoverflow.com/questions/3698814/handling-change-in-a-database-schema/3703715#3703715 also)

If you can not VS2010 Ultimate or Premium and no VS2008 Team or Database Editions I would you recommend to look at some products of http://www.red-gate.com/ (like http://www.red-gate.com/products/SQL_Compare/index.htm) with the close features.

Answer 6

You can make use of the system views to query this data. The following example assumes that your databases live on the same server. Essentially, you make a sub query for each table/column/datatype set and join the two on table and column name. Any differences will be evidenced by NULL on either side of the join. If you really want to get clever, you can add a flag column to check the data types. Personally, I would take this data and copy it into Excel for easy sorting/review.

select
    a.prod_table,
    a.prod_column,
    a.prod_column_datatype,
    b.dev_table,
    b.dev_column,
    b.dev_column_datatype
from
    (select 
        t1.name prod_table,
        c1.name prod_column,
        dt1.name prod_column_datatype
     from
        prod.sys.tables t1
        join prod.sys.columns c1 on (t1.object_id = c1.object_id)
        join prod.sys.types dt1 on (c1.system_type_id = dt1.system_type_id)) a
    full outer join 
    (select 
        t2.name dev_table,
        c2.name dev_column,
        dt2.name dev_column_datatype
     from
        prod.sys.tables t2
        join prod.sys.columns c2 on (t2.object_id = c2.object_id)
        join prod.sys.types dt2 on (c2.system_type_id = dt2.system_type_id)) b
    on (a.prod_table = b.dev_table and
        a.prod_column = b.dev_column)
order by 
    a.prod_table,
    a.prod_column,
    b.dev_table,
    b.dev_column