Publishing Database Projects with Visual Studio 2012 and MSBuild

by Administrator 11. August 2012 11:13
A common task for database administrators is to automate building and publishing of database projects.

With Visual Studio 2012, this automation is even eaiser.

Visual Studio 2012 provides a platform for building and publishing database projects using a combination of MSBuild.exe and sqlcmd.exe.

A prerequisite for building and publishing database projects with MSBuild are the SQL Server Data Tools, which can be downloaded here

Also, a publish profile must be created beforehand in Visual Studio to create the .publish.xml file.

A typical process for building and publishing a database project might contain the following steps:

1) Drop the existing objects in the database

(a clean database will cause the publish to generate create statements, instead of alter statements)

2) Build the project

(creates the .dacpac, and .dll files in the debug dir)

3) Publish the project

(creates the .publish.sql file)

4) Create the database objects

(execute the publish.sql file to create the schema)

5) Insert domain data

(insert static domain (lookup) data, e.g. country or state)

6) Import instance data

(execute an import process to populate business object data, e.g. product or customer)

7) Insert test data

(insert test data to support the development process)

Following is an example CMD to process the above steps:

@echo off

setlocal

:PROMPT

ECHO ===============================================================

SET /P AREYOUSURE=You are about to rebuild %1, Are you sure (Y/[N])?

IF /I "%AREYOUSURE%" NEQ "Y" GOTO END

ECHO %1 build started : %time%

C:

CD

SET SERVER=your server Name

REM parm to control target DB of staging data import

REM passed to script variables via sqlcmd

SET TRANDB=%1

SET STAGEDB=your staging DB

SET SCRIPTSPATH=your scripts dir, e.g. domain data create

SET DROPFILE=your drop schema script file name

SET SCHEMAPATH=your DB project\bin\Debug\

SET PUBLISHPROFILE=your project dir\%TRANDB%.publish.xml

SET SCHEMAFILE=your publish schema file

SET DOMAINFILE=your domain data insert script file name

SET TESTDATAFILE=your test data insert script file name

SET PROJECT=your project file

REM clean build target

DEL /F /S /Q your project dir\bin\Debug\*.*

ECHO ========= drop schema =========

sqlcmd -S%SERVER% -d%TRANDB% -i%SCRIPTSPATH%%DROPFILE% -E -v DatabaseName=%1

REM build

msbuild /t:build your project name.sqlproj /p:Configuration="Local" /p:UpdateDatabase=False

/p:OutputPath=%SCHEMAPATH%

REM publish to file

msbuild /t:publish .sqlproj /p:SqlPublishProfilePath=%PUBLISHPROFILE% /p:Configuration="Local" /p:UpdateDatabase=False /p:OutputPath=%SCHEMAPATH%

ECHO ========= create schema =========

sqlcmd -S%SERVER% -d%TRANDB% -i%SCHEMAPATH%%SCHEMAFILE% -E -v DatabaseName=%1

ECHO ========= insert domain data =========

sqlcmd -S%SERVER% -d%TRANDB% -i%SCRIPTSPATH%%DOMAINFILE% -E -v DatabaseName=%1

ECHO ========= run data import =========

sqlcmd -S%SERVER% -d%STAGEDB% -Q"exec _dev_run_stage_sp %1" -E

ECHO ========= insert test data =========

sqlcmd -S%SERVER% -d%TRANDB% -i%SCRIPTSPATH%%TESTDATAFILE% -E -v DatabaseName=%1

ECHO =====================================

ECHO %1 build complete : %time%

:END

With Visual Studio 2012, we now have complete end to end integration of build and publish for database projects.

Tags:

Log in
Privacy Policy Terms and Conditions Copyright Relational Development