How do I version a SQL Server Database?

Question

I need to put versions onto a SQL Server 2005 database and have these accessible from a .NET Application. What I was thinking is using an Extended Properties on the Database with a name of 'version' and of course the value would be the version of the database. I can then use SQL to get at this. My question is does this sound like a good plan or is there a better way for adding versions to a SQL Server database?

Lets assume I am unable to use a table for holding the Metadata.

Answer 1

I do this:

Create a schema table:

CREATE TABLE [dbo].[SchemaVersion](
    [Major] [int] NOT NULL,
    [Minor] [int] NOT NULL,
    [Build] [int] NOT NULL,
    [Revision] [int] NOT NULL,
    [Applied] [datetime] NOT NULL,
    [Comment] [text] NULL)

Update Schema:

INSERT INTO SchemaVersion(Major, Minor, Build, Revision, Applied, Comment)
VALUES (1, 9, 1, 0, getdate(), 'Add Table to track pay status')

Get database Schema Version:

SELECT TOP 1 Major, Minor, Build from SchemaVersion
ORDER BY Major DESC, Minor DESC, Build DESC, Revision DESC

Adapted from what I read on Coding Horror

Answer 2

We use the Extended Properties as you described it and it works really well.

I think having a table is overkill. If I want to track the differences in my databases I use source control and keep all the db generation scripts in it.

I've also used some ER diagram tools to help me keep track of changes in DB versions. This was outside the actual application but it allowed me to quickly see what changed.

I think it was CASEStudio, or something like that.