Red-gate's SQL Compare
Recently I was facing this problem: I am developing an asp.net 2.0/remoting/winforms application which stores data in Sql Server 2005. From time to time I publish my application and its database to the public for testing purposes. The project is under development plus I don't create database at the beginning and never change it (because of various reasons). Instead I add metadata and change database structure here and there. No problems so far but when I need to publish a new version, I have a problem if I want to keep public date and not just overwrite them with new database. What were my options so far:
- carefully record each database modification and store SQL DDL statements. Run all these statements on public database at publish time. This approach works but it is tedious
- create new blank database with latest structure and somehow import data from public database. Substitute public database with this new one. Again, tedious.
Fortunately Red-gate comes to help with its Sql Compare product. It is able to keep database structures in sync, or better, at publish time I just say to SQL Compare that my target database structure (public one) has to look like the development one. SQL Compare compares two database structures and report what's and how's changed. After this step I could just press Synchronization Wizard... button and it would synchronize the target database. Or I can check out the proposed SQL statements SQL Compare creates for me. There is also plenty of options to tweak its behaviors one wishes (i.e. you can filter out the database objects that you don't want synchronized). As a cherry on the pie there is also a command line version and a set of .net API (SQL Toolkit) that lets you do the job from your .net application (I didn't test it yet but I see its great potential).
The bottom line is that Sql Compare is a great time saver tool when you are dealing with Sql Server database synchronization.
And there is much more from Red-gate. They have full set of tools that helps you manage Sql Server beast, the latest one and fresh from the owen being Sql Refactor. That's right, Sql Server refactoring tool - no more hassles when you need to rename an object and then search for all the spots where you have to rename it. I can't wait to try this one out...