Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I version a SQL Server Database?

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.

like image 502
widmayer Avatar asked Jan 16 '09 20:01

widmayer


People also ask

How do I find the SQL Server database version?

select * from product_component_version; The result shows your current installed components, Version Number, Maintenance Release Number, Patch Release Number, Port-Specific Patch Release Number etc.

How do I update my SQL database to the latest version?

To upgrade an existing instance of SQL Server to a different edition, from the SQL Server Installation Center click Maintenance, and then select Edition Upgrade. If Setup support files are required, SQL Server Setup installs them. If you are instructed to restart your computer, restart before you continue.


2 Answers

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

like image 180
Matt Brunell Avatar answered Oct 13 '22 18:10

Matt Brunell


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.

like image 22
darren Avatar answered Oct 13 '22 17:10

darren