SQL - Backup Database

Octopus.Script exported 2024-09-11 by bcullman belongs to ‘SQL Server’ category.

Backup a MS SQL Server database to the file system.

Parameters

When steps based on the template are included in a project’s deployment process, the parameters below can be set.

Server

Server = .

The name of the SQL Server instance that the database resides in.

Database

Database

The name of the database to back up.

Backup Directory

BackupDirectory

The output directory to drop the database backup into.

SQL login

SqlLogin

The SQL auth login to connect with. If specified, the SQL Password must also be entered.

SQL password

SqlPassword

The password for the SQL auth login to connect with. Only used if SQL Login is specified.

Compression Option

Compression = 1

  • 0 - Use the default backup compression server configuration
  • 1 - Enable the backup compression
  • 2 - Disable the backup compression

Devices

Devices = 1

The number of backup devices to use for the backup.

Backup file suffix

Stamp

Specify a suffix to add to the backup file names. If left blank the backup will use the current timestamp.

Use SQL Server timestamp format

UseSqlServerTimeStamp = false

If no suffix is specified, use the MSSQL timestamp format.

Connection Timeout

ConnectionTimeout = 36000

Specify the connection timeout settings (in seconds) for the SQL connection. If the backup takes longer than this value, the backup will fail.

Backup Action

Incremental = false

Specify the Database backup action

Copy Only

CopyOnly = true

Specify whether the backup is Copy Only

Retention Policy Enabled

RetentionPolicyEnabled = false

Specify if a limit should be imposed on retaining older backups. Will only be applied if Retention Policy Count is set, and is greater than 0.

Retention Policy Count

RetentionPolicyCount

Specify how many old copies of the DB should be retained

Script body

Steps based on this template will execute the following PowerShell script.

$ErrorActionPreference = "Stop"
$EnableVerboseOutput = $false # pester does not support -Verbose; this is a workaround

function ConnectToDatabase() {
  param($server, $SqlLogin, $SqlPassword, $ConnectionTimeout)

  $server.ConnectionContext.StatementTimeout = $ConnectionTimeout

  if ($null -ne $SqlLogin) {

    if ($null -eq $SqlPassword) {
      throw "SQL Password must be specified when using SQL authentication."
    }

    $server.ConnectionContext.LoginSecure = $false
    $server.ConnectionContext.Login = $SqlLogin
    $server.ConnectionContext.Password = $SqlPassword

    Write-Host "Connecting to server using SQL authentication as $SqlLogin."
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server $server.ConnectionContext
  }
  else {
    Write-Host "Connecting to server using Windows authentication."
  }

  try {
    $server.ConnectionContext.Connect()
  }
  catch {
    Write-Error "An error occurred connecting to the database server!`r`n$($_.Exception.ToString())"
  }
}

function AddPercentHandler {
  param($smoBackupRestore, $action)

  $percentEventHandler = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] { Write-Host $dbName $action $_.Percent "%" }
  $completedEventHandler = [Microsoft.SqlServer.Management.Common.ServerMessageEventHandler] { Write-Host $_.Error.Message }

  $smoBackupRestore.add_PercentComplete($percentEventHandler)
  $smoBackupRestore.add_Complete($completedEventHandler)
  $smoBackupRestore.PercentCompleteNotification = 10
}

function CreateDevice {
  param($smoBackupRestore, $directory, $name)

  $devicePath = [System.IO.Path]::Combine($directory, $name)
  $smoBackupRestore.Devices.AddDevice($devicePath, "File")
  return $devicePath
}

function CreateDevices {
  param($smoBackupRestore, $devices, $directory, $dbName, $incremental, $timestamp)

  $targetPaths = New-Object System.Collections.Generic.List[System.String]

  $extension = ".bak"

  if ($incremental -eq $true) {
    $extension = ".trn"
  }

  if ($devices -eq 1) {
    $deviceName = $dbName + "_" + $timestamp + $extension
    $targetPath = CreateDevice $smoBackupRestore $directory $deviceName
    $targetPaths.Add($targetPath)
  }
  else {
    for ($i = 1; $i -le $devices; $i++) {
      $deviceName = $dbName + "_" + $timestamp + "_" + $i + $extension
      $targetPath = CreateDevice $smoBackupRestore $directory $deviceName
      $targetPaths.Add($targetPath)
    }
  }
  return $targetPaths
}

function BackupDatabase {
  param (
    [Microsoft.SqlServer.Management.Smo.Server]$server,
    [string]$dbName,
    [string]$BackupDirectory,
    [int]$devices,
    [int]$compressionOption,
    [boolean]$incremental,
    [boolean]$copyonly,
    [string]$timestamp,
    [string]$timestampFormat,
    [boolean]$RetentionPolicyEnabled,
    [int]$RetentionPolicyCount
  )

  $smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup
  $targetPaths = CreateDevices $smoBackup $devices $BackupDirectory $dbName $incremental $timestamp

  Write-Host "Attempting to backup database $server.Name.$dbName to:"
  $targetPaths | ForEach-Object { Write-Host $_ }
  Write-Host ""

  if ($incremental -eq $true) {
    $smoBackup.Action = "Log"
    $smoBackup.BackupSetDescription = "Log backup of " + $dbName
    $smoBackup.LogTruncation = "Truncate"
  }
  else {
    $smoBackup.Action = "Database"
    $smoBackup.BackupSetDescription = "Full Backup of " + $dbName
  }

  $smoBackup.BackupSetName = $dbName + " Backup"
  $smoBackup.MediaDescription = "Disk"
  $smoBackup.CompressionOption = $compressionOption
  $smoBackup.CopyOnly = $copyonly
  $smoBackup.Initialize = $true
  $smoBackup.Database = $dbName

  try {
    AddPercentHandler $smoBackup "backed up"
    $smoBackup.SqlBackup($server)
    Write-Host "Backup completed successfully."

    if ($RetentionPolicyEnabled -eq $true) {
      ApplyRetentionPolicy $BackupDirectory $dbName $RetentionPolicyCount $Incremental $Devices $timestampFormat
    }
  }
  catch {
    Write-Error "An error occurred backing up the database!`r`n$($_.Exception.ToString())"
  }
}

function ApplyRetentionPolicy {
  param (
      [string]$BackupDirectory,
      [string]$dbName,
      [int]$RetentionPolicyCount,
      [bool]$Incremental = $false,
      [int]$Devices = 1,
      [string]$timestampFormat = "yyyy-MM-dd-HHmmss"
  )

  # Check if RetentionPolicyCount is defined
  if (-not $PSBoundParameters.ContainsKey('RetentionPolicyCount')) {
      Write-Host "Retention policy not applied as RetentionPolicyCount is undefined."
      return
  }

  # Set the appropriate file extension
  $extension = if ($Incremental) { '.trn' } else { '.bak' }

  # Prepare the regex pattern for matching the files
  $dateRegex = $timestampFormat -replace "yyyy", "\d{4}" -replace "MM", "\d{2}" -replace "dd", "\d{2}" -replace "HH", "\d{2}" -replace "mm", "\d{2}" -replace "ss", "\d{2}"
  $devicePattern = if ($Devices -gt 1) { "(_\d+)" } else { "" }
  $regexPattern = "^${dbName}_${dateRegex}${devicePattern}${extension}$"

  # Get all matching files in the directory
  $allBackups = Get-ChildItem -Path $BackupDirectory -Filter "*$extension" | Where-Object { $_.Name -match $regexPattern }

  # If there are no matching backups, exit
  if (-not $allBackups) {
      Write-Host "No matching backups found."
      return
  }

  # If RetentionPolicyCount is zero, don't delete or keep any backups
  if ($RetentionPolicyCount -le 0) {
      if($EnableVerboseOutput) { # pester does not support -Verbose; this is a workaround
        Write-Host "Retention policy not applied as RetentionPolicyCount is set to 0."
      }
  } elseif ($Devices -gt 1) {
      # Group by the timestamp part (ignore the device number)
      $groupedBackups = $allBackups | Group-Object {
          # Extract the timestamp, ignoring the device part if there are multiple devices
          ($_.Name -replace "${devicePattern}${extension}$", "") -replace "^${dbName}_", ""
      }

      # Sort the groups by the timestamp
      $sortedGroups = $groupedBackups | Sort-Object Name

      # Get the groups to keep
      $groupsToKeep = $sortedGroups | Select-Object -Last $RetentionPolicyCount
      $filesToKeep = $groupsToKeep | ForEach-Object { $_.Group }

      # Flatten the collection of files to keep, ensuring that FullName is accessed correctly
      $filesToKeepFlattened = $filesToKeep | ForEach-Object { $_ | Select-Object -ExpandProperty FullName }
      $filesToDelete = $allBackups | Where-Object { $filesToKeepFlattened -notcontains $_.FullName }

      # Delete the old backups
      $filesToDelete | ForEach-Object {
          if($EnableVerboseOutput) { # pester does not support -Verbose; this is a workaround
            Write-Host "Deleting old backup: $($_.FullName)"
          }
          Remove-Item -Path $_.FullName -Force
      }

      # List the files to keep
      $filesToKeepFlattened | ForEach-Object {
          Write-Verbose "Keeping backup: $($_)"
      }

      Write-Host "Retention policy applied. Kept $RetentionPolicyCount most recent backups."
  } else {
      # Single device: simply sort the backups by timestamp
      $sortedBackups = $allBackups | Sort-Object Name

      # Get the backups to keep
      $backupsToKeep = $sortedBackups | Select-Object -Last $RetentionPolicyCount
      $filesToDelete = $allBackups | Where-Object { $backupsToKeep -notcontains $_ }

      # Delete the old backups
      $filesToDelete | ForEach-Object {
        if($EnableVerboseOutput) { # pester does not support -Verbose; this is a workaround
          Write-Host "Deleting old backup: $($_.FullName)"
        }
        Remove-Item -Path $_.FullName -Force
      }

      # List the files to keep
      $backupsToKeep | ForEach-Object {
        if($EnableVerboseOutput) { # pester does not support -Verbose; this is a workaround
          Write-Host "Keeping backup: $($_.FullName)"
        }
      }

      if($EnableVerboseOutput) { # pester does not support -Verbose; this is a workaround
        Write-Host "Retention policy applied. Kept $RetentionPolicyCount most recent backups."
      }
  }
}

function Invoke-SqlBackupProcess {
  param (
    [hashtable]$OctopusParameters
  )

  # Extracting parameters from the hashtable
  $ServerName = $OctopusParameters['Server']
  $DatabaseName = $OctopusParameters['Database']
  $BackupDirectory = $OctopusParameters['BackupDirectory']
  $CompressionOption = [int]$OctopusParameters['Compression']
  $Devices = [int]$OctopusParameters['Devices']
  $Stamp = $OctopusParameters['Stamp']
  $UseSqlServerTimeStamp = $OctopusParameters['UseSqlServerTimeStamp']
  $SqlLogin = $OctopusParameters['SqlLogin']
  $SqlPassword = $OctopusParameters['SqlPassword']
  $ConnectionTimeout = $OctopusParameters['ConnectionTimeout']
  $Incremental = [boolean]::Parse($OctopusParameters['Incremental'])
  $CopyOnly = [boolean]::Parse($OctopusParameters['CopyOnly'])
  $RetentionPolicyEnabled = [boolean]::Parse($OctopusParameters['RetentionPolicyEnabled'])
  $RetentionPolicyCount = [int]$OctopusParameters['RetentionPolicyCount']

  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

  $server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName

  ConnectToDatabase $server $SqlLogin $SqlPassword $ConnectionTimeout

  $database = $server.Databases | Where-Object { $_.Name -eq $DatabaseName }
  $timestampFormat = "yyyy-MM-dd-HHmmss"
  if ($UseSqlServerTimeStamp -eq $true) {
    $timestampFormat = "yyyyMMdd_HHmmss"
  }
  $timestamp = if (-not [string]::IsNullOrEmpty($Stamp)) { $Stamp } else { Get-Date -format $timestampFormat }

  if ($null -eq $database) {
    Write-Error "Database $DatabaseName does not exist on $ServerName"
  }

  if ($Incremental -eq $true) {
    if ($database.RecoveryModel -eq 3) {
      write-error "$DatabaseName has Recovery Model set to Simple. Log backup cannot be run."
    }

    if ($database.LastBackupDate -eq "1/1/0001 12:00 AM") {
      write-error "$DatabaseName has no Full backups. Log backup cannot be run."
    }
  }

  if ($RetentionPolicyEnabled -eq $true -and $RetentionPolicyCount -gt 0) {
    if (-not [int]::TryParse($RetentionPolicyCount, [ref]$null) -or $RetentionPolicyCount -le 0) {
      Write-Error "RetentionPolicyCount must be an integer greater than zero."
    }
  }

  BackupDatabase $server $DatabaseName $BackupDirectory $Devices $CompressionOption $Incremental $CopyOnly $timestamp $timestampFormat $RetentionPolicyEnabled $RetentionPolicyCount
}

if (Test-Path -Path "Variable:OctopusParameters") {
  Invoke-SqlBackupProcess -OctopusParameters $OctopusParameters
}

Provided under the Apache License version 2.0.

Report an issue

To use this template in Octopus Deploy, copy the JSON below and paste it into the Library → Step templates → Import dialog.

{
  "Id": "34b4fa10-329f-4c50-ab7c-d6b047264b83",
  "Name": "SQL - Backup Database",
  "Description": "Backup a MS SQL Server database to the file system.",
  "Version": 13,
  "ExportedAt": "2024-09-11T09:30:00.0000000-07:00",
  "ActionType": "Octopus.Script",
  "Author": "bcullman",
  "Parameters": [
    {
      "Name": "Server",
      "Label": "Server",
      "HelpText": "The name of the SQL Server instance that the database resides in.",
      "DefaultValue": ".",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "Database",
      "Label": "Database",
      "HelpText": "The name of the database to back up.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "BackupDirectory",
      "Label": "Backup Directory",
      "HelpText": "The output directory to drop the database backup into.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "SqlLogin",
      "Label": "SQL login",
      "HelpText": "The SQL auth login to connect with. If specified, the SQL Password must also be entered.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "SqlPassword",
      "Label": "SQL password",
      "HelpText": "The password for the SQL auth login to connect with. Only used if SQL Login is specified.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "Sensitive"
      }
    },
    {
      "Name": "Compression",
      "Label": "Compression Option",
      "HelpText": "- 0  -   Use the default backup compression server configuration\n- 1  -   Enable the backup compression\n- 2  -   Disable the backup compression",
      "DefaultValue": "1",
      "DisplaySettings": {
        "Octopus.ControlType": "Select",
        "Octopus.SelectOptions": "0|Default\n1|Enabled\n2|Disabled"
      }
    },
    {
      "Name": "Devices",
      "Label": "Devices",
      "HelpText": "The number of backup devices to use for the backup.",
      "DefaultValue": "1",
      "DisplaySettings": {
        "Octopus.ControlType": "Select",
        "Octopus.SelectOptions": "1|1\n2|2\n3|3\n4|4"
      }
    },
    {
      "Name": "Stamp",
      "Label": "Backup file suffix",
      "HelpText": "Specify a suffix to add to the backup file names. If left blank the backup will use the current timestamp.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "UseSqlServerTimeStamp",
      "Label": "Use SQL Server timestamp format",
      "HelpText": "If no suffix is specified, use the MSSQL timestamp format.",
      "DefaultValue": "false",
      "DisplaySettings": {
        "Octopus.ControlType": "Checkbox"
      }
    },
    {
      "Name": "ConnectionTimeout",
      "Label": "Connection Timeout",
      "HelpText": "Specify the connection timeout settings (in seconds) for the SQL connection. If the backup takes longer than this value, the backup will fail.",
      "DefaultValue": "36000",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "Incremental",
      "Label": "Backup Action",
      "HelpText": "Specify the Database backup action",
      "DefaultValue": "false",
      "DisplaySettings": {
        "Octopus.ControlType": "Select",
        "Octopus.SelectOptions": "false|Full\ntrue|Log (Incremental)"
      }
    },
    {
      "Name": "CopyOnly",
      "Label": "Copy Only",
      "HelpText": "Specify whether the backup is Copy Only",
      "DefaultValue": "true",
      "DisplaySettings": {
        "Octopus.ControlType": "Checkbox"
      }
    },
    {
      "Name": "RetentionPolicyEnabled",
      "Label": "Retention Policy Enabled",
      "HelpText": "Specify if a limit should be imposed on retaining older backups. Will only be applied if Retention Policy Count is set, and is greater than 0.",
      "DefaultValue": "false",
      "DisplaySettings": {
        "Octopus.ControlType": "Checkbox"
      }
    },
    {
      "Name": "RetentionPolicyCount",
      "Label": "Retention Policy Count",
      "HelpText": "Specify how many old copies of the DB should be retained",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    }
  ],
  "Properties": {
    "Octopus.Action.Script.ScriptBody": "$ErrorActionPreference = \"Stop\"\n$EnableVerboseOutput = $false # pester does not support -Verbose; this is a workaround\n\nfunction ConnectToDatabase() {\n  param($server, $SqlLogin, $SqlPassword, $ConnectionTimeout)\n\n  $server.ConnectionContext.StatementTimeout = $ConnectionTimeout\n\n  if ($null -ne $SqlLogin) {\n\n    if ($null -eq $SqlPassword) {\n      throw \"SQL Password must be specified when using SQL authentication.\"\n    }\n\n    $server.ConnectionContext.LoginSecure = $false\n    $server.ConnectionContext.Login = $SqlLogin\n    $server.ConnectionContext.Password = $SqlPassword\n\n    Write-Host \"Connecting to server using SQL authentication as $SqlLogin.\"\n    $server = New-Object Microsoft.SqlServer.Management.Smo.Server $server.ConnectionContext\n  }\n  else {\n    Write-Host \"Connecting to server using Windows authentication.\"\n  }\n\n  try {\n    $server.ConnectionContext.Connect()\n  }\n  catch {\n    Write-Error \"An error occurred connecting to the database server!`r`n$($_.Exception.ToString())\"\n  }\n}\n\nfunction AddPercentHandler {\n  param($smoBackupRestore, $action)\n\n  $percentEventHandler = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] { Write-Host $dbName $action $_.Percent \"%\" }\n  $completedEventHandler = [Microsoft.SqlServer.Management.Common.ServerMessageEventHandler] { Write-Host $_.Error.Message }\n\n  $smoBackupRestore.add_PercentComplete($percentEventHandler)\n  $smoBackupRestore.add_Complete($completedEventHandler)\n  $smoBackupRestore.PercentCompleteNotification = 10\n}\n\nfunction CreateDevice {\n  param($smoBackupRestore, $directory, $name)\n\n  $devicePath = [System.IO.Path]::Combine($directory, $name)\n  $smoBackupRestore.Devices.AddDevice($devicePath, \"File\")\n  return $devicePath\n}\n\nfunction CreateDevices {\n  param($smoBackupRestore, $devices, $directory, $dbName, $incremental, $timestamp)\n\n  $targetPaths = New-Object System.Collections.Generic.List[System.String]\n\n  $extension = \".bak\"\n\n  if ($incremental -eq $true) {\n    $extension = \".trn\"\n  }\n\n  if ($devices -eq 1) {\n    $deviceName = $dbName + \"_\" + $timestamp + $extension\n    $targetPath = CreateDevice $smoBackupRestore $directory $deviceName\n    $targetPaths.Add($targetPath)\n  }\n  else {\n    for ($i = 1; $i -le $devices; $i++) {\n      $deviceName = $dbName + \"_\" + $timestamp + \"_\" + $i + $extension\n      $targetPath = CreateDevice $smoBackupRestore $directory $deviceName\n      $targetPaths.Add($targetPath)\n    }\n  }\n  return $targetPaths\n}\n\nfunction BackupDatabase {\n  param (\n    [Microsoft.SqlServer.Management.Smo.Server]$server,\n    [string]$dbName,\n    [string]$BackupDirectory,\n    [int]$devices,\n    [int]$compressionOption,\n    [boolean]$incremental,\n    [boolean]$copyonly,\n    [string]$timestamp,\n    [string]$timestampFormat,\n    [boolean]$RetentionPolicyEnabled,\n    [int]$RetentionPolicyCount\n  )\n\n  $smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup\n  $targetPaths = CreateDevices $smoBackup $devices $BackupDirectory $dbName $incremental $timestamp\n\n  Write-Host \"Attempting to backup database $server.Name.$dbName to:\"\n  $targetPaths | ForEach-Object { Write-Host $_ }\n  Write-Host \"\"\n\n  if ($incremental -eq $true) {\n    $smoBackup.Action = \"Log\"\n    $smoBackup.BackupSetDescription = \"Log backup of \" + $dbName\n    $smoBackup.LogTruncation = \"Truncate\"\n  }\n  else {\n    $smoBackup.Action = \"Database\"\n    $smoBackup.BackupSetDescription = \"Full Backup of \" + $dbName\n  }\n\n  $smoBackup.BackupSetName = $dbName + \" Backup\"\n  $smoBackup.MediaDescription = \"Disk\"\n  $smoBackup.CompressionOption = $compressionOption\n  $smoBackup.CopyOnly = $copyonly\n  $smoBackup.Initialize = $true\n  $smoBackup.Database = $dbName\n\n  try {\n    AddPercentHandler $smoBackup \"backed up\"\n    $smoBackup.SqlBackup($server)\n    Write-Host \"Backup completed successfully.\"\n\n    if ($RetentionPolicyEnabled -eq $true) {\n      ApplyRetentionPolicy $BackupDirectory $dbName $RetentionPolicyCount $Incremental $Devices $timestampFormat\n    }\n  }\n  catch {\n    Write-Error \"An error occurred backing up the database!`r`n$($_.Exception.ToString())\"\n  }\n}\n\nfunction ApplyRetentionPolicy {\n  param (\n      [string]$BackupDirectory,\n      [string]$dbName,\n      [int]$RetentionPolicyCount,\n      [bool]$Incremental = $false,\n      [int]$Devices = 1,\n      [string]$timestampFormat = \"yyyy-MM-dd-HHmmss\"\n  )\n\n  # Check if RetentionPolicyCount is defined\n  if (-not $PSBoundParameters.ContainsKey('RetentionPolicyCount')) {\n      Write-Host \"Retention policy not applied as RetentionPolicyCount is undefined.\"\n      return\n  }\n\n  # Set the appropriate file extension\n  $extension = if ($Incremental) { '.trn' } else { '.bak' }\n\n  # Prepare the regex pattern for matching the files\n  $dateRegex = $timestampFormat -replace \"yyyy\", \"\\d{4}\" -replace \"MM\", \"\\d{2}\" -replace \"dd\", \"\\d{2}\" -replace \"HH\", \"\\d{2}\" -replace \"mm\", \"\\d{2}\" -replace \"ss\", \"\\d{2}\"\n  $devicePattern = if ($Devices -gt 1) { \"(_\\d+)\" } else { \"\" }\n  $regexPattern = \"^${dbName}_${dateRegex}${devicePattern}${extension}$\"\n\n  # Get all matching files in the directory\n  $allBackups = Get-ChildItem -Path $BackupDirectory -Filter \"*$extension\" | Where-Object { $_.Name -match $regexPattern }\n\n  # If there are no matching backups, exit\n  if (-not $allBackups) {\n      Write-Host \"No matching backups found.\"\n      return\n  }\n\n  # If RetentionPolicyCount is zero, don't delete or keep any backups\n  if ($RetentionPolicyCount -le 0) {\n      if($EnableVerboseOutput) { # pester does not support -Verbose; this is a workaround\n        Write-Host \"Retention policy not applied as RetentionPolicyCount is set to 0.\"\n      }\n  } elseif ($Devices -gt 1) {\n      # Group by the timestamp part (ignore the device number)\n      $groupedBackups = $allBackups | Group-Object {\n          # Extract the timestamp, ignoring the device part if there are multiple devices\n          ($_.Name -replace \"${devicePattern}${extension}$\", \"\") -replace \"^${dbName}_\", \"\"\n      }\n\n      # Sort the groups by the timestamp\n      $sortedGroups = $groupedBackups | Sort-Object Name\n\n      # Get the groups to keep\n      $groupsToKeep = $sortedGroups | Select-Object -Last $RetentionPolicyCount\n      $filesToKeep = $groupsToKeep | ForEach-Object { $_.Group }\n\n      # Flatten the collection of files to keep, ensuring that FullName is accessed correctly\n      $filesToKeepFlattened = $filesToKeep | ForEach-Object { $_ | Select-Object -ExpandProperty FullName }\n      $filesToDelete = $allBackups | Where-Object { $filesToKeepFlattened -notcontains $_.FullName }\n\n      # Delete the old backups\n      $filesToDelete | ForEach-Object {\n          if($EnableVerboseOutput) { # pester does not support -Verbose; this is a workaround\n            Write-Host \"Deleting old backup: $($_.FullName)\"\n          }\n          Remove-Item -Path $_.FullName -Force\n      }\n\n      # List the files to keep\n      $filesToKeepFlattened | ForEach-Object {\n          Write-Verbose \"Keeping backup: $($_)\"\n      }\n\n      Write-Host \"Retention policy applied. Kept $RetentionPolicyCount most recent backups.\"\n  } else {\n      # Single device: simply sort the backups by timestamp\n      $sortedBackups = $allBackups | Sort-Object Name\n\n      # Get the backups to keep\n      $backupsToKeep = $sortedBackups | Select-Object -Last $RetentionPolicyCount\n      $filesToDelete = $allBackups | Where-Object { $backupsToKeep -notcontains $_ }\n\n      # Delete the old backups\n      $filesToDelete | ForEach-Object {\n        if($EnableVerboseOutput) { # pester does not support -Verbose; this is a workaround\n          Write-Host \"Deleting old backup: $($_.FullName)\"\n        }\n        Remove-Item -Path $_.FullName -Force\n      }\n\n      # List the files to keep\n      $backupsToKeep | ForEach-Object {\n        if($EnableVerboseOutput) { # pester does not support -Verbose; this is a workaround\n          Write-Host \"Keeping backup: $($_.FullName)\"\n        }\n      }\n\n      if($EnableVerboseOutput) { # pester does not support -Verbose; this is a workaround\n        Write-Host \"Retention policy applied. Kept $RetentionPolicyCount most recent backups.\"\n      }\n  }\n}\n\nfunction Invoke-SqlBackupProcess {\n  param (\n    [hashtable]$OctopusParameters\n  )\n\n  # Extracting parameters from the hashtable\n  $ServerName = $OctopusParameters['Server']\n  $DatabaseName = $OctopusParameters['Database']\n  $BackupDirectory = $OctopusParameters['BackupDirectory']\n  $CompressionOption = [int]$OctopusParameters['Compression']\n  $Devices = [int]$OctopusParameters['Devices']\n  $Stamp = $OctopusParameters['Stamp']\n  $UseSqlServerTimeStamp = $OctopusParameters['UseSqlServerTimeStamp']\n  $SqlLogin = $OctopusParameters['SqlLogin']\n  $SqlPassword = $OctopusParameters['SqlPassword']\n  $ConnectionTimeout = $OctopusParameters['ConnectionTimeout']\n  $Incremental = [boolean]::Parse($OctopusParameters['Incremental'])\n  $CopyOnly = [boolean]::Parse($OctopusParameters['CopyOnly'])\n  $RetentionPolicyEnabled = [boolean]::Parse($OctopusParameters['RetentionPolicyEnabled'])\n  $RetentionPolicyCount = [int]$OctopusParameters['RetentionPolicyCount']\n\n  [System.Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.SMO\") | Out-Null\n  [System.Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.SmoExtended\") | Out-Null\n  [System.Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.ConnectionInfo\") | Out-Null\n  [System.Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.SmoEnum\") | Out-Null\n\n  $server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName\n\n  ConnectToDatabase $server $SqlLogin $SqlPassword $ConnectionTimeout\n\n  $database = $server.Databases | Where-Object { $_.Name -eq $DatabaseName }\n  $timestampFormat = \"yyyy-MM-dd-HHmmss\"\n  if ($UseSqlServerTimeStamp -eq $true) {\n    $timestampFormat = \"yyyyMMdd_HHmmss\"\n  }\n  $timestamp = if (-not [string]::IsNullOrEmpty($Stamp)) { $Stamp } else { Get-Date -format $timestampFormat }\n\n  if ($null -eq $database) {\n    Write-Error \"Database $DatabaseName does not exist on $ServerName\"\n  }\n\n  if ($Incremental -eq $true) {\n    if ($database.RecoveryModel -eq 3) {\n      write-error \"$DatabaseName has Recovery Model set to Simple. Log backup cannot be run.\"\n    }\n\n    if ($database.LastBackupDate -eq \"1/1/0001 12:00 AM\") {\n      write-error \"$DatabaseName has no Full backups. Log backup cannot be run.\"\n    }\n  }\n\n  if ($RetentionPolicyEnabled -eq $true -and $RetentionPolicyCount -gt 0) {\n    if (-not [int]::TryParse($RetentionPolicyCount, [ref]$null) -or $RetentionPolicyCount -le 0) {\n      Write-Error \"RetentionPolicyCount must be an integer greater than zero.\"\n    }\n  }\n\n  BackupDatabase $server $DatabaseName $BackupDirectory $Devices $CompressionOption $Incremental $CopyOnly $timestamp $timestampFormat $RetentionPolicyEnabled $RetentionPolicyCount\n}\n\nif (Test-Path -Path \"Variable:OctopusParameters\") {\n  Invoke-SqlBackupProcess -OctopusParameters $OctopusParameters\n}\n",
    "Octopus.Action.Script.Syntax": "PowerShell"
  },
  "Category": "SQL Server",
  "HistoryUrl": "https://github.com/OctopusDeploy/Library/commits/master/step-templates//opt/buildagent/work/75443764cd38076d/step-templates/sql-backup-database.json",
  "Website": "/step-templates/34b4fa10-329f-4c50-ab7c-d6b047264b83",
  "Logo": "iVBORw0KGgoAAAANSUhEUgAAAMgAAADICAMAAACahl6sAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJbWFnZVJlYWR5ccllPAAAAD9QTFRFlZ+r3DAr6p+dy8/V4G9t////5efp9M7NrLS+wCYm8/T1vcPK1tnd10xK+fn6/PLyUU5O+eXk3+Hk7O3u7/DxS2XoPwAADb9JREFUeNrsnYl6nbgOgMEYDHghGN7/Wa8k70B6s3AOZD5o2umcSaf+0S4bUbX/kat6QB6QB+QBeUAekAfkAXlAHpAH5AF5QB6QB+QBeUAekAckXMv4XwBZVCPVnwcZlZSNXRrzp0HGTkqplrY1zfKHQboGMZwoGvVXQUbVy152QaPUu3XrJJCl6Xsp1/SBfbdunQJiZd/3zVqqmfprIEb1iLHRpLF5s279FsQ0iCH3etQ03R8CQYyq74/MwTbN3wGxQFGRRJTaJiVL815z/wXIIiviQEunq2lsNyZhvdfcfw6iCMPavl9H20jkgV8gP1F2NRRJmvEvgIA4gAS0B8xkpexEYWB3F0ijAyOxfwAkcsBvHQk53QWW71HwGm8PIhJHazIS98HYdUqBar1TJD8EYQOABGNe+w0J0dj3iuSHIOMw6PRHOyDpdhggE2XvDmLYAChsDh4MSPI1g92DWkGaosbbey0kARbOyFCaTCYgDemioQWp3D+O9EO4NGNCRpIFMKQzjlG9TyS/iOwoE64jjeaVwICOzjeoGfgue38QshPRMV57lhpVjbNemZTMK7X+gaQRSRgQzaz2JDX9CjRiDvWV+gMgRniSltWMMV0TSo1fcIEjEAKUa7k/CDiomkjaeeAU8JEmoRAOuoLp/hWidTJp9RBiipkF07our9fj/Lpmn51MeM2TnAx5gnp/cRZj6P2aD6BdWoBu1QUeiESwWoCu8a10OBfzHUFaATIxoFssfjIxUKbZiJobkg/ibFSNny2aM/pa4Lt0y4eoWwJkQP9S11NQNoOmw18Ic0qDDsIIg59TiC517aTDa5a7OBDPLDjRBMemmbgTCIhjEINbNVpHLXzozzxAhI4mg9ETv7i4DwhYiHa6JfA2T9F6dPltaDwgBQifwgG5ZOAMlpNAZlrShEpW8ykG/mgkCaMmX40LXwX3uUBR21wLgoYxoMOtc22agpJlGBM5AYF5pcFUwOkXXr8Ty2n7IxrWgze4sIo6WrvD4LNx6pc8QDtzHVA0uwGIcJ6otO4IQhahfZLCtqYjYiUwsOlqEMMp8S31w4MIHrUKv1PvnZlhsUJjF4NAWHQ5PCRUIoGA5XutEpMJsquPFjvzX6GcB2I0Ybg45wWDpi/Iz7K07QPiOfZQEwtls7gShCL6kGe6U4tBg8Bmk7syfSjRpF0glOVCEDT3Mp0KQZyV+cxeswKEjur1baGcuc8O66bQsM10C0Wa6jy4oG2E7gXkXeAxdOdhmLkMBPxWSLJyFj5vBKJLURAGJ58m0NKNcuLh01UgLLvXU87CWSEQVlDUSOHu/gQp2xgaTSAidRFISICjl83UiyVYl3/NIdHiKQZy73pNEIq4BqTNzZht2w8sCISjXWjnqYtcEZtLwTBM9c2Qci5I+ouDYs2sQMGPZxH+Y5kGiFIE6nskp4LwEPcmTpaBd99MqZTiLHPK2wwRDAQq5sxVjeS+enMBSGhAzMRhQsTIUOK1Lz9w2cWHZqy+YSevkMiknWvSMRfZoGg2mX1ecBA6yHupCyRCEqDkasaqMYsYc/LGRwWUmdHd7j4dG/x4ukIiE3HQ382KVDF546NAN9XHSmQsWo65wkbmuFSdxcdCtQ7yKP2ZgzLdx9dc19kSEbFqF0mzdsYuDgydf/I/RW8m324jPGUgPPgsoTPz0Af5MNn0p5ZgZpDJ9F6QfI2ztxQf/TT3DS+2J8Hm8b/sYAJxmXeCzJukikdnpcUUG5BeKKzQnfpf0UJUX4gmpyaNdVoQJlWzYSGGG9I5Fz0mXtoJGEh9sPc70ZZErBrN+0AMyyTCkkEwr1BJe1hOwnfysEiQyl5dMWneqlp8iGGCstyI4YLIVKT4gwfDJmvMTHDrIUP44FWz4JbEe93vnIUJXlSHyUDi92rnps1c+/LcgBiG7OIghqu6KHHXYxZlMsLLfpAzlAGTfjB0ICzlgLq0jqO5rGbnIAudtU+KqpAfKiI25XghCM3cuYlvn34+D2Qil5rqKDZlWRY/BA97CkM4aWRb89Pz2+eBsIHMedab1smks62fogs0+JMSDmL+3RH080B8a9qDCJMVvXrehgiu6yiP+pRN0epEgQi3SeUkkgeXXUOuDmdWBn7Wbuh5Gz2U67JtgsvqomUdtw4RQnNx3hMNJ269QS2iXRN7DrmUmXXGIYr+48knBqoTLUR4xztTXzRU73OgSPvSmov27OscELCEQWBgQM1hrjqc2tR+EPx1ojgVZMJTc+hzQzXl2sCc0pVMFkDRLa85iHbWyQe0Xoau1rkrg0AMk5VU5pJCmeXOILR9CMGCJ7cL5TuDJCVReDe7Aoi5K8hUUwKYc4A0MoXCLRy/+vHOIKBYPnXnbVk7BY1KS78zCKPNJShmY/9pjo0ToJjW/PErtJHxniCCjjtAxMBds9LXcrYCIZjFau4PAqURxwg+bDvvuJ/WdeiiEGW8PYge9GSEL7yjMNxOlLGd87XjGi3jriC4k4tHY8H5Gn94GUtc56QiCBn5eGcQMHRB9epEe2yDE0boe4y2i0f8jUcBkPV2IHg2nmHDkwk+uAqD573Q1dps0WAqYPTLi0L7r0CAAXs4NR3vxy8mi+fDAKRQI0AZ7wgyD7j8AQ/O0bMjrDFL8cjeYu0m+KEDux2IyLo4qFM0Q6R4GKnbgbQ3BDE6UdRsXpxWdblIrN00p0fiuBfIpCMGbtIafHwS8UAkYaHG2uLpRHBcKzqvW4GM6Skxhs62a6R7fh0fPgyZripARnK8NwOJ8gh9UXz00K0fn5p2v1uUXXZp771AhN6cc8PZLt4ejFJ+3INV8fm3cQkl7nqngOj9le7jJ8ARAwgqF0HFhxDHDq775Vp0SgGb/308XEEjg5KLbUgmo1Kdx8hSlRuBOHlU2bPfBp8GzSIGPn1o246e3BvBB9usKLwPCHPHqPAx42C1thAIkTQKn80fF6tsNtHiTiB0imelAQlBIluBOJmAVPBRXWXL6QM3ATGYslPhKpNEmq1AnJ04kI2vvQnIxAftXWofQRYUyGZxOJMDOXZjd+4BYnU6mZdApOw3AulwcAWR2O2ib9EOEoNOSSCqFi1f4ViXbL2Lokki3ka2MrkDiKryg5IIgqePRpxRozYUjmQxi9o+Pb1e3/tVVTG1yaJuGZz2IHt/nGoEN9zQbBe1di53NOCEi3p3vbwbX8oD7n1PkzfwH5RljX7iDs7fMDQ5yHrrtrmpLFeDyKraqDbpFk6pkRKsO04NckYBJW8a5bZCpWh9s7HrXpMzfhVEVdX2RtLENhpJJSWNcUKMkBqqppgTBmKBPGVEVeu68UIQ4NjPLwtjtUg08KOx2dCK3eQ2SOQtSAMkciHIUlX9/tMmkRQUXiB7JwtlbpbPXwBiqqra3cZVxUlnSaPCHwCLPzo/jYp1JUi/U6yuwZltNH6uPxh8YuXRHKcRdMsCSHsViK0KjzUqWSWMvt8bj5EHY3LR3MfWdt1yGUiVCQRFUdGXBNWqjklU6KhkOmUpD4Yqq1uvAmkAZHVdBZrXBhQ0CXcBDmcm2y4c+uHCnGxIVJZNlfVWkIpcVgf330HY0e19UIqyODMpyUGzlkwYWb4FkfFFtv7/QSwtP0CYTFCUxq877VpzgWASmWXAdtN7fCdIUKcyUEBo6StSKU9i8s6Q7Lyboiw4a9JhfL8KpE/j/3Lr7WMzyJHEiqTzAjEuoy+cs/Nc14CYqjoK62AxMnnbPqTAVC+iQHBQOUbFctnYUjFXSYQU6yD36vNAntTL0sCzhvL57d03arfP8GaJVJu/fu03xUnn1KtznSGXCO/vPVYmS3uljWx1q/eRJQ/mfr6sT+ibIy+LFZZpr/VayyZE7lPCzk2XpQmznwxffulova/FkUIk3VFxAiWIT+jlZwOL15eOcftSZK+KpR94MaNkVmF9MggQQ7y5EERVpXKBoZfeyNhYmXjVOjYRTFXaC0G8SIKb2lbvnYzlFU2PX7y977TotZr1FZDFk7ipnoWhLzJUJqBO1BmiXpYfxVyuGzdNzKUglMgHmWQRfWloSDmkYW6BaZwppryeJenYi8eBfqn50ESZNMFARuUyYhnbV2qbBVuXpjQuczdF+nhVO6j3JIszENO4MCkzmx59C3VbpvuWtrUvHr/+9QZdcMPGyUJu2gtyN4U5erV1wZHlLx7H/NWWaRNAKK3fh2572IaIFkNiMXcACb4LKI5KCih8q+PH7QxVV0v36pHlX99WMLLaBfmi8D2I5ytOlZYY6ZtXv2rhOztWNghlp1gdvpxgr1ApnR9f/qaFb+0hRqFsh6tjMNmJIo+J9uWvI/nm9vQaUfIb3JQG0imXz2fRsHn5C2K+e2DArH1QsNhvGKuUR462OWhsr/Llbyf4yaEaGR2Yu83gsVaftLgMUtqN4b/hFR4/O69lk1iUsVTTG+VFofbbz+YN73776VFAH99dG1Iu7l09Uh1bdCdf/wqlXxyXHRML5sD/GBD/jpfx/fJsvOttu589vnXv2KhAIBgYQQNfNg//hBdyQcio+vCjxxpks1gLApmqj+rjox0/5G1BgteVfbaPhTjR6Okwl/kAFtl/9PcGyWqpPutEYFW1dM5CAARkcneJlDwLlVP+dVDhMNdHW8mP45TzriBZ7k+Xi4W9kbMS0v5JkDdeD8gD8oA8IA/IA/KAPCAPyAPygDwgD8gD8oA8IA/IA/IXr/8JMAAhf0RDrOWy2QAAAABJRU5ErkJggg==",
  "$Meta": {
    "Type": "ActionTemplate"
  }
}

History

Page updated on Wednesday, September 11, 2024