New-SqlCloneImage
Published 24 February 2017
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> [-Modifications <ModificationScript[]>] [<CommonParameters>] New-SqlCloneImage [-Name] <string> [-SqlServerInstance] <SqlServerInstanceResource> [-BackupFileName] <string[]> [-Destination] <ImageLocationResource> [-BackupPassword <string>] [-Modifications <ModificationScript[]>] [-KeepCdc] [-BufferCount <int>] [-MaxTransferSize <int>] [<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.
| Aliases | None |
| Required? | true |
| Position? | 0 |
| Default Value | None |
| Accept Pipeline Input | false |
| Accept Wildcard Characters | false |
-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.
| Aliases | None |
| Required? | true |
| Position? | 1 |
| Default Value | None |
| Accept Pipeline Input | true (ByValue) |
| Accept Wildcard Characters | false |
-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.
| Aliases | None |
| Required? | true |
| Position? | 2 |
| Default Value | None |
| Accept Pipeline Input | false |
| Accept Wildcard Characters | false |
-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.
| Aliases | None |
| Required? | true |
| Position? | 2 |
| Default Value | None |
| Accept Pipeline Input | false |
| Accept Wildcard Characters | false |
-BackupPassword
<System.String>
Specifies the password for the backup, if the backup is encrypted.
| Aliases | None |
| Required? | false |
| Position? | named |
| Default Value | None |
| Accept Pipeline Input | false |
| Accept Wildcard Characters | false |
-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
| Aliases | None |
| Required? | true |
| Position? | 3 |
| Default Value | None |
| Accept Pipeline Input | false |
| Accept Wildcard Characters | false |
-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.
| Aliases | None |
| Required? | false |
| Position? | named |
| Default Value | None |
| Accept Pipeline Input | false |
| Accept Wildcard Characters | false |
-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.
| Aliases | None |
| Required? | false |
| Position? | named |
| Default Value | False |
| Accept Pipeline Input | false |
| Accept Wildcard Characters | false |
-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.
| Aliases | None |
| Required? | false |
| Position? | named |
| Default Value | None |
| Accept Pipeline Input | false |
| Accept Wildcard Characters | false |
-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.
| Aliases | None |
| Required? | false |
| Position? | named |
| Default Value | None |
| Accept Pipeline Input | false |
| Accept Wildcard Characters | false |
<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 ----------
$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.
