Redgate - SQL Clone, Create Image

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

{
  "Id": "4ff62eff-f615-453e-9a14-ca7bf67cb586",
  "Name": "Redgate - SQL Clone, Create Image",
  "Description": "Creates a database image with [Redgate SQL Clone](https://www.red-gate.com/products/dba/sql-clone/index).\n\nRequires SQL Clone.\n\n*Version date: 16th May 2019*",
  "Version": 1,
  "ExportedAt": "2019-05-16T11:37:47.360Z",
  "ActionType": "Octopus.Script",
  "Author": "alex-yates",
  "Packages": [],
  "Parameters": [
    {
      "Id": "8c140a4c-65a2-4341-a604-73d14775b3a0",
      "Name": "cloneServer",
      "Label": "SQL Clone Server (required)",
      "HelpText": "The URL for your SQL Clone server (e.g. http://sql-clone.example.com:14145)",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "e5ac1d04-b8a5-440e-ba69-a5d66a53abba",
      "Name": "cloneUser",
      "Label": "SQL Clone User (optional)",
      "HelpText": "User account to access SQL Clone. (If left blank Octopus tentacle account will be used.)",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "5f6288f2-57c9-4a11-91f2-b0c2e3cb9ccd",
      "Name": "clonePassword",
      "Label": "SQL Clone Password (optional)",
      "HelpText": "User account to access SQL Clone. (If left blank Octopus tentacle account will be used.)",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "Sensitive"
      }
    },
    {
      "Id": "ee5286d3-f233-410c-92ab-36743f8743e7",
      "Name": "imageName",
      "Label": "Image Name (required)",
      "HelpText": "The name of your database image.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "0edb1e1f-0c27-428b-9ff0-7c76faf1369e",
      "Name": "imageLocation",
      "Label": "Image Location (required)",
      "HelpText": "Images need to have a UNC (network share) path destination in order to be shared with other machines. For example: \\\\servername\\fileshare\\images",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "aa803a9d-ab55-4e36-969c-3b0e3637d36e",
      "Name": "sourceInstance",
      "Label": "Source SQL Server Instance (required)",
      "HelpText": "For example: MYSERVER\\MYINSTANCE",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "39c29a4a-f483-4b96-bf08-238bb677835d",
      "Name": "sourceType",
      "Label": "Image Source Type (required)",
      "HelpText": "Images can be created from a backup file (.bak or .sqb) or from a SQL Server database.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "Select",
        "Octopus.SelectOptions": "database|SQL Server Database\nbackup|Backup file(s)"
      }
    },
    {
      "Id": "5e9f4d95-4776-4a0e-8245-05d645bd0997",
      "Name": "sourceDatabase",
      "Label": "Source Database (required if Source Type is SQL Server Database)",
      "HelpText": "The database from which to create the image.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "62da0567-35f5-4c3f-ac9e-61b75a399053",
      "Name": "sourceFileNames",
      "Label": "Source File Names (required if Source Type is Backup File(s))",
      "HelpText": "For striped and/or differential backups, separate multiple file paths with ;",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "ab892024-c4b5-46c2-9434-2ad150e3e014",
      "Name": "sourceFilePassword",
      "Label": "Source File Password (optional)",
      "HelpText": "Source backup file password.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "Sensitive"
      }
    },
    {
      "Id": "a758e9de-05c6-4879-8541-d7f73215fe87",
      "Name": "modificationScriptFiles",
      "Label": "Modification Script Files (optional)",
      "HelpText": "SQL scripts and/or Data Masker masking set files (Data Masker must be installed on the clone agent machine), separate multiple file paths with ;",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    }
  ],
  "Properties": {
    "Octopus.Action.Script.ScriptSource": "Inline",
    "Octopus.Action.Script.Syntax": "PowerShell",
    "Octopus.Action.Script.ScriptBody": "$ErrorActionPreference = 'Stop'\n\n# The code for this step template is largely a copy/paste job from the\n# Azure DevOps Services step template which is maintained by Redgate:\n# https://github.com/red-gate/SqlCloneVSTSExtension/blob/master/ImageTask/SQLCloneImageTask.ps1\n# The code was copied and adapted on 16th May 2019.\n\nWrite-Verbose \"cloneServer is $cloneServer\"\nWrite-Verbose \"cloneUser is $cloneUser\"\nWrite-Verbose \"clonePassword is $clonePassword\"\nWrite-Verbose \"sourceType is $sourceType\"\nWrite-Verbose \"imageName is $imageName\"\nWrite-Verbose \"imageLocation is $imageLocation\"\nWrite-Verbose \"sourceInstance is $sourceInstance\"\nWrite-Verbose \"sourceDatabase is $sourceDatabase\"\nWrite-Verbose \"sourceFileNames is $sourceFileNames\"\nWrite-Verbose \"sourceFilePassword is $sourceFilePassword\"\nWrite-Verbose \"modificationScriptFiles is $modificationScriptFiles\"\n\nWrite-Debug \"Entering script SQLCloneImageTask.ps1\"\n\n# This line is broken: Import-Module \"$PSScriptRoot\\Modules\\RedGate.SQLClone.PowerShell.dll\"\n\nif($cloneUser){\n    $password = ConvertTo-SecureString -String $clonePassword -AsPlainText -Force\n    $credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $cloneUser,$password\n\n}\nConnect-SqlClone -ServerUrl $cloneServer -Credential $credential\nWrite-Output \"Connected to SQL Clone server\"\n\n    try\n    {\n        $cloneImageLocation = Get-SqlCloneImageLocation $imageLocation\n        Write-Output \"Found image location\"\n    }\n    catch\n    {\n        $imageLocations = Get-SqlCloneImageLocation\n        $imageLocationNames = \"`n\"\n        Foreach ($cImageLocation in $imageLocations)\n        {\n            $imageLocationNames += $cImageLocation.Path + \"`n\"\n        }\n        $message = 'SQL Clone image location \"' + $imageLocation + '\"  has not been added to SQL Clone, available locations:' + $imageLocationNames\n        write-error $message\n        exit 1\n    }\n\n    $sqlServerParts = $sourceInstance.Split('\\', [System.StringSplitOptions]::RemoveEmptyEntries)\n    if ($sqlServerParts.Count -ge 3)\n    {\n        write-error 'SQL Server instance ' + $sourceInstance + ' has not been recognised, if specifying a named instance please use \"machine\\instance\"'\n        exit 1\n    }\n    $cloneSqlServerHost = $sqlServerParts[0]\n    $instanceName = ''\n    if ($sqlServerParts.Count -ge 2)\n    {\n        $instanceName = $sqlServerParts[1]\n    }\n    \n    try\n    {\n        $instance = Get-SqlCloneSqlServerInstance -MachineName $cloneSqlServerHost -InstanceName $instanceName\n        Write-Output \"Found SQL Server instance\"\n    }\n    catch\n    {\n        $instances = Get-SqlCloneSqlServerInstance\n        $instanceNames = \"`n\"\n        Foreach ($cInstance in $instances)\n        {\n            $instanceNames += $cInstance.Name + \"`n\"\n        }\n        $message = 'SQL Server instance \"' + $sourceInstance + '\"  has not been added to SQL Clone, available instances:' + $instanceNames\n        write-error $message\n        exit 1\n    }\n\n    $modificationScripts = @()\n    if($modificationScriptFiles){\n        $modificationFiles = $modificationScriptFiles.Split(';', [System.StringSplitOptions]::RemoveEmptyEntries)\n            \n        Foreach ($modificationScriptFile in $modificationFiles)\n        {\n            if ($modificationScriptFile -Like \"*.sql\")\n            {\n                $modificationScripts += New-SqlCloneSqlScript -Path $modificationScriptFile\n            }\n\n            if ($modificationScriptFile -Like \"*.dmsmaskset\")\n            {\n                $modificationScripts += New-SqlCloneMask -Path $modificationScriptFile\n            }\n        }\n    }\n    \n    if ($sourceType -eq 'database')\n    {\n        Write-Output \"Source type = database\"\n        Write-Output \"Creating image\"\n        $NewImage = New-SqlCloneImage -Name $imageName -SqlServerInstance $instance -DatabaseName $sourceDatabase -Destination $cloneImageLocation -Modifications $modificationScripts | Wait-SqlCloneOperation    \n        Write-Output \"Finished creating image\"\n    }\n    else\n    {\n        Write-Output \"Source type = backup\"\n        $backupFiles = $sourceFileNames.Split(';', [System.StringSplitOptions]::RemoveEmptyEntries)\n        Write-Output \"Creating image from backup\"\n        if($sourceFilePassword)\n        {\n            $NewImage = New-SqlCloneImage -Name $imageName -SqlServerInstance $instance -BackupFileName $backupFiles -BackupPassword $sourceFilePassword -Destination $cloneImageLocation -Modifications $modificationScripts | Wait-SqlCloneOperation\n        }\n        else\n        {\n            $NewImage = New-SqlCloneImage -Name $imageName -SqlServerInstance $instance -BackupFileName $backupFiles -Destination $cloneImageLocation -Modifications $modificationScripts | Wait-SqlCloneOperation\n        }\n        Write-Output \"Finished creating image from backup\"        \n    }\n\n    \n\nWrite-Debug \"Leaving script SQLCloneImageTask.ps1\"\n"
  },
  "Category": "Redgate",
  "HistoryUrl": "https://github.com/OctopusDeploy/Library/commits/master/step-templates//opt/buildagent/work/75443764cd38076d/step-templates/redgate-sql-clone-create-image.json",
  "Website": "/step-templates/4ff62eff-f615-453e-9a14-ca7bf67cb586",
  "Logo": "iVBORw0KGgoAAAANSUhEUgAAAMgAAADICAMAAACahl6sAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJbWFnZVJlYWR5ccllPAAAAD9QTFRF9s3N7Z6e3U1N87+/+NjY0RIS2Tc3zgAA/O7u425u1SQk5Xp66Y2N/fPz76ys+eHh4F5e0AkJ/vj4/vz8////8g2GDwAABlRJREFUeNrsne2SpCoMhkEQ+RJQ+v6v9YDau+JO97a2IuxJdn7MVk3V+EzSQN6QiB7/iCEAARAAARAAARAAARAAARAAARAAARAAARAAAZAvrOeWUkSIwVprt7bwf2wIQYha3vuCQXpKjFbCNUzKoXtlQysZc05pQygvDmSkWMXnb9vuUwtAkjUKkzJAPKcEizcO+IBIOhycM94I4q1Rjn0D8bRWOmWsvwekx461Z1A8YViDbW6QnmJ3IsPvMGMacZ8NhBshu6usddrmAeGYtd2VNjBFLwcZqbqWYnGLQuOlIFSxLo9JRf1lIKNhQ5fLwuf+KhDbdHmtodeAkMwcXYsBBEAABEAABEAA5DqQdvg3QLjRyjWsrR4kyg29pYhg1ci2bpAFZzoLIywmKWuoF2QlQlgyxZuUlYNs4m2oG2SmmeONxHhjJ8XbLSAbMRufsb7dDbKIwhahyT+Vg/yON2+JVofWt3JA/iie7FvfigR5xtu8vn0mK5ULspb6Poi3KkDmDegZb0PeQ6O9AuYxhngzMusxnm1+eDyvNGtl1sSKpT4hSmFDkB1PIMFD1gwxVWOp69pYm2WNC0Toq2IzlXlT3TS6uNvUApgT2ixXAnaWj1zmnJ0lPrHs5xpt8JFQGBNiP60IZgfZRBdi764ExEsOk5MQLQ9kE13006JJy5rgJEJe3ES5AaRr+N8+pW/vOcw3UcJyR9c3BO4ACT7xSSHreBY1yMbZ+0A2JPS7khy6EWSzM1pZLUjHyKdrV+Egm7WLsGpB0ujyqK0WZPM5MbJakHQ/8YdJ7gfpREoy1ALy6ClCBmMthGNNOEVJlezxpq0FZC2Ohn3QUpSc1Uct5YFyw20gr20MHkOEkPnmcpSAKwVJPbYcBqOQhbESopmikUWvtWsRqFiQt+UUa4PHAphWSjgX2SStECQNRB657HLnr2KQ+xIrAAEQAPnLMtP/IyDkl7Ajp3/N3HGk9GQmOXf1iNrZ+Gy+QJAXgohYa9k2nFBYPFkutE7NpvFshkyGAulYGEg36BWJJ7LbHOuHRX2cTc4WHEtLAwk+4UcO9ag4kDS6et1WC5JGV6/rBQk+WS/RaqgWpGtXvRKei6FakPSXc1EvyGbt+iC6SgUJJIlPhmpBUr1r1EO9IEl0jaJekDS6rKsXJI0u/r4btmyQJLr4j7W558GRFg0STivr6FLxJC/0krmQ5Qw/W58fZDl3x68512CNmOyZZpjl2Q7cSskJwslU7rfx6/SsF8QHAAEQAKkC5BY56BzzHE01rLD9xL3oDjlol42c87DfxKIiHd+KROWV3uKDkzjGaSmsRbExnAUGad6mJ0WAhDBBc5g0c5xMlc7tfXe5ut41/pH85gfpp+KzmYvPjdwxyEKaceWT9m4QJA93H66jq1c3g/hvenUlerlllJ1YvY2uVFqpDGQdXT556tpAkrVrfRW9OpDgkx9J6gNJLnQSWTFIcg3y12GlRpD1dWFvopTB2K1Xyk+JrjGcy36PnKwD5NnAJLChBSRW+0GGOBzTqdjbgywvJkPcAxJiX+koJlr+2UzMckCW8BEaE9rPbdN72hTvB5ladMTSohOf/liXJWW3gbTSOf26aWpnJm9yNlTGybbT3z88/yJ+nNTk6m2bscXVhudHpw4bfu4oFDc5m473fn4/+uMQLBx7MeaifIHuw0EqJYOMNo4aEZ+NGikUhCOs3K7hLyWBPMfxuCMCTCEgzwFJxwfW3A+yP4qKAuGxVyTOLT9riFh+kDGOBdFxhP+pg6lygng+DZ5hlww+ywDS8z2jc8oEsWje0eT1sw4vKytMn2S2500QZYLACFAAARAAARAAARAAKRDEujYrx+Auem1HfDtPRg6pd4mau47xHqlcThn2vqVnbz7CRYZEpJNi93DL3YmVR/rqN/VIceBNdgdSXW+Nuw5lYPjQhfWD4gM37oIQG2Sj7EHJ/6iK4h9Iu1NjbJBOk+PNA9/IQZwSdZIaNzBlKP+m+vKVrjUFAQmeOS5rxTdvBk98P9f1FKWxR0QLx3Yq7sM8+hSd04tykvbrH2MsIJipkPb39VXG2xyEWj4+zrIz1fhlufF+nCYf6blvaepimr/VU/dSf2I5OF/Fyj8yGbzpGEAABEAABEAABEAABEAABEAABEAABEAA5H8F8p8AAwD2WseknbBRCwAAAABJRU5ErkJggg==",
  "$Meta": {
    "Type": "ActionTemplate"
  }
}

History