New-SqlCloneImage

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

Syntax

New-SqlCloneImage [-Name] <string> [-SqlServerInstance] <SqlServerInstanceResource> [-DatabaseName] <string> [-Destination] <ImageLocationResource> [-SqlScript <string[]>] [-Modifications <ModificationScript[]>] [<CommonParameters>]

New-SqlCloneImage [-Name] <string> [-SqlServerInstance] <SqlServerInstanceResource> [-BackupFileName] <string[]> [-Destination] <ImageLocationResource> [-BackupPassword <string>] [-SqlScript <string[]>] [-Modifications <ModificationScript[]>] [-KeepCdc] [-BufferCount <int>] [-MaxTransferSize <int>] [<CommonParameters>]

New-SqlCloneImage [-Name] <string> [-BackupLocation] <BackupLocationResource> [-BackupFileName] <string[]> [-Destination] <ImageLocationResource> [-BackupPassword <string>] [-SqlScript <string[]>] [-Modifications <ModificationScript[]>] [<CommonParameters>]

Description

The New-SqlCloneImage cmdlet starts creating a new image from either a live dabase 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.

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

-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 BackupLocation and BackupFileName parameters.

-BackupLocation <RedGate.SqlClone.Client.Api.Objects.BackupLocationResource>

DEPRECATED: Pass full paths to your backup file(s) using the BackupFileName argument instead.

Specifies location of the backup files. The Get-SqlCloneBackupLocation cmdlet can be used to get a BackupLocationResource.

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

-BackupFileName <System.String[]>

Specifies the backup file path(s). 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

-SqlScript <System.String[]>

DEPRECATED (use Modifications): Specifies the body of T-SQL script(s) to run on the image database while it is being created. The script 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.

-Modifications <RedGate.SqlClone.Api.ScriptRunning.ModificationScript[]>

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.

<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.

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

• RedGate.SqlClone.Client.Api.Objects.BackupLocationResource

  DEPRECATED: Pass full paths to your backup file(s) using the BackupFileName argument instead.

  Specifies location of the backup files. The Get-SqlCloneBackupLocation cmdlet can be used to get a BackupLocationResource.

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

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 ----------

$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.