Steve Fenton

Run custom database scripts with PowerShell and Octopus Deploy

We are using model-based deployments using Visual Studio database projects and Octopus Deploy. One of the things you can’t do when performing DacPac deployments is include a pre-deployment scrip to disable replication prior to running the deployment (although you can re-enable it in a post-deployment script).

The solution is to change the “disable replication” script to a plain “copy always” script in Visual Studio (so it will be packaged by Octopack and deployed to the server) and then run it on the server using a PowerShell command prior to the DacPac deployment.

Running SQL Scripts With Powershell

The following pre-requisites must be installed on the target server in both x86 and x64 versions (so six small installs in total). They are all in the “Microsoft® SQL Server® Feature Pack” (SQL 2012, SQL 2014), which looks like a download, but if you expand the “Install Instructions” you’ll find lots of individual downloads.

Once downloaded, install them in this order…

1. SQLSysCLRTypes
2. SharedManagementObjects
3. PowerShellTools

You can test that you have everything you need by running the following PowerShell command.

Import-Module sqlps

If you have any trouble with this, you can check and try the full path (using “110” in your path for 2012 and “120” for 2014 etc).

Import-Module "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\sqlps"

You will get a warning, which is aimed at the module developer. You can ignore this – and when we call this from Octopus we will specifically ask for it to be ignored so it doesn’t stop the deployment.

WARNING: The names of some imported commands from the module ‘sqlps’ include unapproved verbs that might make them less discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose parameter. For a list of approved verbs, type Get-Verb.

Here is a basic script to run a SQL query:

Import-Module sqlps
Invoke-Sqlcmd -ServerInstance "." -Database MyDb -Username usr1 -Password pwd -Query "SELECT GETUTCDATE() AS Example"

And you can also run a SQL script file:

Import-Module sqlps
Invoke-Sqlcmd -ServerInstance "." -Database MyDb -Username usr1 -Password pwd  -InputFile "C:\SQL\Script.sql"

The Octopus Version

Now we can add a PowerShell step to the deployment process to run our custom SQL script.

$FileName = $OctopusParameters['Octopus.Action[Database Deployment].Output.Package.InstallationDirectoryPath'] + "\Data\DisableReplication.sql"
Import-Module "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\sqlps" -WarningAction SilentlyContinue
Invoke-Sqlcmd -ServerInstance "." -Database $DatabaseName -Username $UserName -Password $Password -InputFile $FileName