Octopus - RedGate - Deploy from Package - Capture update script as artifact

tomslickers

I am using RedGate - Deploy from Package with Octopus. This is working nicely but I would like to capture the update script as an artifact. How can I do that?

AlexYates

2 options:

OPTION 1:
Write your own powershell rather than using the default step template.

For example:

$scriptsFolder = "C:\Work\scripts" #replace with the location of your nuget package, probably using one of the #[Octopus.action[stepname]... system variables
$test = New-DlmDatabaseConnection -ServerInstance "test01\sql2014" -Database "Test" -Username "sa" -Password "P@ssw0rd" # Username and password only necessary if using SQL Auth. ServerInstance and Database should probably be parameterised

$syncResult = Sync-DlmDatabaseSchema -Source $scriptsFolder -Target $test
Out-File $syncResult.UpdateSql -FilePath "C:\Work\update.sql"

(See example 4 in docs for details here:
https://documentation.red-gate.com/dlma2/cmdlet-reference/sync-dlmdatabaseschema)

OPTION 2:
Use 2 steps:

1. Redgate - Create Database Release - https://library.octopusdeploy.com/step-templates/c20b70dc-69aa-42a1-85db-6d37341b63e3/actiontemplate-redgate-create-database-release
2. Redgate - Deploy from Database Release - https://library.octopusdeploy.com/step-templates/7d18aeb8-5e69-4c91-aca4-0d71022944e8/actiontemplate-redgate-deploy-from-database-release

The first step will create various resources, including a plain sql script and a nice html diff report in advance of deployment. Both will be uploaded as an artifact. You could optionally add a "Manual intervention" step in between the create and deploy steps to enable a review. Perhaps just scope that to the production/pre-production environment?

WHICH TO CHOOSE?
Option 1 is quicker/simpler but requires writing your own PS
Option 2 does not require custom scripting and enables review, but the script is created in advance and it's a slightly more complex process. Also, your deployments will take longer.