MyTechFinds.com

1. If you are using TFS 2010 build definition, add custom steps/actions in build process template to deploy database,
2. If you are using old MSBuild 2008 – TFSBuild.proj file, you can call external batch file or command(s) to deploy database.

Lets take a look at each method one by one,

Deploy database using build process template in TFS 2010

1. Locate the build process template that your build definition file is using. This can be done by editing the build definition file and going to the process tab.
   
   
2. Open the build process definition .xaml file to edit,
3. Add these blocks to the workflow to deploy a build on the build controller. I have placed them just after the main build commands in the workflow. You can choose to find the right spot where you want this db deployment to happen in the build sequence.
   
   
4. In ConvertWorkspaceItem, I am setting which .dbproj I want to build before I deploy it to the build controller,
   
   
5. The most important block is MSBuild, and all that you have to do to deploy the database is to set the Targets property to New String() {“Deploy”}. This command will deploy the database build in above step to the local machine, in this case, the build controller.
   
   
6. Make sure that in dbproj file, you have selected “Generate script and deploy” so that the full database script is executed once the database is created on the build controller.

Deploy database using command line from MSBuild 2008 build definition

1. Create a batch file with database deployment commands. I am using and relying on vsdbcmd that comes as part of the SQL Server installation to deploy the database, but you can always come up with your own cmd file to do the job for other databases.
2. I added below commands in <Target Name="AfterCompile"> section of TFSBuild.proj

<Target Name="AfterCompile">

<Exec command="&quot;C:\Program Files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Deploy\vsdbcmd&quot; /manifest:$(OutDir)TestDatabase.deploymanifest /p:TargetDatabase=&quot;TestDatabase&quot; /a:deploy /dd:+"/>
<Exec command="sqlcmd -S . -E -d CiDatabase -i $(SolutionRoot)\TestDatabase\Scripts\SeedDataInserts.sql"/>
<Exec command="sqlcmd -S . -E -I -d CiDatabase -i $(SolutionRoot)\TestDatabase\Scripts\TestScripts\SeedTestCaseData.sql"/>

</Target>