I have inherited a system that requires a dbschema to be deployed to multiple databases (one per client) and set up a nightly build to deploy changes to my dev tier (sample below). About 50% of the time it works great, but the other 50% I get an error somewhere in the middle of the build:
"d:\builds{Project}\Dev Nightly Database Update\BuildType\TFSBuild.proj" (EndToEndIteration target) (1) -> "d:\builds{Project}\Dev Nightly Database Update\Sources\Database\Database.dbproj" (Rebuild;Deploy target) (3:18) -> (DspDeploy target) -> C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.TSqlTasks.targets(120,5): error MSB4018: The "SqlDeployTask" task failed unexpectedly. [d:\builds{Project}\Dev Nightly Database Update\Sources\Database\Database.dbproj] C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.TSqlTasks.targets(120,5): error MSB4018: There is a file locking violation. Close any external application that uses the database file. [d:\builds{Project}\Dev Nightly Database Update\Sources\Database\Database.dbproj]
I have looked around but not found any real solutions. I assume that I am getting the error because msbuild is still using the dbproj file when the next iteration starts up. I tried putting a Sleep task in as a last ditch effort but that hasn't helped at all.
Any other suggestions?
<Target Name="Client DB Deploy" Outputs="%(Database.Name)">
<MSBuild Projects="$(SolutionRoot)\Database\Database.dbproj"
Properties="Configuration=Default; OutDir=$(BinariesRoot)\$(Configuration)\;
DeployToDatabase=True;TargetDatabase=%(Database.Name);
TargetConnectionString=Data Source=$(DatabaseServer)%3BIntegrated Security=True;
AlwaysCreateNewDatabase=False;BlockIncrementalDeploymentIfDataLoss=False"
Targets="Rebuild;Deploy" />
<Copy SourceFiles="$(OutDir)..\Database.sql" DestinationFolder="$(OutDir)_SetupPackages\_Database\%(Database.Name)" />
<SleepTask Timeout="10000" /> <!--sleep the build to try and avoid file locks we get in the loop-->
