TFS2010 Team build - how to deploy databases before running unit tests

Question

I followed How to prepare database for TFS deployment walkthrough

and my build script successfully deploys the database at the end of the build process. However, I need the database to be deployed before running unit tests. I tried copying the step and pasting it right above "Get Impacted Tests, Index Sources and Publish Symbols". However, the build process returns the following error:

* The deployment manifest file Database_Core.deploymanifest does not exist Here is the excerpt from my xaml file for the database deploy:

                        <Sequence DisplayName="Deploy Database" sap:VirtualizedContainerService.HintSize="486,330">
                        <sap:WorkflowViewStateService.ViewState>
                          <scg:Dictionary x:TypeArguments="x:String, x:Object">
                            <x:Boolean x:Key="IsExpanded">True</x:Boolean>
                            <x:Boolean x:Key="IsPinned">True</x:Boolean>
                          </scg:Dictionary>
                        </sap:WorkflowViewStateService.ViewState>
                        <If Condition="[BuildDetail.CompilationStatus &lt;&gt; BuildPhaseStatus.Failed]" DisplayName="If Build Succeeded" sap:VirtualizedContainerService.HintSize="464,206">
                          <sap:WorkflowViewStateService.ViewState>
                            <scg:Dictionary x:TypeArguments="x:String, x:Object">
                              <x:Boolean x:Key="IsPinned">True</x:Boolean>
                            </scg:Dictionary>
                          </sap:WorkflowViewStateService.ViewState>
                          <If.Then>
                            <mtbwa:InvokeProcess Arguments="[&quot;/a:Deploy /cs:&quot;&quot;Data Source=MyServer-SQL1\BUILD;Integrated Security=True;Pooling=False&quot;&quot; /dd+ /dsp:Sql /manifest:Database_Core.deploymanifest&quot;]" DisplayName="Invoke VSDBCMD" FileName="C:\Program Files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Deploy\VSDBCMD.EXE" sap:VirtualizedContainerService.HintSize="219,100" WorkingDirectory="[BuildDetail.DropLocation]">
                              <mtbwa:InvokeProcess.ErrorDataReceived>
                                <ActivityAction x:TypeArguments="x:String">
                                  <ActivityAction.Argument>
                                    <DelegateInArgument x:TypeArguments="x:String" Name="errOutput" />
                                  </ActivityAction.Argument>
                                  <mtbwa:WriteBuildError DisplayName="VSDBCMD Error" sap:VirtualizedContainerService.HintSize="200,22" Message="[errOutput]" />
                                </ActivityAction>
                              </mtbwa:InvokeProcess.ErrorDataReceived>
                              <mtbwa:InvokeProcess.OutputDataReceived>
                                <ActivityAction x:TypeArguments="x:String">
                                  <ActivityAction.Argument>
                                    <DelegateInArgument x:TypeArguments="x:String" Name="stdOutput" />
                                  </ActivityAction.Argument>
                                  <mtbwa:WriteBuildMessage DisplayName="VSDBCMD Output" sap:VirtualizedContainerService.HintSize="200,22" Importance="[Microsoft.TeamFoundation.Build.Client.BuildMessageImportance.High]" Message="[stdOutput]" mva:VisualBasic.Settings="Assembly references and imported namespaces serialized as XML namespaces" />
                                </ActivityAction>
                              </mtbwa:InvokeProcess.OutputDataReceived>
                              <sap:WorkflowViewStateService.ViewState>
                                <scg:Dictionary x:TypeArguments="x:String, x:Object">
                                  <x:Boolean x:Key="IsPinned">False</x:Boolean>
                                </scg:Dictionary>
                              </sap:WorkflowViewStateService.ViewState>
                            </mtbwa:InvokeProcess>
                          </If.Then>
                          <If.Else>
                            <mtbwa:WriteBuildWarning DisplayName="Deployment Skipped" sap:VirtualizedContainerService.HintSize="220,100" Message="Database deployment was skipped" />
                          </If.Else>
                        </If>
                      </Sequence>

Answer 1

This is exactly what I needed as well. Please see here a PNG that depicts all points that follow:

1. At first I arranged a set of Arguments in my build process template, where I set the target Database host, the user & the password. (See "Argument" section)
   
2. In case the current Project's Unit tests need a running DB, I set in "Items to build" 2 different projects:
   • In the first slot the *.dbproj
   • In the second the SLN itself
     
3. Now within the Build process template I 've expanded the "Run MSBuild for project" as a Sequence (See "Sequence"), ensuring that that the MSBuild Arguments are different in the left case :

Arguments for MSBuild on the left side ("Run MSBuild + Deploy DB"):

String.Format("/p:SkipInvalidConfigurations=true /t:Build;Deploy /p:TargetConnectionString=""Data Source={0}%3Buser={1}%3Bpwd={2}"" /p:DeployToDatabase=true /p:TargetDatabase={3}_{4} {5}",
          TargetMachineToDeployDB, DBUsername, DBPassword, DBName, BuildDetail.BuildNumber.Replace(".", "_"), MSBuildArguments)

In case it's not too obvious, the connection between the Arguments & the displayed params in the Definition are:
- TargetMachineToDeployDB = "PC name where Database shall be deployed"
- DBUsername = "Database Username"
- DBPassword = "Database Password"
- DBName = "Database Prefix Name" (I concat the current buildname)

Arguments for MSBuild on the right side ("Run MSBuid for SLN/Project"):

String.Format("/p:SkipInvalidConfigurations=true {0}", MSBuildArguments)

Note that if I deployed a DB on the left side, I will also set a DBHasBeenSet into TRUE, which will also trigger some file handling inside "Adaptations in Source Files". These include redirecting our NUnit DLLs to the newly constructed DB. I can set more details on that, if you 'd like.