Automagically generate an MSSQL Updatescript between two database versions

28. Februar 2011

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?

  1. Just generate the script
  2. looking over the script
    1. 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
  3. 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)
  4. backing up your database (always a good idea)
  5. 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):

Some pictures are here (perhaps I add some pictures here), I hope you get the idea and enjoyed this post:

Create Readonly Bind Mounts on Linux

27. Februar 2011

Today I’m writing in english so more people can read my post.

Recently I’ve found in a situtation where I wanted to build my own packages on linux (actually ubuntu 10.04 LTS). I’ve done this before, but for now I wanted to do this in a chroot (better test environment not messing my normal system with). I don’t want to write to much about chrooting on linux, cause it’s off topic. If you want to know more RTFM, drop me a message or go to (it’s written for ubuntu, but you may use schroot on other distros at well)

This post covers bind mounting a directory readonly, I use it for chrooted environments, but you may use it for other szenarios as well.
Inspired by I want to write my own post.

I needed some directories from my rootinstallation in my chrooted installation, so how to get them in there:

  • Softlink?: forget it, it’s chrooted
  • Hardlink?: on a directory?
  • bind mount: bingo!

Ok let’s mount home (with schroot your “fstab” would be in /etc/schroot/mount-defaults, so schroot mounts these directories only when you are in the chroot-environment):
/home /home none rw,bind 0 0

Ok, chrooting with schroot -c lucid64 -u root

What if I do a rm -rf /home in my chroot, it’s changrooted, so I’m save, am I?
Here comes a big, fat NO, you have bind mounted it, so you have direct access to the root directory /home!

So just bind mounting it readonly:
/home /home none ro,bind 0 0

changing into the chroot gives us a warning: /home seems to be mounted read-write.

So no possibility to bind mount a directory readonly?

Well there is – a normal readonly mount would work with 2 commands:
mount -o bind /source /destination
mount -o remount,ro /destination

But how can I accomplish this in my fstab (or for schroot: /etc/schroot/mount-defaults)?

The solution: install bindfs (on my ubuntu: sudo aptitude install bindfs).
Bindfs is an “alias” for mount -o bind and does many more, I don’t want to go deeper, it’s only a userland mount with fuse, but I don’t mind about this, with that you can do:
bindfs -o ro /source /destination

or in your fstab (Note: The bindfs# is important here!):
bindfs#/home /home fuse ro 0 0

After that your bind mount will be readonly!

GPS-Koordinaten in den Kontakten fürs Iphone

7. April 2010

Ich hab ein Iphone, es ist ein Firmenhandy und trotz seiner Schwächen habe ich es schätzen gelernt. Eine nette Funktion ist die eingebaute Kartenfunktion mit Lokalisierung. Ein Navi wollte ich mir (noch) nicht zulegen, die meisten Orte die ich anfahre kenne ich, die ich nicht kenne schau ich mir vorher in google maps an, aber das iphone hat mich schon öfters auf den richtigen Weg gebracht. Und das ab und zu sogar zu Fuß mitten in Wien. (Mit dem Auto würde ich allerdings nicht durch Wien mit dem Iphone navigieren, außer man lädt sich TomTom aus dem Appstore um 99$ oder man hat einen verläßlichen Beifahrer, der die Unsicherheiten zwischen hohen Gebäuden richtig deutet :-) )

Diesen Beitrag weiterlesen »

Noch ein Programm, das mir das Leben erleichtert oder wie vergrößere ich eine virtuelle Platte

6. April 2010

Dieses Programm fehlte auf jeden Fall in meinem letzten Post: Virtualbox

Virtualbox stellt virtuelle Maschinen zur Verfügung. Eine virtuelle Maschine ist wie ein Computer im Computer. Ich arbeite oft mit Programmen, die sich gegenseitig ausschließen (Manche SPS-Programme zwischen Siemens und Rockwell vertragen sich nicht, manchmal sogar Siemens gegen ältere Siemensversionen des selben Programms). Jetzt könnte ich natürlich meine Festplatte in 10 Partitionen aufteilen, auf jede WinXP (oder Win7) draufklatschen, die sensiblen Programme separat installieren … .

Diesen Beitrag weiterlesen »

Heigl Harald’s Blog oder “Wieso noch ein Blog?”

5. April 2010

Also wieso noch ein Blog? Gibt es nicht schon genug im Internet? Der Leser mag schon recht haben, aber vielleicht kann ich ihm ja noch etwas neues vermitteln. Hier ein wenig Information zu mir und meinen Beweggründen, wer gleich zu ein paar von mir geschätzten Programmen springen will, soll sich keinen Zwang antun – hier ist der Link: Programme, die mir das Leben erleichtern

Diesen Beitrag weiterlesen »

Programme, die mir das Leben erleichtern

5. April 2010

Für diesen Blog gibt es nur ein paar Links und Information zu Programmen, die ich empfehle. Meine Beweggründe dafür findet man hier.

Diesen Beitrag weiterlesen »