Back to basics(VSDBCMD.EXE command-line)

This is a command-line tool use in conjunction with new Visual Studio 2010 (VS2010) Database project type.

If you are new to VS2010 Database projects then I strongly recommend having a quick read on MSDN. In a nutshell Database project type allows you to source control the artefacts that you normally add to a SQL Server database (i.e. user defined functions, stored procedures). The output of this type of project is a *.sql file that you can run against the SQL Sever to drop and re-create the database with your artefacts in place. The output script takes care of checking for objects already created and dropping, re-creating in a consistent manner. 

We can "build" this type of project in a TFS build server. However the output you get once you "build" this type of project in TFS or VS2010 is not the final *.sql file. So if you right-click the project and "build", what files do you get? (see the following)

Figure 1: Build output

No *.sql file is generated!. So why is it not generating *.sql file. We will need to build the project as  "Deploy" in order to generate the file. (see below)

Figure 2: Building the project as "Deploy"

Great, now we know how to do this using the UI. But since TFS projects are build as "Release" in the build server, we will need to use the VSDBCMD.EXE to generate the *.sql file.

See the MSDN complete overview of this tool.

This is how I used the command-line tool to generate *.sql deployment script. By the way the VSDBCMD.EXE executable is in "C:\Program Files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Deploy" folder.

Figure 3: Executing VSDBCMD.EXE with manifest file.

As per the above log message, the deployment *.sql is now created. 

We can in theory execute this in the TFS build server itself. If you are interested in this, check this article in MSDN.