I have a liveDB and I have a development DB. Now when I make a change to development DB, I want to find out the changes later when I am deploying my application on server. For eg. I may have added fields and tables and so on. I want to be able to detect those changes.

Could you please let me know some famous tools that are free and does this job for me so I don't have to remember or write the changes in notepad and later on apply them.

  • 1
    There are no "free tools", but if you're willing to do some work, and you're using C#, you could always use something like "FluentMigrator". Otherwise, your best bet is to actually pay for Redgate's SqlCompare package, it's the industry standard, at least for TSQL, that I know of. – jcolebrand May 2 '13 at 5:49
  • Instead of comparing two databases you should implement a proper script management. Stop doing "ad-hoc" changes to the dev database, put every change into a SQL script that is stored in your source control. You also might want to look a schema migration tools like Flyway or Liquibase. @jcolebrand: there are free tools to do this, but they are no replacement for proper migration scripts. – a_horse_with_no_name May 2 '13 at 7:28
  • Free versions of SqlCompare? gimmmegimmmegimmmmmmee :D – jcolebrand May 2 '13 at 17:18

There are many free tools - native and opensource available for comparing schema between 2 databases:

  1. Open DBDiff ==> This is on Codeplex and works great.
  2. SQL Admin Studio ==> This is now a free tool.
  3. Hidden Gem from SQL 2005 and up : tablediff.exe (you can find this in the COM directory of your SQL Server install folder) Compare schemas: Regular or Strict
  4. Powershell - You have to write your own code or build up on existing ones
  5. Since you are using SQL Server 2012, SSDT is also an option.

From my experience, its worth to invest in Redgate's Schema and data compare tools as they have commandline options and they integrate well with Powershell as well.

| improve this answer | |

The versions of Visual Studio that support "Database Projects" should provide a schema comparison tool that can be used against two different "live" copies of the database (as opposed to a "live" copy and the copy that you are working on in Visual Studio.)

You don't need to create a full-blown project (although Microsoft pushes you to do so since that is really the point of supporting database projects and it does have it's advantages) just to compare a couple of databases. The comparison tool will show you what is different and can generate a script to "merge" the changes. You don't have to manually keep track of your changes and you shouldn't have to write a significant amount of code to "upgrade" your production database to match your development database.

The trick is that Database Projects support for older Visual Studios was provided as a download, while newer versions included it. Some editions of Visual Studio don't support it. They've changed the name of the feature every time they have released it, it seems. "Frankly, I have found the licensing surrounding this feature one of the most confusing things about it. They key thing is that you want to look for support of "Database Projects".

TL;DR: If you are using Visual Studio for other things, you might already have a comparison tool via the "Database Projects" feature. If you've already paid for Visual Studio, that makes the comparison tool effectively free.

| improve this answer | |

I personally don't like free tools messing with my databases...
like others said before me, the best tool for comparing is Redgate Compare
(another option from Redgate is their new Source Control - if you have a repository).

You have another options as a developer:
Develop your own .NET tool using SMO Scripter Class Methods
Scripting Basics.
Then you will only have to compare the (text) results.

I still think that Redgate's software is worth the funds it costs, but that's up to you to decide...

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.