Simon Thorneycroft and Jonathan Hodgson

Comparing Sql Server schema and data between databases and servers

I'm going a bit Sql Server blog crazy...

Some tools you just need to have, one of those is Sql Compare from Red Gate, they are a British company based near Cambridge.

"SQL Compare is for use in a development environment when changes made to a local database need to be pushed to a live database on a remote server. Traditionally, this meant spending hours trawling through database schemas, hand-generating migration scripts. SQL Compare will automate this process for you. It will compare and synchronize all database objects, including, but not limited to, tables, stored procedures, views and user-defined functions. SQL Compare does this fiddly and tedious job properly – for example, dependencies and referential integrity are properly taken into account, and the SQL scripts are of a very high quality."

One thing that is particularly good about this set of tools is that it places created scripts within a transaction, so it will rollback any changes should an error occur.  The scripts also include users and associated permissions for all objects. Perfect tool for double-checking you haven't missed anything and checking that development, staging, test and production are all in sync after a release.