Database Script execution Azure Pipeline

RemoveAzureFirewall.ps1

[CmdletBinding(DefaultParameterSetName = 'None')]
param
(
  [String] [Parameter(Mandatory = $true)] $ServerName,
  [String] $AzureFirewallName = "AzureWebAppFirewall"
)

$ErrorActionPreference = 'Stop'

If ((Get-AzureSqlDatabaseServerFirewallRule -ServerName $ServerName -RuleName $AzureFirewallName -ErrorAction SilentlyContinue))
{
  Remove-AzureSqlDatabaseServerFirewallRule -RuleName $AzureFirewallName -ServerName $ServerName
}

SetAzureFirewallRule.ps1

[CmdletBinding(DefaultParameterSetName = 'None')]
param
(
  [String] [Parameter(Mandatory = $true)] $ServerName,
  [String] $AzureFirewallName = "AzureWebAppFirewall"
)

$ErrorActionPreference = 'Stop'

function New-AzureSQLServerFirewallRule {
  $agentIP = (New-Object net.webclient).downloadstring("http://checkip.dyndns.com") -replace "[^\d\.]"
  New-AzureSqlDatabaseServerFirewallRule -StartIPAddress $agentIp -EndIPAddress $agentIp -RuleName $AzureFirewallName -ServerName $ServerName
}
function Update-AzureSQLServerFirewallRule{
  $agentIP= (New-Object net.webclient).downloadstring("http://checkip.dyndns.com") -replace "[^\d\.]"
  Set-AzureSqlDatabaseServerFirewallRule -StartIPAddress $agentIp -EndIPAddress $agentIp -RuleName $AzureFirewallName -ServerName $ServerName
}

If ((Get-AzureSqlDatabaseServerFirewallRule -ServerName $ServerName -RuleName $AzureFirewallName -ErrorAction SilentlyContinue) -eq $null)
{
  New-AzureSQLServerFirewallRule
}
else
{
  Update-AzureSQLServerFirewallRule
}

SqlScriptDeploymentTasks.yaml

variables:
  AzureSubscription: '<Azure service connection>'
  ServerName: '<Database server name>'
  DatabaseName: '<Database name>'
  AdminUser: '<SQL user name>'
  AdminPassword: '<SQL user password>'   <--- use encrypted variables!!!!!!!!!!

steps:
- task: AzurePowerShell@2
  displayName: Update Database
  inputs:
    azureSubscription: '$(AzureSubscription)'
    ScriptPath: '$(Build.SourcesDirectory)\Scripts\SqlUpdateScriptByVersion.ps1'
    arguments: '-ServerName $(ServerName) -UserName $(AdminUser) -Password $(AdminPassword) -DatabaseName $(DatabaseName) -ArgosVersion $(ArgosVersion))'
    azurePowerShellVersion: LatestVersion

SqlUpdateScriptByVersion.ps1

Param(
    [string]$ServerName,
    [string]$DatabaseName,
    [string]$Username,
    [string]$Password,
    [string]$ScriptsFolderPath
    [string]$ArgosVersion
)

$ErrorActionPreference = 'Stop'

function New-AzureSQLServerFirewallRule {
  $agentIP = (New-Object net.webclient).downloadstring("http://checkip.dyndns.com") -replace "[^\d\.]"
  New-AzureSqlDatabaseServerFirewallRule -StartIPAddress $agentIp -EndIPAddress $agentIp -RuleName $AzureFirewallName -ServerName $ServerName
}
function Update-AzureSQLServerFirewallRule{
  $agentIP= (New-Object net.webclient).downloadstring("http://checkip.dyndns.com") -replace "[^\d\.]"
  Set-AzureSqlDatabaseServerFirewallRule -StartIPAddress $agentIp -EndIPAddress $agentIp -RuleName "AzureWebAppFirewall" -ServerName $ServerName
}

If ((Get-AzureSqlDatabaseServerFirewallRule -ServerName $ServerName -RuleName "AzureWebAppFirewall" -ErrorAction SilentlyContinue) -eq $null)
{
  New-AzureSQLServerFirewallRule
}
else
{
  Update-AzureSQLServerFirewallRule
}

Get-ChildItem -Path $ScriptsFolderPath -Include DatabaseScript-$ArgosVersion*.sql | Foreach-Object {
    $scriptName = $_.Name
 
    Write-Host "Executing $scriptName.`n"
    Invoke-Sqlcmd -InputFile $scriptName 
        -ServerInstance $ServerName `
        -Username $Username `
        -Password $Password `
        -Database $DatabaseName
 }

If ((Get-AzureSqlDatabaseServerFirewallRule -ServerName $ServerName -RuleName "AzureWebAppFirewall" -ErrorAction SilentlyContinue))
{
  Remove-AzureSqlDatabaseServerFirewallRule -RuleName "AzureWebAppFirewall" -ServerName $ServerName
}

Ver: https://docs.microsoft.com/en-us/azure/devops/pipelines/process/variables?view=azure-devops&tabs=yaml%2Cbatch