Version history in your database using Microsoft Visual Studio 2010

In this blog post you will find a little technique that we use in order to store a version history of our deployments. Please check that this requires a working knowledge of datadude so if you don’t know what the terms SQLCMD variables, Post-Deployment script, msbuild & Continuous Integration means to then this blog post may not be referred to for you :)

First we Need a table

First we need a table for the storage of our version history, in this case lets called it BuildVersion and it definitely looks like the following

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

Second we Need a project variable

Here we have a SQLCMD variable declared as part of our project that is meant to store a version number. By default in datadude SQLCMD variables are declared in a file that is called Database.sqlcmdvars:

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

Here we Need to populate the table

We are using the value in our ‘BuildVersion’ variable to fill our BuildVersion table and we do that within a Post-Deployment script here by using the following code sample that you may see what happen next :)

INSERT [dbo].[BuildVersion]([BuildVersion],

[Deployed])
VALUES ('$(BuildVersion)',

GETDATE())

--$(Here BuildVersion is a SQLCMD variable declared within this project)

And here we need to tell our mechanism what the build number really is

First 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) then we can pass in a value from our msbuild script. Find the code that we use to do that:

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

The important bit for what we are discussing here is that last part (underlined):

/p:BuildVersion="$(BuildLabel)"

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

Lets Wrap-up

That’s really what you need. If it all remains together correctly then [BuildVersion] will surely contain a nice history of all your deployments

I hope this is useful for you. I guess the same technique will also work in earlier versions of datadude but I don’t have any to hand so can’t about it.