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…
I use SQL compare too and it’s awsome.
SQL refactor is also very nice.
although i use it mainly for code formatting.
Also try SQL Promt from Red Gate. It an intellisense tool that soupports a lot of apps (SQL Analyzer, VS.NET, SQLServer Management Studio and so on). And it’s FREE 🙂
http://www.red-gate.com/products/SQL_Prompt/index.htm
Hi Giuseppe,
Of course, I have been using
SQL Prompt for quite some time now and find it very effective although not without glitches.
Thanks for the tip though
To ti zna precej povečati obseg dela, ko boš prebil sedanje stanje (ena razvojna in ena delovna baza). Če boš recimo imel 4 baze (razvojna, testna, staging in produkcija), bo že komplicirano. Kaj šele, če boš imel to bazo nameščeno pri več strankah.
Tolko nor pa spet nisem 🙂
I use SQL Complete for code formatting, this is a pretty good tool.
https://www.devart.com/dbforge/sql/sqlcomplete/sql-code-formatter.html
Great post, thanks!
What about SQL Complete https://www.devart.com/dbforge/sql/sqlcomplete/productivity-extension.html ?
This tool takes care of building, editing, and modifying SQL queries, managing and analyzing SQL code, thus making the process more efficient and as simple as ABC.