Looking for a good Database Comparison Tool

Question

Hey there.

I'm looking for an App that will compare two databases and generate the appropriate scripts to upgrade one from the other (I'm using SQL Server 2008).

My scenario is that I've built a fairly basic CMS a while ago, and just recently added several new features. I'd like to upgrade the 3 or 4 existing websites to the new system, which requires updating the database.

Moreover, I'd like to know the "industry standard" way of doing DB updates and being able to auto upgrade when a new software version is released.

Thanks for the advice.

Answer 1

Red-Gate SQL Compare. Can't think of anything better

Answer 2

RedGate have several excellent DB Tools that do what you want. Check out SQL Compare

For incremental releases:

• If you are a 'Microsoft shop' then check out the GDR R2 Database project in Visual Studio 2008, Steep learning curve but it does a lot.

• Bare bones approach: write all your DB schema and data changes as version controlled scripts. Add a version table to your DB schemas, check and update with each incremental change script you run in.

Answer 3

SQL Compare by Redgate or Visual Studio Team System 2008 Database Edition (Data Dude), the last one will also do static code analysis, see here Running static code analysis on SQL Server database with Visual Studio Team System for database architects for a short post

Make sure to grab Microsoft Visual Studio Team System 2008 Database Edition GDR R2 here http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en It adds 2008 support

Answer 4

Two people have mentioned Red-Gate and it is a great tool. But, you should seriously consider Visual Studio Database Edition for complete database schema management. It's free for users of Visual Studio Team Suite and Visual Studio Developer Edition users. Not only does it do schema comparisons as well as Red-Gate, but it will also version control your schema, permit refactoring and much more.