For those of you who are using DXC you will probably quickly figure out that you have limited options when it comes to deploying to the integration environment database yourself. When you get access to the integration environment SQL you won't have deleted/create database permissions. This makes sense as spinning up/deleting database will incur costs and it can also mess up the reporting in the background.
I'm a bit of a continuous integration geek so on most of the projects I set up, I build a fully automated Ci/Cd process. This also involves tearing down and re-building databases with test data. As DXC doesn't give you full fledge access to drop and re-create a database if you want to achieve a similar thing you'll be limited to executing commands within SQL. This approach will allow you to do everything you need, except it's a bit more long-winded compared to working with a normal Azure SQL database.
First, you should be aware that this article is a bit of a continuation from Wiping Your Episerver Database. That program works brilliantly on a local on-prem PC, however, when it comes to running the update.bat file against a web app we soon encounter a fundamental issue. If you have no idea what the update.bat file is, then have a look here first. When you run 'update.bat' you need to pass in the folder location of your webroot, like so:
When you're running your environment in a web app you won't have access. Talking to managed services, you can use a tool called 'Kudu'. Kudu is an Azure app that gives you access to a web apps file system. This approach looks like it would work, however, after digging through the epideploy.exe a bit, it was apparent that if you set it to SQL mode, then you could fake this directory.
When I say fake the directory, what I mean is that epideploy doesn't need access to your real webroot, all it needs is for you to add a folder that has a web.config in it, with an empty app settings section and a valid connection string setting that points to your integration environment database. Epideploy (the thing that update.bat calls) only needs the connection string in order to run the update SQL scripts. So creating a fake webroot with an empty web.config will allow it to do its magic, otherwise, it will crash and moan.
Now if you call your update.bat file, like so:
Epideploy will now carry on happily running and apply any patches that are required :)
Things Worth Knowing
If you write your own custom clear database program and you want Octopus to run trigger state program in your build via a PowerShell script, then don't forget to set the PowerShell directory first. If you don't, when the batch file tries to load the scripts, it will look in the current home directory, which is usually Windows\System32. For reference, a PowerShell script to run an exe could look like this: