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;
  }

  $__isAzureDatabaseServer = $false
  if ((Get-SqlScalarValue "isAzureDatabaseServer" $ConnectionString ("select SERVERPROPERTY('EngineEdition')")) -eq 5) {
    $__isAzureDatabaseServer = $true
    if ($__isAzurePlatformTarget -eq $false) {
      Write-Warning "The server is Microsoft Azure SQL Database but the project is set to target SQL Server. This mismatch may result in deployment failure. To resolve, open the project in Visual Studio and adjust the Target Platform setting in the project designer to Microsoft Azure SQL Database."
    }
  }

  $__databaseExists = $false
  $__autoCreateDatabase = $false
  if ((Get-SqlScalarValue "databaseExists" $ConnectionString ("select count(*) from sys.databases where name = '" + $DatabaseName.Replace("'", "''") + "'")) -ne 0) {
    $__databaseExists = $true
  }
  elseif ($__isAzurePlatformTarget -eq $true) {
    $__autoCreateDatabase = $true
  }

  if ($__isAzureDatabaseServer -eq $true) {
    $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 ($__databaseExists -eq $false -and $__autoCreateDatabase -eq $false) {
    $SqlCmd = $SqlCmdBase
  }
  else {
    $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 ($__databaseExists -eq $false) {
  if ($__autoCreateDatabase -eq $true) {
    $SqlCmdCreateDatabase = $SqlCmdBase + ' ' + $SqlCmdAuth + ' -Q "CREATE DATABASE [' + $DatabaseName.Replace('"', '""') + ']"'
    try {
      Write-Output "Automatically 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 does not exist. It will be created by your project Pre-Deployment script(s)."
  }
}
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/dev/continuous-integration-msbuild'
}