Add versioning to SQL Server Projects

VersionSqlProj.md

Add versioning to SQL Server Projects

Summary

This is a basic set of instructions on how to add version number support for SQL Server projects and databases. This will will add the version number as an extended property on the deployed database.

How to access version number

You may query this version number out of the extended properties tab on the database property window in SQL Management Studio or with the following query.

SELECT [value]
FROM SYS.EXTENDED_PROPERTIES
WHERE
[major_id] = 0
AND [name] = N'DbVersion'
AND [minor_id] = 0

Instructions

Step 1 - Add SetDacVersion.sql to project

Add a file as Scripts\SetDacVersion.sql in your project. The contents of this file will be overwritten by the build process so it doesn't really matter too much is must just exist.

-- Generated by MsBuild Step - DO NOT EDIT
:setvar DacVersion  "0.0.0.0"
:setvar BuildVersion "0.0.0-LocalDev+0"

Step 2 - Add SetDatabaseVersion.sql to the project

This will be used as part of the post deploy process to insert or update the extended property.

IF EXISTS (
	SELECT *
	FROM SYS.EXTENDED_PROPERTIES
	WHERE
		[major_id] = 0
		AND [name] = N'DbVersion'
		AND [minor_id] = 0
)
BEGIN
EXEC sp_updateextendedproperty
	@name='DbVersion',
	@value ='$(BuildVersion)',
	@level0type = NULL,
	@level0name = NULL,
	@level1type = NULL,
	@level1name = NULL,
	@level2type = NULL,
	@level2name = NULL;
END
ELSE 
BEGIN
EXEC sp_addextendedproperty
	@name='DbVersion',
	@value ='$(BuildVersion)',
	@level0type = NULL,
	@level0name = NULL,
	@level1type = NULL,
	@level1name = NULL,
	@level2type = NULL,
	@level2name = NULL;
END
PRINT 'DacVersion = $(DacVersion)';
PRINT 'BuildVersion = $(BuildVersion)';

Step 3 - Add do your pre-deployment script

This assume you have a pre-deployment script such as Scripts\PreDeployment.sql. If this file does not exist either add one or if it is a different directory you may have to update the paths accordingly. This points to the file created in step 1.

:r SetDacVersion.sql

Step 4 - Add do your post-deployment script

This assume you have a post-deployment script such as Scripts\PostDeployment.sql. If this file does not exist either add one or if it is a different directory you may have to update the paths accordingly. This points to the file created in step 2.

:r SetDatabaseVersion.sql

Step 5 - Add WriteDacVersion.targets to your project

This assumes you are only adding this to a single SQL Project. Optionally you may create this file anywhere so it might be shared with other projects just make sure any paths are updated accordingly.

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="WriteDacVersion" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0">
    <Target Name="WriteDacVersion"
    >
<PropertyGroup>
<DacVersion Condition="'$(DacVersion)'==''">0.0.0.1</DacVersion>
<BuildVersion Condition="'$(BuildVersion)'==''">0.0.0-LocalDev+0</BuildVersion>
</PropertyGroup>
<PropertyGroup>
<DatabaseVersionScript>
-- Generated by MsBuild Step - DO NOT EDIT
:setvar DacVersion  &quot;$(DacVersion)&quot;
:setvar BuildVersion &quot;$(Version)&quot;
</DatabaseVersionScript>
</PropertyGroup>
<Message Text="Writing $(ProjectDir)Scripts\SetDacVersion.sql" />
<Message Text="Versions: $(DacVersion) = $(Version)" />
        <WriteLinesToFile
            File="$(ProjectDir)Scripts\SetDacVersion.sql"
            Lines="$(DatabaseVersionScript)"
            Overwrite="true"
            Encoding="Unicode" />
    </Target>
</Project>

Step 6 - Edit existing SQL Project file

In Visual Studio unload the SQL server project and update the .sqlproj file as follows.

6.a - Change the Default Targets

On the root Project element update the DefaultTargets to include WriteDacVersion

DefaultTargets="WriteDacVersion;Build"

6.b - Add Import element

After the root Project and before the first PropertyGroup add a new import element.
This element points to the targets file you created in step 5.

<Import Project="WriteDacVersion.targets" />

6.c - Add/Update DacVersion Property

Look for an element named DacVersion in the first PropertyGroup. If it does not exist add it.

<DacVersion>$(DbVersion)</DacVersion>

Step 7 - Update build process

The previous changes added two new properties to your build. These properties are Version and DacVersion. DavVersion must be in the format #.#.#.# or else it will not be compatible with Data Tier Application tools. Version my follow any string pattern to align with any other versioning you already have withing your build pipelines.

msbuild YourProjectOrSolution.sln /p:Version=0.1.2-FullVersionHere+Commits /p:DBVersion=0.1.2.0