New-DbaDbTransfer

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

Synopsis

Creates a transfer object to clone objects from one database to another.

Description

Returns an SMO Transfer object that controls the process of copying database objects from one database to another.
Does not perform any actions unless explicitly called with .TransferData() or piped into Invoke-DbaDbTransfer.

Syntax

New-DbaDbTransfer
    [[-SqlInstance] <DbaInstanceParameter>]
    [[-SqlCredential] <PSCredential>]
    [[-DestinationSqlInstance] <DbaInstanceParameter>]
    [[-DestinationSqlCredential] <PSCredential>]
    [[-Database] <String>]
    [[-DestinationDatabase] <String>]
    [[-BatchSize] <Int32>]
    [[-BulkCopyTimeOut] <Int32>]
    [[-ScriptingOption] <ScriptingOptions>]
    [[-InputObject] <NamedSmoObject[]>]
    [-CopyAllObjects]
    [[-CopyAll] <String[]>]
    [-SchemaOnly]
    [-DataOnly]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> New-DbaDbTransfer -SqlInstance sql1 -Destination sql2 -Database mydb -CopyAll Tables

Creates a transfer object that, when invoked, would copy all tables from database sql1.mydb to sql2.mydb

Example: 2

PS C:\> Get-DbaDbTable -SqlInstance sql1 -Database MyDb -Table a, b, c | New-DbaDbTransfer -SqlInstance sql1 -Destination sql2 -Database mydb

Creates a transfer object to copy specific tables from database sql1.mydb to sql2.mydb

Optional Parameters

-SqlInstance

Source SQL Server instance name.

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

-DestinationSqlInstance

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

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

-Database

The database to copy the objects from.

-DestinationDatabase

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

-BatchSize

The BatchSize for the data copy defaults to 5000.

-BulkCopyTimeOut

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

-ScriptingOption

Custom scripting rules, generated by New-DbaScriptingOption

-InputObject

Enables piping of database SMO objects into the command.

-CopyAllObjects

Transfer all objects of the source database

-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

-SchemaOnly

Transfers only object schema.

-DataOnly

Transfers only data without copying schema.

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