SQL - Restore Database

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

{
  "Id": "469b6d9d-761a-4f94-9745-20e9c2f93841",
  "Name": "SQL - Restore Database",
  "Description": "Restore a MS SQL Server database to the file system.",
  "Version": 4,
  "ExportedAt": "2016-03-24T16:32:09.272+00:00",
  "ActionType": "Octopus.Script",
  "Author": "bobjwalker",
  "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 restore.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "BackupDirectory",
      "Label": "Backup Directory",
      "HelpText": "The backup directory to retrieve the database backup from.",
      "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 current date, in the format given by the DateFormat parameter, is used.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      },
      "Name": "Separator",
      "Label": "Separator",
      "HelpText": "Separator used between database name and suffix.",
      "DefaultValue": "_"
    },
    {
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      },
      "Name": "DateFormat",
      "Label": "Date Format",
      "HelpText": "Date format to use if backup is suffixed with a date stamp (e.g. yyyy-MM-dd)",
      "DefaultValue": "yyyy-MM-dd"
    }
  ],
  "Properties": {
    "Octopus.Action.Script.ScriptBody": "$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$SqlLogin = $OctopusParameters['SqlLogin']\n$SqlPassword = $OctopusParameters['SqlPassword']\n$DateFormat = $OctopusParameters['DateFormat']\n$Separator = $OctopusParameters['Separator']\n$ErrorActionPreference = \"Stop\"\n\nfunction ConnectToDatabase()\n{\n    param($server, $SqlLogin, $SqlPassword)\n        \n    if ($SqlLogin -ne $null) {\n\n        if ($SqlPassword -eq $null) {\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    } 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 = Join-Path $directory ($name)\n    $smoBackupRestore.Devices.AddDevice($devicePath, \"File\")    \n    return $devicePath\n}\n\nfunction CreateDevices {\n    param($smoBackupRestore, $devices, $directory, $dbName)\n        \n    $targetPaths = New-Object System.Collections.Generic.List[System.String]\n    \n    if ($devices -eq 1){\n        $deviceName = $dbName + $Separator + $timestamp + \".bak\"\n        $targetPath = CreateDevice $smoBackupRestore $directory $deviceName\n        $targetPaths.Add($targetPath)\n    } else {\n        for ($i=1; $i -le $devices; $i++){\n            $deviceName = $dbName + \"_\" + $timestamp + \"_\" + $i + \".bak\"\n            $targetPath = CreateDevice $smoBackupRestore $directory $deviceName\n            $targetPaths.Add($targetPath)\n        }\n    }\n    return $targetPaths\n}\n\nfunction RelocateFiles{\n    param($smoRestore)\n    \n    foreach($file in $smoRestore.ReadFileList($server))\n    {\n        $relocateFile = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile\n        $relocateFile.PhysicalFileName = $server.Settings.DefaultFile + $file.LogicalName + [System.IO.Path]::GetExtension($file.PhysicalName)\n        $relocateFile.LogicalFileName = $file.LogicalName\n        $smoRestore.RelocateFiles.Add($relocateFile)\n    }\n}\n\nfunction RestoreDatabase {\n    param($dbName, $devices)\n\n    $smoRestore = New-Object Microsoft.SqlServer.Management.Smo.Restore\n    $targetPaths = CreateDevices $smoRestore $devices $BackupDirectory $dbName $timestamp\n\n    Write-Host \"Attempting to restore database $ServerName.$dbName from:\"\n    $targetPaths\n    Write-Host \"\"\n\n    foreach ($path in $targetPaths) {\n        if (-not (Test-Path $path)) {\n            Write-Host \"Cannot find backup device \"($path)\n            return          \n        }\n    }\n    \n    if ($server.Databases[$dbName] -ne $null)  \n    {  \n        $server.KillAllProcesses($dbName)\n        $server.KillDatabase($dbName)\n    }\n\n    $smoRestore.Action = \"Database\"\n    $smoRestore.NoRecovery = $false;\n    $smoRestore.ReplaceDatabase = $true;\n    $smoRestore.Database = $dbName\n\n    RelocateFiles $smoRestore\n    \n    try {\n        AddPercentHandler $smoRestore \"restored\"        \n        $smoRestore.SqlRestore($server)\n    } catch {\n        Write-Error \"An error occurred restoring the database!`r`n$($_.Exception.ToString())\"\n    }\n        \n    Write-Host \"Restore completed successfully.\"\n}\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\nConnectToDatabase $server $SqlLogin $SqlPassword\n\n$database = $server.Databases | Where-Object { $_.Name -eq $DatabaseName }\n$timestamp = if(-not [string]::IsNullOrEmpty($Stamp)) { $Stamp } else { Get-Date -format $DateFormat }\n\nRestoreDatabase $DatabaseName $Devices",
    "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-restore-database.json",
  "Website": "/step-templates/469b6d9d-761a-4f94-9745-20e9c2f93841",
  "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