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 between instances using backup/restore or detach/attach methods.
Moves user databases from one SQL Server instance to another, supporting both on-premises and Azure SQL Managed Instance destinations. Ideal for server migrations, environment refreshes, disaster recovery testing, and cloud migrations where you need to relocate entire databases with their data and structure intact.
Offers two migration methods: backup/restore (safer, supports cross-version migrations) and detach/attach (faster, requires same SQL Server version). The backup/restore method creates copy-only backups to avoid breaking your existing backup chain, while detach/attach physically moves database files via administrative shares.
Automatically handles file path mapping, preserves database properties like ownership chaining and trustworthy settings, and includes safety checks for Availability Groups, mirroring, and replication. By default, databases are placed in the destination server's default data and log directories, but you can preserve the original folder structure.
Works with named instances, clusters, SQL Server Express Edition, and Azure blob storage for cloud scenarios. Supports multiple destination servers, database renaming, and batch operations for migrating multiple databases efficiently.
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.
Specifies one or more destination SQL Server instances where databases will be migrated.
Supports on-premises instances and Azure SQL Managed Instances for cloud migrations.
When targeting multiple destinations, backups are performed once and shared across all targets.
Alias | |
Required | True |
Pipeline | false |
Default Value |
Uses backup and restore method for database migration, creating copy-only backups to preserve existing backup chains.
This is the safest method for cross-version migrations and works with Azure blob storage.
Requires either -SharedPath for backup location or -UseLastBackup to use existing backups.
Alias | |
Required | True |
Pipeline | false |
Default Value | False |
Uses detach/copy/attach method for database migration by moving physical database files.
This method is faster than backup/restore but requires same SQL Server versions and administrative share access.
Source databases are automatically reattached if destination attachment fails.
Alias | |
Required | True |
Pipeline | false |
Default Value | False |
Specifies the source SQL Server instance containing the databases to migrate.
Supports named instances, clusters, and SQL Server Express editions.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Specifies credentials for connecting to the source SQL Server instance when Windows authentication is not available.
Use this when the source server requires SQL authentication or when running under a different security context.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Specifies credentials for connecting to the destination SQL Server instance when Windows authentication is not available.
Required for Azure SQL Managed Instance destinations or when destination requires SQL authentication.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Specifies which user databases to migrate by name.
Use this when you need to migrate specific databases rather than all databases on the instance.
Supports tab completion from the source instance and accepts multiple database names.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Specifies databases to exclude when using -AllDatabases.
Use this to skip problematic databases like those in use, under maintenance, or containing sensitive data.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Migrates all user databases from the source instance, excluding system databases (master, model, msdb, tempdb).
Use this for full server migrations or when moving all business databases to a new instance.
Alias | All |
Required | False |
Pipeline | false |
Default Value | False |
Specifies additional parameters for the backup operation as a hashtable.
Use this to enable compression (@), checksum verification, or other backup options.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Specifies the storage location accessible by both source and destination SQL Server instances.
Accepts local paths (C:\Backups), UNC shares (\server\backups), or Azure blob storage URLs.
SQL Server service accounts on both instances must have read/write permissions to this location.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Specifies the SQL Server credential name for Azure blob storage authentication.
Required when using storage access keys with Azure blob storage paths.
For SAS tokens, the credential name should match the Azure storage URL.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Overwrites existing databases at the destination with the same name.
Use this when refreshing existing databases or when you want to replace destination databases completely.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Restores databases in NORECOVERY mode, leaving them ready for additional transaction log restores.
Use this for staging environments or when setting up log shipping scenarios.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Preserves backup files after migration instead of automatically deleting them.
Use this when you want to keep backups for additional restores or compliance requirements.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Specifies how many backup files to create for each database backup to improve performance.
Default is 3 files, which provides good parallelism for most databases.
Increase for very large databases or high-performance storage systems.
Alias | |
Required | False |
Pipeline | false |
Default Value | 3 |
Reattaches databases to the source instance after successful detach/attach migration.
Required when using -DetachAttach with multiple destination servers to restore source functionality.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Sets source databases to read-only before migration to prevent data changes during the process.
Use this to ensure data consistency when databases must remain accessible at the source during migration.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Maintains the exact file path structure from the source instance on the destination.
Use this when destination servers have identical drive layouts or when preserving specific organizational folder structures.
The destination instance must have matching directory paths available.
Alias | ReuseFolderStructure |
Required | False |
Pipeline | false |
Default Value | False |
Migrates SQL Server feature databases including ReportServer, ReportServerTempDB, SSISDB, and distribution databases.
Use this when migrating servers that host Reporting Services, Integration Services, or replication components.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Uses existing backups from backup history instead of creating new ones.
The most recent full, differential, and log backups must be accessible to all destination servers.
Useful for migration scenarios where fresh backups already exist.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Continues restoration by applying transaction log backups to databases in RECOVERING or STANDBY states.
Use this with -UseLastBackup when resuming interrupted restore operations or applying additional log backups.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Accepts database objects piped from Get-DbaDatabase for migration.
Use this to migrate databases filtered by specific criteria like size, compatibility level, or other properties.
Alias | |
Required | False |
Pipeline | true (ByValue) |
Default Value |
Creates regular backups instead of copy-only backups, which affects the database's backup chain.
Only use this when you want migration backups to be part of the regular backup sequence.
Default copy-only behavior preserves existing backup chains and is recommended for migrations.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Preserves Change Data Capture (CDC) configuration and data during migration.
Use this when destination databases need to maintain CDC tracking for auditing or replication.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Preserves replication configuration during database migration.
Use this when migrating publisher or subscriber databases that participate in replication topologies.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Sets source databases to offline status after successful migration.
Use this for cutover scenarios where source databases should be unavailable after migration.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Renames the database during migration when copying a single database.
The database name and physical file names are updated to use the new name.
Cannot be used with multiple databases or together with -Prefix parameter.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Adds a prefix to all migrated database names and their physical file names.
Use this to distinguish migrated databases (e.g., 'DEV_' prefix for development copies).
Cannot be used together with -NewName parameter.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Forcibly overwrites existing databases at the destination and bypasses safety checks.
Breaks database mirroring, removes databases from Availability Groups, and rolls back blocking transactions.
Use with caution as this will permanently destroy existing destination databases.
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 |