Starts creating a new image from either a live database or backup.

Syntax

New-SqlCloneImage [-Name] <string> [-SqlServerInstance] <SqlServerInstanceResource> [-DatabaseName] <string> [-Destination] <ImageLocationResource> [-Modifications <ModificationScriptResource[]>] [-Teams <TeamResource[]>] [-CompactImage <CompactImageResource>] [<CommonParameters>]

New-SqlCloneImage [-Name] <string> [-SqlServerInstance] <SqlServerInstanceResource> [-BackupFileName] <string[]> [-Destination] <ImageLocationResource> [-BackupPassword <string>] [-Modifications <ModificationScriptResource[]>] [-KeepCdc] [-BufferCount <int>] [-MaxTransferSize <int>] [-Teams <TeamResource[]>] [-CompactImage <CompactImageResource>] [<CommonParameters>]

Description

The New-SqlCloneImage cmdlet starts creating a new image from either a live database or backup and outputs details of the creation operation.

This cmdlet returns a OperationResource which can be passed to the Wait-SqlCloneOperation cmdlet.

Connect-SqlClone must be called before this cmdlet.

Parameters

-Name <System.String>

Specifies the name of the image.

-SqlServerInstance <RedGate.SqlClone.Client.Api.Objects.SqlServerInstanceResource>

Specifies the SQL Server instance that the source database is on. The Get-SqlCloneSqlServerInstance cmdlet can be used to get a SqlServerInstanceResource.

-DatabaseName <System.String>

Specifies the name of the database to be used as the source for the image.

You can't use this parameter in addition to the BackupFileName parameters.

-BackupFileName <System.String[]>

Specifies the backup file path(s). These can be full and/or differential, but not a transaction log backup. If you have striped and/or differential backups, you should pass an array of all the files.

You can't use this parameter in addition to the DatabaseName parameter.

-BackupPassword <System.String>

Specifies the password for the backup, if the backup is encrypted.

-Destination <RedGate.SqlClone.Client.Api.Objects.ImageLocationResource>

Specifies where the image will be stored. The Get-SqlCloneImageLocation cmdlet can be used to get an ImageLocationResource

-Modifications <RedGate.SqlClone.Client.Api.Objects.ModificationScriptResource[]>

Specifies an ordered sequence of modifications to make to the image database while it is being created. SQL script modifications will run with limited privileges, as a temporary user & login that has db_owner on the image database and VIEW ANY DEFINITION and VIEW SERVER STATE on the server. Masking modifications will be run by Data Masker for SQL Server.

See New-SqlCloneSqlScript.

-KeepCdc <System.Management.Automation.SwitchParameter>

Specifies that the SQL Server performing the restore should retain Change Data Capture tables in the new image when creating an image from backup.

-BufferCount <System.Int32>

Specifies the Buffer Count to be passed to the SQL Server performing the restore when creating an image from backup.

This sets the total number of I/O buffers to be used, which may affect performance when creating images.

-MaxTransferSize <System.Int32>

Specifies Max Transfer Size to be passed to the SQL Server performing the restore when creating an image from backup

This sets the largest unit of transfer in bytes to be used, which may affect performance when creating images.

-Teams <RedGate.SqlClone.Client.Api.Objects.TeamResource[]>

Specifies Existing Teams that can access this image.

Any team with the `Include All Images` flag will have access to all images.

User must provide a team for the image if the current user doesn't have access to all images.

-CompactImage <RedGate.SqlClone.Client.Api.Objects.CompactImageResource>

Specifies if the image file should be compacted after modifications.

This feature requires the SQL Clone agent machine to be running on Windows 8 / Server 2012 or later.

Possible values: None, Compact

<CommonParameters>

This cmdlet supports the common parameters: -Verbose, -Debug, -ErrorAction, -ErrorVariable, -OutBuffer, and -OutVariable. For more information, see http://technet.microsoft.com/en-us/library/hh847884.aspx.

Inputs

The input type is the type of the objects that you can pipe to the cmdlet.

• RedGate.SqlClone.Client.Api.Objects.SqlServerInstanceResource

  Specifies the SQL Server instance that the source database is on. The Get-SqlCloneSqlServerInstance cmdlet can be used to get a SqlServerInstanceResource.

Return values

The output type is the type of the objects that the cmdlet emits.

• RedGate.SqlClone.Client.Api.Objects.OperationResource

Examples

---------- EXAMPLE 1 ----------

Connect-SqlClone -ServerUrl 'http://sql-clone.example.com:14145'
$SqlServerInstance = Get-SqlCloneSqlServerInstance -MachineName WIN201601 -InstanceName SQL2014
$ImageDestination = Get-SqlCloneImageLocation -Path '\\red-gate\data-images'

$ImageOperation = New-SqlCloneImage -Name "AdventureWorks-$(Get-Date -Format yyyyMMddHHmmss)" `
    -SqlServerInstance $SqlServerInstance `
    -DatabaseName 'AdventureWorks' `
    -Destination $ImageDestination

Wait-SqlCloneOperation -Operation $ImageOperation

This example creates a new image from the 'AdventureWorks' database on WIN201601\SQL2014 and saves it under '\\red-gate\data-images' with a timestamp postfix.

---------- EXAMPLE 2 ----------

Connect-SqlClone -ServerUrl 'http://sql-clone.example.com:14145'
$SqlServer = Get-SqlCloneSqlServerInstance -MachineName 'WIN201601' -InstanceName 'SQL2016'
$ImageDestination = Get-SqlCloneImageLocation -Path '\\red-gate\data-images'

$ImageOperation = New-SqlCloneImage -Name "AdventureWorks-$(Get-Date -Format yyyyMMddHHmmss)" `
    -SqlServerInstance $SqlServer `
    -BackupFileName @('\\red-gate\backups\AdventureWorks-201701012210.bak') `
    -Destination $ImageDestination

$ImageOperation | Wait-SqlCloneOperation

This example creates a new image from the backup file '\\red-gate\backups\AdventureWorks-201701012210.bak' and saves it under '\\red-gate\data-images' with a timestamp postfix.

---------- EXAMPLE 3 ----------

Connect-SqlClone -ServerUrl 'http://sql-clone.example.com:14145'
$SqlServer = Get-SqlCloneSqlServerInstance -MachineName 'WIN201601' -InstanceName 'SQL2016'
$ImageDestination = Get-SqlCloneImageLocation -Path '\\red-gate\data-images'

$ImageOperation = New-SqlCloneImage -Name "Production-$(Get-Date -Format yyyyMMddHHmmss)" `
    -SqlServerInstance $SqlServer `
    -BackupFileName @('\\red-gate\backups\Production-201703201042.sqb') `
    -BackupPassword 'PasswordForSQLBackup' `
    -Destination $ImageDestination

$ImageOperation | Wait-SqlCloneOperation

This example creates a new image from the encrypted SQL Backup file '\\red-gate\backups\Production-201703201042.sqb' and saves it under '\\red-gate\data-images' with a timestamp postfix.

---------- EXAMPLE 4 ----------

Connect-SqlClone -ServerUrl 'http://sql-clone.example.com:14145'
$SqlServer = Get-SqlCloneSqlServerInstance -MachineName 'WIN201601' -InstanceName 'SQL2016'
$ImageDestination = Get-SqlCloneImageLocation -Path '\\red-gate\data-images'

$ImageOperation = New-SqlCloneImage -Name 'ImageFromDifferentialBackup' `
    -SqlServerInstance $SqlServer `
    -BackupFileName @('\\red-gate\backups\FullBackup.bak', '\\red-gate\backups\DifferentialBackup.bak') `
    -Destination $ImageDestination

$ImageOperation | Wait-SqlCloneOperation

This example creates a new image from a differential backup.

---------- EXAMPLE 5 ----------

Connect-SqlClone -ServerUrl 'http://sql-clone.example.com:14145'
$SqlServer = Get-SqlCloneSqlServerInstance -MachineName 'WIN201601' -InstanceName 'SQL2016'
$ImageDestination = Get-SqlCloneImageLocation -Path '\\red-gate\data-images'

$maskingScript = New-SqlCloneSqlScript -Path \\red-gate\data-scripts\mask-emails.sql
$permissionsScript = New-SqlCloneSqlScript -Path \\red-gate\data-scripts\change-permissions.sql

$ImageOperation = New-SqlCloneImage -Name "AdventureWorks-$(Get-Date -Format yyyyMMddHHmmss)" `
    -SqlServerInstance $SqlServer `
    -BackupFileName @('\\red-gate\backups\AdventureWorks-201701012210.bak') `
    -Destination $ImageDestination `
    -Modifications @($maskingScript, $permissionsScript)

$ImageOperation | Wait-SqlCloneOperation

This example creates a new image from a backup file, and runs a pair of SQL scripts (in order) during its creation.

---------- EXAMPLE 6 ----------

Connect-SqlClone -ServerUrl 'http://sql-clone.example.com:14145'
$SqlServer = Get-SqlCloneSqlServerInstance -MachineName 'WIN201601' -InstanceName 'SQL2016'
$ImageDestination = Get-SqlCloneImageLocation -Path '\\red-gate\data-images'

$dropDataScript = New-SqlCloneSqlScript -Path \\red-gate\data-scripts\drop-data.sql
$shrinkScript = New-SqlCloneSqlScript -Path \\red-gate\data-scripts\shrink.sql

$ImageOperation = New-SqlCloneImage -Name "AdventureWorks-$(Get-Date -Format yyyyMMddHHmmss)" `
    -SqlServerInstance $SqlServer `
    -BackupFileName @('\\red-gate\backups\AdventureWorks-201701012210.bak') `
    -Destination $ImageDestination `
    -Modifications @($dropDataScript, $shrinkScript) `
    -CompactImage Compact

$ImageOperation | Wait-SqlCloneOperation

This example creates a new image from a backup file, and runs a pair of SQL scripts (in order) during its creation.

---------- EXAMPLE 7 ----------

$OriginalErrorActionPreference = $ErrorActionPreference
$ErrorActionPreference = "Stop"

Connect-SqlClone -ServerUrl 'http://sql-clone.example.com:14145'
$SqlServer = Get-SqlCloneSqlServerInstance -MachineName 'WIN201601' -InstanceName 'SQL2016'
$ImageDestination = Get-SqlCloneImageLocation -Path '\\red-gate\data-images'

$mask = New-SqlCloneMask -Path \\red-gate\masking-sets\production.dmsmaskset

$ImageOperation = New-SqlCloneImage -Name "AdventureWorks-$(Get-Date -Format yyyyMMddHHmmss)" `
    -SqlServerInstance $SqlServer `
    -BackupFileName @('\\red-gate\backups\AdventureWorks-201701012210.bak') `
    -Destination $ImageDestination `
    -Modifications @($mask)

$ImageOperation | Wait-SqlCloneOperation

$ErrorActionPreference = $OriginalErrorActionPreference

This example creates a new image from a backup file, and invokes Data Masker with a masking set file to sanitize its data. We can use ErrorActionPreference to halt execution on any error, to make sure the image isn't created if there's a problem.

---------- EXAMPLE 8 ----------

Connect-SqlClone -ServerUrl 'http://sql-clone.example.com:14145'
$SqlServerInstance = Get-SqlCloneSqlServerInstance -MachineName WIN201601 -InstanceName SQL2014
$ImageDestination = Get-SqlCloneImageLocation -Path '\\red-gate\data-images'
$Teams = Get-SqlCloneTeam | Where-Object { $_.Name -like 'Dev*' }
$ImageOperation = New-SqlCloneImage -Name "AdventureWorks-$(Get-Date -Format yyyyMMddHHmmss)" `
    -SqlServerInstance $SqlServerInstance `
    -DatabaseName 'AdventureWorks' `
    -Destination $ImageDestination `
    -Teams $Teams

Wait-SqlCloneOperation -Operation $ImageOperation

This example creates a new image that can be access by existing teams with names starting with "Dev"