THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Maintaining version history in your database using Visual Studio 2010

I have written a few blogs lately explaining how my current project is employing the use of datadude (aka the database development tools in Visual Studio 2010) in order to manage our database code, deployment of that code and also testing of it. In this blog post I’m going to share a little technique that we use in order to store a version history of our deployments. Note that this assumes a working knowledge of datadude so if you don’t know what the terms Post-Deployment script, SQLCMD variables, Continuous Integration & msbuild refer to then maybe this blog post is not for you!

 

Need a table

Firstly, we need a table to store our version history, in our case we have called it [BuildVersion] and it looks like this:


CREATE TABLE [dbo].[BuildVersion]
(
   
[BuildVersion] NVARCHAR(20) NOT NULL,
   
[Deployed]     DATETIME NOT NULL
)

20101019schema_view

 

Need a project variable

We have a SQLCMD variable declared as part of our project that is intended to hold a version number. In datadude SQLCMD variables are (by default) declared in a file called Database.sqlcmdvars:

20101019slcmdvars_solnexp

In our case we have a variable called ‘BuildVersion’ that we default to the value of “Unknown”

20101019sqlcmdvars_vars

 

Need to populate the table

We use the value in our ‘BuildVersion’ variable in order to populate our [BuildVersion] table and we do that within a Post-Deployment script using the following simple code:

INSERT   [dbo].[BuildVersion]([BuildVersion], [Deployed])
VALUES   ('$(BuildVersion)', GETDATE())  --$(BuildVersion is a SQLCMD variable declared within this project)

 

And of course we need to tell our mechanism what the build number is

We need to make sure that $(BuildVersion) has a value in it. As we are deploying our database as part of a continuous integration build (leveraging msbuild) we can pass in a value from the msbuild script. Here’s the code that we use to do that:

<Exec Command="..\VSDBCMD\vsdbcmd.exe /Action:Deploy /ConnectionString:&quot;Data Source=$(Server);Integrated Security=True&quot; /DeployToDatabase:+ /ManifestFile:&quot;..\MyDB.deploymanifest&quot; /p:BuildVersion=&quot;$(BuildLabel)&quot;" />

The important bit for what we are discussing herein is that last underlined part:

/p:BuildVersion=&quot;$(BuildLabel)&quot;

The /p directive is used to populate a named variable (in our case BuildVersion) with a value. In our case than value is taken from an msbuild property which, in our case, is referred to by $(BuildLabel). [Note that managing msbuild properties is outside the scope of this blog post.]

Wrap-up

That’s pretty much all you need. If it all hangs together correctly then [BuildVersion] will contain a nice history of all your deployments like so:

20101019results

Hope this is useful! I suspect the same technique will work in earlier versions of datadude but I don’t have any to hand so can’t find out.

@Jamiet

Published Tuesday, October 19, 2010 9:26 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Kristian said:

Interesting.

When would you use this BuildVersion table, and when would you instead refer back to the TFS history/reports/tables?

October 20, 2010 8:40 AM
 

jamiet said:

Kristian,

We don't use TFS so for us its a no-brainer. I'm sure there are other scenarios where it would be useful tho - e.g. Displaying the current DB version on a user-facing website.

-Jamie

October 20, 2010 8:46 AM
 

SSIS Junkie said:

Over the past eighteen months I have worked on four separate projects for customers that wanted to make

January 2, 2012 11:07 AM
 

Inder said:

We have requirement in which client can define GETDATE(), GETUTCDATE(), SYSDATETIME() etc. on the time of database deployment. We are trying to create a system function as a wrapper function  using http://msdn.microsoft.com/en-us/library/aa224829(v=sql.80).aspx#feedback link but we are getting below error.

The specified schema name "sys" either does not exist or you do not have permission to use it.

Please suggest us a better way for implementing our requirement. Currently we are using SQLServer 2008R2

April 25, 2013 7:07 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement