SQL - Backup Database

Octopus.Script exported 2024-03-26 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"

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,
      [boolean]$Incremental,
      [int]$Devices,
      [string]$timestampFormat
  )

  if ($RetentionPolicyCount -le 0) {
      Write-Host "RetentionPolicyCount must be greater than 0. Exiting."
      return
  }

  $extension = if ($Incremental) { '.trn' } else { '.bak' }
  # This pattern helps to isolate the timestamp and possible device part from the filename
  $pattern = '^' + [regex]::Escape($dbName) + '_(\d{4}-\d{2}-\d{2}-\d{6})(?:_(\d+))?' + [regex]::Escape($extension) + '$'

  $allBackups = Get-ChildItem -Path $BackupDirectory -File | Where-Object { $_.Name -match $pattern }

  # Group backups by their base name (assuming base name includes date but not part number)
  $backupGroups = $allBackups | Group-Object { if ($_ -match $pattern) { $Matches[1] } }

  # Sort groups by the latest file within each group, assuming the filename includes a sortable date
  $sortedGroups = $backupGroups | Sort-Object { [DateTime]::ParseExact($_.Name, "yyyy-MM-dd-HHmmss", $null) } -Descending

  # Select the latest groups based on retention policy count
  $groupsToKeep = $sortedGroups | Select-Object -First $RetentionPolicyCount

  # Flatten the list of files to keep
  $filesToKeep = $groupsToKeep | ForEach-Object { $_.Group } | ForEach-Object { $_.FullName }

  # Identify files to remove
  $filesToRemove = $allBackups | Where-Object { $filesToKeep -notcontains $_.FullName }

  foreach ($file in $filesToRemove) {
      Remove-Item $file.FullName -Force
      Write-Host "Removed old backup file: $($file.Name)"
  }

  Write-Host "Retention policy applied successfully. Retained the most recent $RetentionPolicyCount 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": 12,
  "ExportedAt": "2024-03-26T09: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\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      [boolean]$Incremental,\n      [int]$Devices,\n      [string]$timestampFormat\n  )\n\n  if ($RetentionPolicyCount -le 0) {\n      Write-Host \"RetentionPolicyCount must be greater than 0. Exiting.\"\n      return\n  }\n\n  $extension = if ($Incremental) { '.trn' } else { '.bak' }\n  # This pattern helps to isolate the timestamp and possible device part from the filename\n  $pattern = '^' + [regex]::Escape($dbName) + '_(\\d{4}-\\d{2}-\\d{2}-\\d{6})(?:_(\\d+))?' + [regex]::Escape($extension) + '$'\n\n  $allBackups = Get-ChildItem -Path $BackupDirectory -File | Where-Object { $_.Name -match $pattern }\n\n  # Group backups by their base name (assuming base name includes date but not part number)\n  $backupGroups = $allBackups | Group-Object { if ($_ -match $pattern) { $Matches[1] } }\n\n  # Sort groups by the latest file within each group, assuming the filename includes a sortable date\n  $sortedGroups = $backupGroups | Sort-Object { [DateTime]::ParseExact($_.Name, \"yyyy-MM-dd-HHmmss\", $null) } -Descending\n\n  # Select the latest groups based on retention policy count\n  $groupsToKeep = $sortedGroups | Select-Object -First $RetentionPolicyCount\n\n  # Flatten the list of files to keep\n  $filesToKeep = $groupsToKeep | ForEach-Object { $_.Group } | ForEach-Object { $_.FullName }\n\n  # Identify files to remove\n  $filesToRemove = $allBackups | Where-Object { $filesToKeep -notcontains $_.FullName }\n\n  foreach ($file in $filesToRemove) {\n      Remove-Item $file.FullName -Force\n      Write-Host \"Removed old backup file: $($file.Name)\"\n  }\n\n  Write-Host \"Retention policy applied successfully. Retained the most recent $RetentionPolicyCount backups.\"\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 Tuesday, March 26, 2024