commands

^

Copy-DbaDatabase

Author Chrissy LeMaire (@cl), netnerds.net
Availability Windows, Linux, macOS

 

Synopsis

Migrates SQL Server databases from one SQL Server to another.

Description

This script provides the ability to migrate databases using detach/copy/attach or backup/restore. This script works with named instances, clusters and SQL Server Express Edition.

By default, databases will be migrated to the destination SQL Server's default data and log directories. You can override this by specifying -ReuseSourceFolderStructure. Filestreams and filegroups are also migrated. Safety is emphasized.

If you are experiencing issues with Copy-DbaDatabase, please use Backup-DbaDatabase | Restore-DbaDatabase instead.

Syntax

Copy-DbaDatabase
    [-Source <DbaInstanceParameter>]
    [-SourceSqlCredential <PSCredential>] -Destination <DbaInstanceParameter[]>
    [-DestinationSqlCredential <PSCredential>]
    [-Database <Object[]>]
    [-ExcludeDatabase <Object[]>]
    [-AllDatabases] -BackupRestore
    [-SharedPath <String>]
    [-AzureCredential <String>]
    [-WithReplace]
    [-NoRecovery]
    [-NoBackupCleanup]
    [-NumberFiles <Int32>]
    [-SetSourceReadOnly]
    [-ReuseSourceFolderStructure]
    [-IncludeSupportDbs]
    [-UseLastBackup]
    [-Continue]
    [-InputObject <Database[]>]
    [-NoCopyOnly]
    [-KeepCDC]
    [-KeepReplication]
    [-SetSourceOffline]
    [-NewName <String>]
    [-Prefix <String>]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Copy-DbaDatabase
    [-Source <DbaInstanceParameter>]
    [-SourceSqlCredential <PSCredential>] -Destination <DbaInstanceParameter[]>
    [-DestinationSqlCredential <PSCredential>]
    [-Database <Object[]>]
    [-ExcludeDatabase <Object[]>]
    [-AllDatabases]
    [-AzureCredential <String>] -DetachAttach
    [-Reattach]
    [-SetSourceReadOnly]
    [-ReuseSourceFolderStructure]
    [-IncludeSupportDbs]
    [-InputObject <Database[]>]
    [-NoCopyOnly]
    [-SetSourceOffline]
    [-NewName <String>]
    [-Prefix <String>]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> Copy-DbaDatabase -Source sql2014a -Destination sql2014b -Database TestDB -BackupRestore -SharedPath \\fileshare\sql\migration

Migrates a single user database TestDB using Backup and restore from instance sql2014a to sql2014b. Backup files are stored in \fileshare\sql\migration.

Example: 2
PS C:\> Copy-DbaDatabase -Source sql2012 -Destination sql2014, sql2016 -DetachAttach -Reattach

Databases will be migrated from sql2012 to both sql2014 and sql2016 using the detach/copy files/attach method.The following will be performed: kick all users out of the database, detach all data/log
files, move files across the network over an admin share (\SqlSERVER\M$\MSSql...), attach file on destination server, reattach at source. If the database files (*.mdf, *.ndf, *.ldf) on destination
exist and aren't in use, they will be overwritten.

Example: 3
PS C:\> Copy-DbaDatabase -Source sql2014a -Destination sqlcluster, sql2016 -BackupRestore -UseLastBackup -Force

Migrates all user databases to sqlcluster and sql2016 using the last Full, Diff and Log backups from sql204a. If the databases exist on the destinations, they will be dropped prior to attach.
Note that the backups must exist in a location accessible by all destination servers, such a network share.

Example: 4
PS C:\> Copy-DbaDatabase -Source sql2014a -Destination sqlcluster -ExcludeDatabase Northwind, pubs -IncludeSupportDbs -Force -BackupRestore -SharedPath \\fileshare\sql\migration

Migrates all user databases except for Northwind and pubs by using backup/restore (copy-only). Backup files are stored in \fileshare\sql\migration. If the database exists on the destination, it will
be dropped prior to attach.
It also includes the support databases (ReportServer, ReportServerTempDb, SSISDB, distribution).

Example: 5
PS C:\> Copy-DbaDatabase -Source sql2014 -Destination managedinstance.cus19c972e4513d6.database.windows.net -DestinationSqlCredential $cred -AllDatabases -BackupRestore -SharedPath

https://someblob.blob.core.windows.net/sql
Migrate all user databases from instance sql2014 to the specified Azure SQL Manage Instance using the blob storage account https://someblob.blob.core.windows.net/sql using a Shared Access Signature
(SAS) credential with a name matching the blob storage account

Example: 6
PS C:\> Copy-DbaDatabase -Source sql2014 -Destination managedinstance.cus19c972e4513d6.database.windows.net -DestinationSqlCredential $cred -Database MyDb -NewName AzureDb -WithReplace -BackupRestore

-SharedPath https://someblob.blob.core.windows.net/sql -AzureCredential AzBlobCredential
Migrates Mydb from instance sql2014 to AzureDb on the specified Azure SQL Manage Instance, replacing the existing AzureDb if it exists, using the blob storage account
https://someblob.blob.core.windows.net/sql using the Sql Server Credential AzBlobCredential

Required Parameters

-Destination

Destination SQL Server. You may specify multiple servers. Note that when using -BackupRestore with multiple servers, the backup will only be performed once and backups will be deleted at the end (if you didn't specify -NoBackupCleanup). When using -DetachAttach with multiple servers, -Reattach must be specified.

Alias
Required True
Pipeline false
Default Value
-BackupRestore

If this switch is enabled, the copy-only backup and restore method will be used to migrate the database(s). This method requires that you specify -SharedPath in a valid UNC format (\server\share). Backups will be immediately deleted after use unless -NoBackupCleanup is specified.

Alias
Required True
Pipeline false
Default Value False
-DetachAttach

If this switch is enabled, the detach/copy/attach method is used to perform database migrations. No files are deleted on Source. If Destination attachment fails, the Source database will be reattached. File copies are performed over administrative shares (\server\x$\mssql) using BITS. If a database is being mirrored, the mirror will be broken prior to migration.

Alias
Required True
Pipeline false
Default Value False

Optional Parameters

-Source

Source SQL Server.

Alias
Required False
Pipeline false
Default Value
-SourceSqlCredential

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

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

Migrates only specified databases. This list is auto-populated from the server for tab completion. Multiple databases may be specified as a collection.

Alias
Required False
Pipeline false
Default Value
-ExcludeDatabase

Excludes specified databases when performing -AllDatabases migrations. This list is auto-populated from the Source for tab completion.

Alias
Required False
Pipeline false
Default Value
-AllDatabases

If this switch is enabled, all user databases will be migrated. System and support databases will not be migrated. Requires -BackupRestore or -DetachAttach.

Alias All
Required False
Pipeline false
Default Value False
-SharedPath

Specifies the network location for the backup files. The SQL Server service accounts must have read/write permission on this path. Can be either a full path 'c:\backups', a UNC path '\server\backups' or an Azure storage Account 'https://example.blob.core.windows.net/sql/'

Alias
Required False
Pipeline false
Default Value
-AzureCredential

The name of the credential on the SQL instance that can write to the AzureBaseUrl, only needed if using Storage access keys If using SAS credentials, the command will look for a credential with a name matching the AzureBaseUrl

Alias
Required False
Pipeline false
Default Value
-WithReplace

If this switch is enabled, the restore is executed with WITH REPLACE.

Alias
Required False
Pipeline false
Default Value False
-NoRecovery

If this switch is enabled, the restore is executed with WITH NORECOVERY. Ideal for staging.

Alias
Required False
Pipeline false
Default Value False
-NoBackupCleanup

If this switch is enabled, backups generated by this cmdlet will not be deleted after they are restored. The default behavior is to delete these backups.

Alias
Required False
Pipeline false
Default Value False
-NumberFiles

Number of files to split the backup. Default is 3.

Alias
Required False
Pipeline false
Default Value 3
-Reattach

If this switch is enabled, all databases are reattached to Source after DetachAttach migration.

Alias
Required False
Pipeline false
Default Value False
-SetSourceReadOnly

If this switch is enabled, all migrated databases are set to ReadOnly on Source prior to detach/attach & backup/restore. If -Reattach is used, databases are set to read-only after reattaching.

Alias
Required False
Pipeline false
Default Value False
-ReuseSourceFolderStructure

If this switch is enabled, databases will be migrated to a data and log directory structure on Destination mirroring that used on Source. By default, the default data and log directories for Destination will be used when the databases are migrated. The structure on Source will be kept exactly, so consider this if you're migrating between different versions and use part of Microsoft's default Sql structure (MSSql12.INSTANCE, etc) To reuse Destination folder structure, use the -WithReplace switch.

Alias ReuseFolderStructure
Required False
Pipeline false
Default Value False
-IncludeSupportDbs

If this switch is enabled, ReportServer, ReportServerTempDb, SSISDB, and distribution databases will be copied if they exist on Source. A log file named $SOURCE-$destinstance-$date-Sqls.csv will be written to the current directory. Use of this switch requires -BackupRestore or -DetachAttach as well.

Alias
Required False
Pipeline false
Default Value False
-UseLastBackup

Use the last full, diff and logs instead of performing backups. Note that the backups must exist in a location accessible by all destination servers, such a network share.

Alias
Required False
Pipeline false
Default Value False
-Continue

If specified, will to attempt to restore transaction log backups on top of existing database(s) in Recovering or Standby states. Only usable with -UseLastBackup

Alias
Required False
Pipeline false
Default Value False
-InputObject

Enables piped input from Get-DbaDatabase

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

If this switch is enabled, backups will be taken without COPY_ONLY. This will break the LSN backup chain, which will interfere with the restore chain of the database. By default this switch is disabled, so backups will be taken with COPY_ONLY. This will preserve the LSN backup chain. For more details please refer to this MSDN article - https://msdn.microsoft.com/en-us/library/ms191495.aspx

Alias
Required False
Pipeline false
Default Value False
-KeepCDC

Indicates whether CDC information should be copied as part of the database

Alias
Required False
Pipeline false
Default Value False
-KeepReplication

Indicates whether replication configuration should be copied as part of the database copy operation

Alias
Required False
Pipeline false
Default Value False
-SetSourceOffline

If this switch is enabled, the Source database will be set to Offline after being copied.

Alias
Required False
Pipeline false
Default Value False
-NewName

If a single database is being copied, this will be used to rename the database during the copy process. Any occurrence of the original database name in the physical file names will be replaced with NewName If specified with multiple databases a warning will be raised and the copy stopped This option is mutually exclusive of Prefix

Alias
Required False
Pipeline false
Default Value
-Prefix

All copied database names and physical files will be prefixed with this string This option is mutually exclusive of NewName

Alias
Required False
Pipeline false
Default Value
-Force

If this switch is enabled, existing databases on Destination with matching names from Source will be dropped. If using -DetachReattach, mirrors will be broken and the database(s) dropped from Availability Groups. If using -SetSourceReadonly, this will instantly roll back any open transactions that may be stopping the process.

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

 

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