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

If I understand your question right (differentiating between internal database versions, like application build numbers), you could have some sort of SYSVERSION table that held a single row of data with this info.

Easier to query.

Could also contain multiple columns of useful info, or multiple rows that represent different times that copy of the database was upgraded.

Update: Well, if you can't use a table to hold the metadata, then either external info of some sort (an INFO file on the hard drive?) or extended properties would be the way to go.

I still like the table idea, though :) You could always use security to only make it accessable through a custom stored proc get_ db_version or something.

Answer 2

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 3

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.