| Author | Chrissy LeMaire (@cl), netnerds.net |
| Availability | Windows, Linux, macOS |
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.
Migrates SQL Server databases from one SQL Server to another.
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.
Copy-DbaDatabase
[-Source <DbaInstanceParameter>]
[-SourceSqlCredential <PSCredential>]
-Destination <DbaInstanceParameter[]>
[-DestinationSqlCredential <PSCredential>]
[-Database <Object[]>]
[-ExcludeDatabase <Object[]>]
[-AllDatabases]
-BackupRestore
[-AdvancedBackupParams <Hashtable>]
[-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>]
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.
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, files copied to the admin share (\SqlSERVER\M$\MSSql...) of destination server, 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.
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.
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).
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
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
PS C:\> Copy-DbaDatabase -Source sql2014a -Destination sqlcluster -BackupRestore -SharedPath \\FS\Backup -AdvancedBackupParams @{ CompressBackup = $true }
Migrates all user databases to sqlcluster. Uses the parameter CompressBackup with the backup command to save some space on the shared path.
PS C:\> Copy-DbaDatabase -Source sqlcs -Destination sqlcs -Database t -DetachAttach -NewName t_copy -Reattach
Copies database t from sqlcs to the same server (sqlcs) using the detach/copy/attach method. The new database will be named t_copy and the original database will be reattached.
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 |
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 either -SharedPath or -UseLastBackup.
Backups will be immediately deleted after use unless -NoBackupCleanup is specified.
| Alias | |
| Required | True |
| Pipeline | false |
| Default Value | False |
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 |
Source SQL Server.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
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 |
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 |
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 |
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 |
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 |
Provide additional parameters to the backup command as a hashtable.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
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 |
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 |
If this switch is enabled, the restore is executed with WITH REPLACE.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
If this switch is enabled, the restore is executed with WITH NORECOVERY. Ideal for staging.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
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 |
Number of files to split the backup. Default is 3.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 3 |
If this switch is enabled, all databases are reattached to Source after DetachAttach migration.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
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 |
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 |
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 |
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 |
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 |
Enables piped input from Get-DbaDatabase
| Alias | |
| Required | False |
| Pipeline | true (ByValue) |
| Default Value |
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 |
Indicates whether CDC information should be copied as part of the database
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Indicates whether replication configuration should be copied as part of the database copy operation
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
If this switch is enabled, the Source database will be set to Offline after being copied.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
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 |
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 |
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 |
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 |
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 |
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 |