How is SQL Change Automation helping you? Share to win DevOps books.

How to populate dbo.__MigrationoLog.release_version column via powershell?

From a previous post, I learned that we should be able to populate the release_version column of the dbo.__MigrationLog.  "If deploying via PowerShell, set the $ReleaseVersion variable to populate this column".  I am using Bamboo and have a deployment package that uses a powershell script to deploy a release using the Use-DatabaseReleaseArtifact powershell cmdlet.  I set the $ReleaseVersion variable as suggested, but still not seeing the column populated in the __MigrationLog table.
Tagged:

Best Answer

  • MondayMonday Posts: 77 Silver 3
    edited January 1, 2019 6:11PM Accepted Answer
    Actually I am just now going down the path of using the powershell cmdlets. looks like it is set in the New-DatabaseReleaseArtifact  -SqlCmdVariables switch. Example: 

    $SqlCmdVariables = @{ReleaseVersion = $IterationNumber;}
    $DBConnection = New-DatabaseConnection -ServerInstance $EAMDBServer -Database $PQDBName
    $DBUpdate = New-DatabaseReleaseArtifact -Source $DBDeployFile -Target $DBConnection -SqlCmdVariables $SqlCmdVariables
    Use-DatabaseReleaseArtifact $DBUpdate -DeployTo $DBConnection

Answers

  • Eddie DEddie D Posts: 1,681 Rose Gold 5
    Hi, thank you for your forum post.

    Is there any warning message or log entry during deployment to indicate why this has not been set?
    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • No, I'm not seeing a warning or error in the deployment log.  Here's the PS code for the inline PS Script task in my Bamboo deployment project:

    <div>$ErrorActionPreference = "Stop"</div><div><br></div><div>#Will this set the release_version in the __MigrationLog table?</div><div>$ReleaseVersion = "${bamboo.deploy.release}"</div><div><br></div><div>$staging = New-DatabaseConnection -ServerInstance "localhost" -Database "SCAPOC_QA"&nbsp;</div><div><br></div><div># This is where the artifact was saved during build plan:</div><div>#C:\Users\peter\bamboo-home\artifacts\plan-753665\shared\build-00031\DB_Package_Artifact\SCAPOC_Pkg.1.31.nupkg</div><div>#</div><div># Where is it saved after the "Artifact Download" task?</div><div>#C:\Users\peter\bamboo-home\xml-data\build-dir\1998849-2129921\findme</div><div>#bamboo.agentWorkingDirectory	The path to the working directory on the agent. This is not the same as the Bamboo working directory.</div><div>#bamboo.build.working.directory	The path to the working directory for Bamboo. This is used by both the build plan and the deployment project.</div><div><br></div><div><br></div><div>#$path = Join-Path -Path ${bamboo.agentWorkingDirectory} -ChildPath "findme"</div><div>#Get-ChildItem : Cannot find path 'C:\Users\peter\bamboo-home\xml-data\build-dir\findme' because it does not exist.</div><div># we're missing the # -&nbsp;</div><div><br></div><div>$path = Join-Path -Path ${bamboo.build.working.directory} -ChildPath "findme"</div><div>$path</div><div>$file = Get-ChildItem -Path $path -Filter *.nupkg | Select-Object Name</div><div>$buildArtifact = $file.Name</div><div>$buildArtifact = Join-Path -Path $path -ChildPath $buildArtifact</div><div>"Here is our final path - OY VEY:"</div><div>$buildArtifact</div><div><br></div><div>#$buildArtifact = Join-Path -Path $buildArtifact -ChildPath "*.nupkg"</div><div><br></div><div>$releaseArtifact = New-DatabaseReleaseArtifact -Source $buildArtifact -Target $staging</div><div><br></div><div># Makes sure the directory we're about to create doesn't already exist.</div><div>#TODO: make this look like the UAT release path logic.</div><div>$exportPath = "C:\Export\DatabaseRelease\QA"</div><div>If (Test-Path $exportPath) {</div><div>&nbsp; &nbsp; rmdir $exportPath -Recurse -Force</div><div>}</div><div><br></div><div>$releaseArtifact | Export-DatabaseReleaseArtifact -Path $exportPath</div><div><br></div><div>Use-DatabaseReleaseArtifact $releaseArtifact -DeployTo $staging</div>

  • MondayMonday Posts: 77 Silver 3
    Not sure if this helps as I am not using Bamboo, just using a powershell script with the deployment file produced from the build at this time.

    See this doco under: Deploying from PowerShell
    https://documentation.red-gate.com/sca3/automating-database-changes/automated-deployment-with-sql-change-automation-core/sqlcmd-package-deployment

    Just set the $ReleaseVersion variable, Example:
    & { $DatabaseServer = $EAMDBServer; $ReleaseVersion = $IterationNumber; &$DBDeployFile }



Sign In or Register to comment.