SQL Server 2000/2005 - Maintaining Development Version and Production Version

Question

I am used to using Oracle Designer by creating database object definitions, then creating DDL SQL required by connecting to a database instance.

For eaxmple, when adding a new column to a table, first I add it to the table definition, then generate the DDL SQL against the development version and run it, then after testing I point to the Production instance to create the missing column in Production.

Given that there is no Designer in SQL Server what is the correct procedure to say add a column to both database instances? Is there a tool for keeping the schemas in sync?

Answer 1

There isn't a built in tool for keeping them in sync (that I know of), but RedGate has some commercial apps for this.

What do you mean there is no designer in SQL Server? Are you using SQL Server Management Studio?

Answer 2

RedGate offer a product called SQL Compare, that can be used to script out the differences between different databases.

See: http://www.red-gate.com/products/SQL_Compare/index.htm

You could also use the Database Diagrams within SQL Server Management Studio to design your database schema however this would be constrained to within a given database.