Database versioning

Since I have abandoned Visual studio database edition because of it not being able to handle tables with data I have returned to my old, proven, way; checking in/committing the update scripts and storing a database version number in a Setting table.

In Microsoft SQL Server Management Studio (or whatever it was called earlier) there is a setting to always create update scripts whenever a change is saved.

Then whenever a change is done, store the update scripts with consecutive version numbers.  These versions are not the same as the release versions but one version number per commit.

013_01_S_Customer_Added the CompanyColour field.sql
013_02_D_Customer_Set the CompanyColour to red except blue for McCarthysen.sql
013_03_S_Customer_Set CompanyColour to notnull and no default value.sql
013_99_D_Setting_Updated database version.sql

013 is the version number.  Next commit is 014 etc.
01, 02, 03 are consecutive numbers.
99 is the final number for this commit.  It always contains an update in the Setting (or whatever you call it) table and sets the version number record to 13.
S and D are Schema and Data respectively.  I have learned that it is good to see this already in the script's filename.
Customer and Setting are the names of the main manipulated tables.
The rest is free text that explains what the script does.

It is now very easy to see how far a database is in the development flow.  Select the the row from the Setting table and notice its number.  Then run all scripts with higher version numbers in the right order.  The scripts are easy to order by their names directly in the version manager or in the file explorer after a checkout/getlatest.

This technique works well with several developers.  Store the scripts locally until they you are ready to commit the code.  Update the version number if someone already used the version number.  Commit.

A tool like SQL compare does approximately the same job.  If you have only a few updates with a few databases it is way faster to just create a diff-script and run it.  Contrary to VSDatabase edition SQLcompare handles tables with data.  It costs money but 1) there is a trial version and 2) it is way cheaper than hacking the code yourself.
There are other tools as well and I have used one but I cannot remember the name of it.  It was not as easy to use as SQLcompare if I recall correctly.

If you have to handle several versions while developing the procedure I have described here is good since it is so easy to check the version of the database.

(I just stumbled upon a situation where saving a view and only updating the output fields from lower to upper case (customername -> CustomerName) did not result in a script file.  In this case there was no problem in scripting a drop/create script through the Object explorer tool box though.)