SQL Server Database schema versioning and update

Question

For my application I have to support update scenarios and the database might be affected.

I want to be able to update from an old version to the newest without installing intermediate versions. E.g. Suppose I have version A (the oldest), B (intermediate) and C (new version). I want to be able to update version A straight to version C. For the application files this is simple, I just replace the old with the new ones. However for the database I do not wish to generate a SQL Script to change the database schema from A straight to C, instead I want first apply a script to change the schema from A to B and from B to C.

How can I store the database version for a SQL Server database? Is there any special property which I can set, instead of implementing a version table? In my code (.NET) I want to read the database version and accordingly to execute the update SQL scripts in the proper order.

I will use both SQL Server 2005 and SQL Server 2008.

Answer 1

I use database extended properties, see Version Control and your Database:

SELECT [value] 
    from ::fn_listextendedproperty (
        'MyApplication DB Version', 
        default, default, default, default, default, default);

...

EXEC sp_updateextendedproperty 
    @name = N'MyApplication DB Version', @value = '1.2';
GO

Answer 2

Stick a version table in, its simple, its effective, and for the past, erm, well its more than 10 years it has worked a treat for me.

I wrote this up in response to another question here