commands

^

Invoke-DbaDbTransfer

Author Kirill Kravtsov (@nvarscar)
Availability Windows, Linux, macOS

 

Want to see the source code for this command? Check out Invoke-DbaDbTransfer on GitHub.
Want to see the Bill Of Health for this command? Check out Invoke-DbaDbTransfer.

Synopsis

Invokes database transfer using a transfer object that clones objects from one database to another.

Description

Invokes database transfer by either accepting an object generated by New-DbaDbTransfer, or generates such object
on the fly when provided with enough parameters. The transfer would follow the rules defined in the transfer object;
the list of such rules could be displayed when listing members of the transfer object:

$transfer = New-DbaDbTransfer -SqlInstance MyInstance -Database MyDB
$transfer | Get-Member

Syntax

Invoke-DbaDbTransfer
    [[-SqlInstance] <DbaInstanceParameter>]
    [[-SqlCredential] <PSCredential>]
    [[-DestinationSqlInstance] <DbaInstanceParameter>]
    [[-DestinationSqlCredential] <PSCredential>]
    [[-Database] <String>]
    [[-DestinationDatabase] <String>]
    [[-BatchSize] <Int32>]
    [[-BulkCopyTimeOut] <Int32>]
    [[-ScriptingOption] <ScriptingOptions>]
    [[-InputObject] <Transfer>]
    [-CopyAllObjects]
    [[-CopyAll] <String[]>]
    [-SchemaOnly]
    [-DataOnly]
    [-ScriptOnly]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> Invoke-DbaDbTransfer -SqlInstance sql1 -DestinationSqlInstance sql2 -Database mydb -CopyAll Tables -DestinationDatabase newdb

Copies all tables from database mydb on sql1 to database newdb on sql2.

Example: 2
PS C:\> Invoke-DbaDbTransfer -SqlInstance sql1 -DestinationSqlInstance sql2 -Database mydb -CopyAllObjects

Copies all objects from database mydb on sql1 to database mydb on sql2.

Example: 3
PS C:\> $transfer = New-DbaDbTransfer -SqlInstance sql1 -DestinationSqlInstance sql2 -Database mydb -CopyAllObjects
PS C:\> $transfer.Options.ScriptDrops = $true
PS C:\> $transfer.SchemaOnly = $true
PS C:\> $transfer | Invoke-DbaDbTransfer

Copies object schema from database mydb on sql1 to database mydb on sql2 using customized transfer parameters.

Example: 4
PS C:\> $options = New-DbaScriptingOption
PS C:\> $options.ScriptDrops = $true
PS C:\> $transfer = New-DbaDbTransfer -SqlInstance sql1 -DestinationSqlInstance sql2 -Database mydb -CopyAll StoredProcedures -ScriptingOption $options
PS C:\> $transfer | Invoke-DbaDbTransfer

Copies procedures from database mydb on sql1 to database mydb on sql2 using custom scripting parameters.

Optional Parameters

-SqlInstance

Source SQL Server instance name.

Alias
Required False
Pipeline false
Default Value
-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

Alias
Required False
Pipeline false
Default Value
-DestinationSqlInstance

Destination Sql Server. You must have appropriate access to create objects on the target server.

Alias
Required False
Pipeline false
Default Value
-DestinationSqlCredential

Login to the source instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Only SQL authentication is supported. When not specified, uses Trusted Authentication.

Alias
Required False
Pipeline false
Default Value
-Database

The database to copy the objects from.

Alias
Required False
Pipeline false
Default Value
-DestinationDatabase

The database to copy the objects to. If not specified, it is assumed to be same as the source database.

Alias
Required False
Pipeline false
Default Value $Database
-BatchSize

The BatchSize for the data copy defaults to 5000.

Alias
Required False
Pipeline false
Default Value 50000
-BulkCopyTimeOut

Value in seconds for the BulkCopy operations timeout. The default is 30 seconds.

Alias
Required False
Pipeline false
Default Value 5000
-ScriptingOption

Custom scripting rules, generated by New-DbaScriptingOption

Alias
Required False
Pipeline false
Default Value
-InputObject

Enables piping of database SMO objects into the command.

Alias
Required False
Pipeline true (ByValue)
Default Value
-CopyAllObjects

Transfer all objects of the source database

Alias
Required False
Pipeline false
Default Value False
-CopyAll

Object types to be transferred from a database. Allowed values:
FullTextCatalogs
FullTextStopLists
SearchPropertyLists
Tables
Views
StoredProcedures
UserDefinedFunctions
UserDefinedDataTypes
UserDefinedTableTypes
PlanGuides
Rules
Defaults
Users
Roles
PartitionSchemes
PartitionFunctions
XmlSchemaCollections
SqlAssemblies
UserDefinedAggregates
UserDefinedTypes
Schemas
Synonyms
Sequences
DatabaseTriggers
DatabaseScopedCredentials
ExternalFileFormats
ExternalDataSources
Logins
ExternalLibraries

Alias
Required False
Pipeline false
Default Value
Accepted Values FullTextCatalogs,FullTextStopLists,SearchPropertyLists,Tables,Views,StoredProcedures,UserDefinedFunctions,UserDefinedDataTypes,UserDefinedTableTypes,PlanGuides,Rules,Defaults,Users,Roles,PartitionSchemes,PartitionFunctions,XmlSchemaCollections,SqlAssemblies,UserDefinedAggregates,UserDefinedTypes,Schemas,Synonyms,Sequences,DatabaseTriggers,DatabaseScopedCredentials,ExternalFileFormats,ExternalDataSources,Logins,ExternalLibraries
-SchemaOnly

Transfers only object schema.

Alias
Required False
Pipeline false
Default Value False
-DataOnly

Transfers only data without copying schema.

Alias
Required False
Pipeline false
Default Value False
-ScriptOnly

Generate the script without moving any objects. Does not include any data - just object definitions.

Alias
Required False
Pipeline false
Default Value False
-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

Alias
Required False
Pipeline false
Default Value False
-WhatIf

If this switch is enabled, no actions are performed but informational messages will be displayed that explain what would happen if the command were to run.

Alias wi
Required False
Pipeline false
Default Value
-Confirm

If this switch is enabled, you will be prompted for confirmation before executing any operations that change state.

Alias cf
Required False
Pipeline false
Default Value