Deploy.template.ps1

function Get-SqlScalarValue($variableName, $ConnectionString, $scalarQuery) {
  try {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = $ConnectionString
    $SqlConnection.Open()
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $scalarQuery
    $SqlCmd.Connection = $sqlConnection
    $scalarValue = [string]$SqlCmd.ExecuteScalar()
    if ($scalarValue -eq '') {
      Write-Warning "Could not determine a value for $variableName variable. An empty string will be supplied to the deployment."
    }
    $SqlConnection.Close()
    return $scalarValue
  }
  catch {
    Write-Warning "Could not retrieve a value for ${variableName}: $_ "
    return ""
  }
}
function Get-ScriptDirectory {
  $Invocation = (Get-Variable MyInvocation -Scope 1).Value
  Split-Path $Invocation.MyCommand.Path
}

try {
  if ($ReleaseVersion -eq $null) {
    $ReleaseVersion = '';
    if ($OctopusEnvironmentName -eq $null) {
      Write-Warning 'As the ReleaseVersion variable is not set, the [__MigrationLog].[release_version] column will be set to NULL for any pending migrations.'
    }
  }
  if ($OctopusReleaseNumber -ne $null) { $ReleaseVersion = $OctopusReleaseNumber }
  if ($DeployPath -eq $null) { $DeployPath = (Get-ScriptDirectory).TrimEnd('\') + '\' }
  if ($SkipOctopusVariableValidation -ne $null) { $SkipVariableValidation = $SkipOctopusVariableValidation }
  if ($UseSqlCmdVariableDefaults -eq $null) { $UseSqlCmdVariableDefaults = "true" }
  if ($UseSqlCmdVariableDefaults -eq "true") {
    Write-Output 'If you require that all SqlCmd variable values be passed in explicitly, specify UseSqlCmdVariableDefaults=False.'
    foreach ($kvp in $SqlCmdVariables.GetEnumerator()) {
      $identity = $kvp.Name
      $default = $kvp.Value
      $currentValue = Get-Variable $identity -ValueOnly -ErrorAction SilentlyContinue
      if ($identity -ne '') {
        if ($currentValue -eq $null) {
          Write-Output "Using default value for $identity variable: $default"
          New-Variable $identity $default
        }
      }
    }
    if ($TargetDatabase -ne '') {
      if ($DatabaseName -eq $null) {
      Write-Output "Using default value for DatabaseName variable: $TargetDatabase"
      $DatabaseName=$TargetDatabase.Replace("'", "''")
      }
    }
    if ($ForceDeployWithoutBaseline -eq $null) {
      Write-Output 'Using default value for ForceDeployWithoutBaseline variable: False'
      $ForceDeployWithoutBaseline = 'False'
    }
    if ($OctoOptionDeployToLocalInstance) {
      if ($DatabaseServer -eq $null -and $OctoOptionDeployToLocalInstance -ne "false") {
        Write-Output '**Deploying to (local) because OctoOptionDeployToLocalInstance=True'
        $DatabaseServer='(local)'
      }
    }
  }

  if ($SkipVariableValidation -ne $true) {
    if ($DatabaseServer -eq $null) {
      Throw 'DatabaseServer variable was not provided.'
    }
    if ($DatabaseName -eq $null) {
      Throw 'DatabaseName variable was not provided.'
    }
    if ($ForceDeployWithoutBaseline -eq $null) {
      Throw 'ForceDeployWithoutBaseline variable was not provided.'
    }
    foreach ($kvp in $SqlCmdVariables.GetEnumerator()) {
      $identity = $kvp.Name
      $currentValue = Get-Variable $identity -ValueOnly -ErrorAction SilentlyContinue
      if ($currentValue -eq $null) {
        Throw "$identity variable was not provided"
      }
    }
  }

  if ($__isAzurePlatformTarget -eq $false) {
    if ($UseWindowsAuth -eq $null) {
      $UseWindowsAuth = $true
    }
  }
  if ($UseWindowsAuth -eq $true) {
      Write-Output 'Using Windows Authentication'
    $SqlCmdAuth = '-E'
    $ConnectionString = 'Data Source=' + $DatabaseServer + ';Integrated Security=SSPI';
  }
  else {
    if ($DatabaseUserName -eq $null) {
      Throw 'As SQL Server Authentication is to be used, please specify values for the DatabaseUserName and DatabasePassword variables. Alternately, specify UseWindowsAuth=True to use Windows Authentication instead.'
    }
    if ($DatabasePassword -eq $null) {
      Throw 'If a DatabaseUserName is specified, the DatabasePassword variable must also be provided.'
    }
    Write-Output 'Using SQL Server Authentication'
    $SqlCmdAuth = '-U "' + $DatabaseUserName.Replace('"', '""') + '" '; $env:SQLCMDPASSWORD=$DatabasePassword; $ConnectionString = 'Data Source=' + $DatabaseServer + ';User Id=' + $DatabaseUserName + ';Password=' + $DatabasePassword;
  }

  if ($__isAzurePlatformTarget -eq $true) {
    $databaseExists = Get-SqlScalarValue 'databaseExists' $ConnectionString ('select count(*) from sys.databases where name = ''' + $DatabaseName.Replace("'", "''") + "'")
    $DefaultFilePrefix = ""
    $DefaultDataPath = ""
    $DefaultLogPath = ""
    $DefaultBackupPath = ""
  }
  else {
    if ($DefaultFilePrefix -eq $null) {
      Write-Output "Using default value for DefaultFilePrefix variable: $TargetDatabase"
      $DefaultFilePrefix = $TargetDatabase.Replace("'", "''")
    }
    if ($DefaultDataPath -eq $null) {
      $DefaultDataPath = Get-SqlScalarValue "DefaultDataPath" $ConnectionString "declare @DefaultPath nvarchar(512); exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DefaultPath output; if (@DefaultPath is null) begin set @DefaultPath = (select F.physical_name from sys.master_files F where F.database_id=db_id('master') and F.type = 0); select @DefaultPath=substring(@DefaultPath, 1, len(@DefaultPath) - charindex('\', reverse(@DefaultPath))); end select isnull(@DefaultPath + '\', '') DefaultData"
      Write-Output "Using default value for DefaultDataPath variable: $DefaultDataPath"
    }
    if ($DefaultLogPath -eq $null) {
      $DefaultLogPath = Get-SqlScalarValue "DefaultLogPath" $ConnectionString "declare @DefaultPath nvarchar(512); exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefaultPath output; if (@DefaultPath is null) begin set @DefaultPath = (select F.physical_name from sys.master_files F where F.database_id=db_id('master') and F.type = 1); select @DefaultPath=substring(@DefaultPath, 1, len(@DefaultPath) - charindex('\', reverse(@DefaultPath))); end select isnull(@DefaultPath + '\', '') DefaultData"
      Write-Output "Using default value for DefaultLogPath variable: $DefaultLogPath"
    }
    if ($DefaultBackupPath -eq $null) {
      $DefaultBackupPath = Get-SqlScalarValue "DefaultBackupPath" $ConnectionString "declare @DefaultBackup nvarchar(512); exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBackup output; select isnull(@DefaultBackup + '\', '') DefaultBackup;"
      Write-Output "Using default value for DefaultBackupPath variable: $DefaultBackupPath"
    }
  }

  Write-Output "Starting '$DatabaseName' Database Deployment to '$DatabaseServer'"
  $SqlCmdVarArguments = 'DatabaseName="' + $DatabaseName.Replace('"', '""') + '"'
  $SqlCmdVarArguments += ' ReleaseVersion="' + $ReleaseVersion.Replace('"', '""') + '"'
  $SqlCmdVarArguments += ' DeployPath="' + $DeployPath.Replace('"', '""') + '"'
  $SqlCmdVarArguments += ' ForceDeployWithoutBaseline="' + $ForceDeployWithoutBaseline.Replace('"', '""') + '"'
  $SqlCmdVarArguments += ' DefaultFilePrefix="' + $DefaultFilePrefix.Replace('"', '""') + '"'
  $SqlCmdVarArguments += ' DefaultDataPath="' + $DefaultDataPath.Replace('"', '""') + '"'
  $SqlCmdVarArguments += ' DefaultLogPath="' + $DefaultLogPath.Replace('"', '""') + '"'
  $SqlCmdVarArguments += ' DefaultBackupPath="' + $DefaultBackupPath.Replace('"', '""') + '"'
  foreach ($kvp in $SqlCmdVariables.GetEnumerator()) {
    $identity = $kvp.Name
    $currentValue = Get-Variable $identity -ValueOnly -ErrorAction SilentlyContinue

    $SqlCmdVarArguments += " $Identity=""" + $currentValue.Replace('"', '""') + '"'
  }

  $SqlCmdBase = 'sqlcmd.exe -b -S "' + $DatabaseServer + '" -v ' + $SqlCmdVarArguments

  if ($__isAzurePlatformTarget -eq $false) {
    $SqlCmd = $SqlCmdBase
  }
  if ($__isAzurePlatformTarget -eq $true) {
    $SqlCmd = $SqlCmdBase + ' -d "' + $DatabaseName.Replace('"', '""') + '"'
  }
  $SqlCmd = $SqlCmd + ' -i "' + (Get-ScriptDirectory) + "\$CreateScriptFileName" + '"'
  $SqlCmdWithAuth = $SqlCmd + ' ' + $SqlCmdAuth
  Write-Output $SqlCmdWithAuth
}
catch {
  Write-Error "A validation error occurred: $_ "
  if ($SkipVariableValidation) {
    Write-Error 'To bypass variable validation, pass this property value to MSBuild: SkipVariableValidation=True'
  }
  if ($OctopusEnvironmentName -ne $null) {
    [Environment]::Exit(1)
  }
  throw
}

# SQLCMD package deployment
if ($__isAzurePlatformTarget -eq $true) {
  $SqlCmdCreateDatabase = $SqlCmdBase + ' ' + $SqlCmdAuth + ' -Q "CREATE DATABASE [' + $DatabaseName.Replace('"', '""') + ']"'
  if ($databaseExists -eq "0") {
    try {
      Write-Output "Creating database $DatabaseName..."
      cmd /Q /C $SqlCmdCreateDatabase
      if ($lastexitcode) {
        throw 'sqlcmd.exe exited with a non-zero exit code.'
      }
    }
    catch {
      Write-Error "A deployment error occurred: $_ "
      if ($OctopusEnvironmentName -ne $null) {
        [Environment]::Exit(1)
      }
      throw
    }
  }
  else {
    Write-Output "The database already exists. An incremental deployment will be performed."
  }
}

try {
  cmd /Q /C $SqlCmdWithAuth
  if ($lastexitcode) {
    throw 'sqlcmd.exe exited with a non-zero exit code.'
  }
}
catch {
  Write-Error "A deployment error occurred: $_ "
  if ($OctopusEnvironmentName -ne $null)     {
    [Environment]::Exit(1)
  }
  throw
}

function Read-Snapshot($ScriptDirectory) {
  try{
    $SnapshotName = (Get-Item "$ScriptDirectory\$SnapshotPackageFileName" -ErrorAction Stop)[0].Name
    $SnapshotPath = Join-Path $ScriptDirectory $SnapshotName
    $HexString = [System.BitConverter]::ToString([System.IO.File]::ReadAllBytes($SnapshotPath)).Replace('-', '')
    if ([string]::IsNullOrEmpty($HexString)) {
      Throw "File [$SnapshotPackageFileName] contained no data."
    }
    return $HexString
  }
  catch{
    Write-Warning "Failed to read schema snapshot from file. As a result, preview/drift reports will be unavailable for the next deployment: $_"
    return $null
  }
}
function Write-Snapshot($HexString) {
  $WriteFailedMessage = "No schema snapshot will be written to the target database. As a result, preview/drift reports will be unavailable for the next deployment."

  if ([string]::IsNullOrEmpty($HexString)) {
    Write-Warning $WriteFailedMessage
    return
  }

  $SchemaSnapshotTableName = "__SchemaSnapshot"

  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = "$ConnectionString;Database=$DatabaseName"
  $SnapshotSqlCmd = New-Object System.Data.SqlClient.SqlCommand
  $SnapshotSqlCmd.Connection = $SqlConnection

  $InsertQuery = "INSERT INTO [$MigrationLogSchemaName].[$SchemaSnapshotTableName] (Snapshot) VALUES (0x$HexString)"
  $SnapshotSqlCmd.CommandText = "$InsertQuery"

  try {
    $SqlConnection.Open()
    $SnapshotSqlCmd.ExecuteScalar()
  }
  catch {
    Write-Warning "Failed to write schema snapshot to database: $_"
    Write-Warning $WriteFailedMessage
  }
  finally{
    $SqlConnection.Close()
    $SnapshotSqlCmd.Dispose()
  }
}

$ScriptDirectory = Get-ScriptDirectory
if (Test-Path "$ScriptDirectory\$SnapshotPackageFileName") {
  Write-Output "Reading schema snapshot"
  $HexString = Read-Snapshot(Get-ScriptDirectory)

  if ($HexString -ne $null) {
    Write-Output "Writing schema snapshot to target database"
    Write-Snapshot($HexString)
  }
}
else
{
  Write-Output 'Skipping schema snapshot deployment as a snapshot file could not be found. As a result, preview/drift reports will be unavailable for the next deployment. To enable schema snapshot creation, specify the ShadowServer property in your build configuration https://www.red-gate.com/sca/continuous-integration'
}