Originally asked this on SO but that question seems to be populated only by tumbleweed so wondered if this might be a better forum for it.
I have a VS2103 solution which includes a database project for which we have a number of publish profiles. One of these is for the purposes of testing some functionality that requires an actual database to be in place so it is supposed to publish the database to (localDb), add any lookup data and then run some post deployment scripts to add some fake data.
The test project has a pair of pre-build events to try and ensure that the DB project has been built and is then deployed:
"$(MSBuildBinPath)\msbuild.exe" "$(SolutionDir)CUIA Database\CUIA Database.sqlproj" /t:Rebuild "$(PROGRAMFILES)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe" /Action:Publish /SourceFile:"$(SolutionDir)CUIA Database\$(OutDir)CUIAWorkspace.dacpac" /Profile:"$(SolutionDir)CUIA Database\UNIT-TEST.publish.xml" /Variables:test_run="True" /p:CreateNewDatabase="True"
The SQLCMD variable is what determines if the test data is inserted into the newly created database, and is defaulted to False.
The problem is that it does not seem to work every time and I cannot find a reason why. If I start with nothing on the local db instance, then it works as expected, the DB is created and the tests have something to use. However, if I then change some of the test data using SSMS and re-run the tests, it seems to be a crapshoot as to whether or not the DB is recreated and mostly it is not. I can see this because my amended test data is still there so it has not been dropped. I have also verified the same behavious on a SQL Express instance. Database logs show no problems and the build outputs from successful runs are the same as for those where the DB is not re-created.
I haven't been able to find much in the way of information on what I am trying to achieve so I'm have to go with some hunches and guesswork so I appreciate that my pre-build event idea is possibly not the best solution. If anyone knows a better one then please speak up and share. However, the primary concern is why is the /p:CreateNewDatabase="True" flag not being respected? Surely this should demand that the database is dropped and re-created every single time?