I don’t know if I mentioned it once before, I’m a software developer in lower Austria, mainly working on .NET (from Applikation to webdesign). As a developer you often have to store data somewhere in a database. You develop your application, install it at the customer’s place, time passes and passes and finally you find in a situtation where your database design has somehow changed.
Hm, have I noted all the changes somewhere, argg, I don’t want to write SQL on my self for a column I dropped or a column I added. Wouldn’t it be nice to have a program, where you just can say: This is snapshot one of my database, this is snapshot two of my database, just do what’s necessary … .
I’ve searched the one day and anothe and found some programs … . Too incomplete, too buggy, does only show differences, but doesn’t generate a automagical script, the list is long.
Many users are insisting on Red-Gates comparison, I never tried it, it is surely a good tool, so far I can tell this. But my boss doesn’t want to spend money on things even if it is a 300 Euro ( which boss does? 
) and I feel uncomfortable with cracking.
So what? Give up? No way, I found a very good tool named Open DBDiff, which does exactly the same so far I can see.
You enter databases, the changed “new” database as source and the “old” original database as destination. The program generates a mssql update script, which works nearly flawlessly.
So what I did today?
- Just generate the script
- looking over the script
- you should especially look over the drops, constrain drops aren’t that bad, as far as the right constrains are created at the end of the script, but if you drop a table or a column all data will be lost within so far
- adjusting the script (if you rename a column, the program can’t know you renamed it, it believes you dropped a column and added a new, so you should preserve your data)
- backing up your database (always a good idea)
- using your script on the original database and thus changing it to the new one …
And all this within a few minutes (ok my google search took longer … )
So no more stumbling around, what you have changed or something like this.
Some notes:
- Redgate software seems to be able to compare two scripts, Open DBDiff is only able to compare two live databases.
- Open DBDiff is only for MSSQL (it’s opensource, so I hope someone implements other databases as well, perhaps I’ll have a look on this)
- You can start the commandline tool OCDB to automate script generation.
- Normal behaviour is to have the column order on both tables the same. Somehow there seems to be an error: On my first start it added columns at the end (which is quite normal, if you add a column), on my second run it wanted to correct the order.But there is an option “Column order” which you may uncheck.
- ALWAYS BACKUP and Look through the script beforehand.
My conclusion: Open DBDiff just rocks (though it’s still beta):
http://opendbiff.codeplex.com/
Some pictures are here (perhaps I add some pictures here), I hope you get the idea and enjoyed this post:
http://www.databasejournal.com/features/mssql/article.php/3842326/Open-DBDiff-for-SQL-Server-2008.htm