Hello!

So, I’ve been working on a project recently where we’ve been using an Azure SQL Database that is stored in source control as an SSDT Project. We’ve also been making use of the excellent TSQLT to run unit tests as part of the build. And typically I set up a Unit Test database to reference the main database so that the tests can be deployed separately from the main code. All this has been working very well, except for those tests that rely on the pre or post deployment scripts that are part of the main project to be run. The issue here is that these scripts are not run as part of the Unit_Test project.

And so there are several options available that can be summed up in the Expanding Brains meme thusly:

A slightly more useful example is this project on GitHub. It basically goes through the four different options that you have - no deployment scripts (Brain One), manually copying them over (Brain Two), editing the sqlproj file (Brain Three) or referencing the original scripts from the other scripts (Brain Four). Run the publishh.xml files and check the output for the messages being printed form the deplyoment scripts.

Clearly the best way with the least pitfalls is Brain Four. We can reference either particular scripts or the original post-deploy which will call all the others referenced by itself. Brain Three also allows us to do this, but stil lrequires editing sqlproj file, which puts off a lot of people. In fact, because the sqlproj file is a little messy, here is the edit to the BuildThree_UnitTest sqlproj file -

  <ItemGroup>
    <MySourceFiles Include="$(SolutionDir)BrainThree\Scripts\Scripts.sql;" />
</ItemGroup>
  <Target Name="BeforeBuild">
    <Message Text="Copying Post Deploy Script" Importance="high" />
    <Copy SourceFiles="@(MySourceFiles)" DestinationFolder="$(SolutionDir)BrainThree_UnitTest\Scripts" OverwriteReadOnlyFiles="true" />
</Target>

You can add more files to the “MySourceFiles” and it will loop over them prior to the build.

With thanks to Ed Elliott for Brain Three solution, and Gavin Campbell for Brain Four solution. I’d still be on Brain Two were it not for them!