< Back
Copy-DbaDatabase
Post
NAME Copy-DbaDatabase
SYNOPSIS
Migrates SQL Server databases from one SQL Server to another.
SYNTAX
Copy-DbaDatabase [-Source <DbaInstanceParameter>] [-SourceSqlCredential <Pscredential>] -Destination
<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-DestinationSqlCredential <Pscredential>] [-Database
<System.Object[]>] [-ExcludeDatabase <System.Object[]>] [-AllDatabases <Switch>] -BackupRestore <Switch>
[-SharedPath <String>] [-AzureCredential <String>] [-WithReplace <Switch>] [-NoRecovery <Switch>]
[-NoBackupCleanup <Switch>] [-NumberFiles <Int>] [-SetSourceReadOnly <Switch>] [-ReuseSourceFolderStructure
<Switch>] [-IncludeSupportDbs <Switch>] [-UseLastBackup <Switch>] [-Continue <Switch>] [-InputObject
<Microsoft.SqlServer.Management.Smo.Database[]>] [-NoCopyOnly <Switch>] [-SetSourceOffline <Switch>] [-NewName
<String>] [-Prefix <String>] [-Force <Switch>] [-EnableException <Switch>] [<CommonParameters>]
Copy-DbaDatabase [-Source <DbaInstanceParameter>] [-SourceSqlCredential <Pscredential>] -Destination
<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-DestinationSqlCredential <Pscredential>] [-Database
<System.Object[]>] [-ExcludeDatabase <System.Object[]>] [-AllDatabases <Switch>] [-AzureCredential <String>]
-DetachAttach <Switch> [-Reattach <Switch>] [-SetSourceReadOnly <Switch>] [-ReuseSourceFolderStructure <Switch>]
[-IncludeSupportDbs <Switch>] [-InputObject <Microsoft.SqlServer.Management.Smo.Database[]>] [-NoCopyOnly
<Switch>] [-SetSourceOffline <Switch>] [-NewName <String>] [-Prefix <String>] [-Force <Switch>] [-EnableException
<Switch>] [<CommonParameters>]
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.
PARAMETERS
-AllDatabases [<Switch>]
If this switch is enabled, all user databases will be migrated. System and support databases will not be
migrated. Requires -BackupRestore or -DetachAttach.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-AzureCredential [<String>]
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
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-BackupRestore [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Continue [<Switch>]
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
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Database [<System.Object[]>]
Migrates only specified databases. This list is auto-populated from the server for tab completion. Multiple
databases may be specified as a collection.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Destination [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DestinationSqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DetachAttach [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDatabase [<System.Object[]>]
Excludes specified databases when performing -AllDatabases migrations. This list is auto-populated from the
Source for tab completion.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Force [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeSupportDbs [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InputObject [<Microsoft.SqlServer.Management.Smo.Database[]>]
Enables piped input from Get-DbaDatabase
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NewName [<String>]
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
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoBackupCleanup [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoCopyOnly [<Switch>]
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
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoRecovery [<Switch>]
If this switch is enabled, the restore is executed with WITH NORECOVERY. Ideal for staging.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NumberFiles [<Int>]
Number of files to split the backup. Default is 3.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Prefix [<String>]
All copied database names and physical files will be prefixed with this string
This option is mutually exclusive of NewName
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Reattach [<Switch>]
If this switch is enabled, all databases are reattached to Source after DetachAttach migration.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ReuseSourceFolderStructure [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SetSourceOffline [<Switch>]
If this switch is enabled, the Source database will be set to Offline after being copied.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SetSourceReadOnly [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SharedPath [<String>]
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/'
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Source [<DbaInstanceParameter>]
Source SQL Server.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SourceSqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-UseLastBackup [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-WithReplace [<Switch>]
If this switch is enabled, the restore is executed with WITH REPLACE.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Migration, Backup, Restore
Author: Chrissy LeMaire (@cl), netnerds.net
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
Requires: sysadmin access on SQL Servers
Limitations:
- Doesn't cover what it doesn't cover (replication, certificates, etc)
- SQL Server 2000 databases cannot be directly migrated to SQL Server 2012 and above.
- Logins within SQL Server 2012 and above logins cannot be migrated to SQL Server 2008 R2 and below.
-------------------------- 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 exists 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, 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
RELATED LINKS
https://dbatools.io/Copy-DbaDatabase
SYNOPSIS
Migrates SQL Server databases from one SQL Server to another.
SYNTAX
Copy-DbaDatabase [-Source <DbaInstanceParameter>] [-SourceSqlCredential <Pscredential>] -Destination
<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-DestinationSqlCredential <Pscredential>] [-Database
<System.Object[]>] [-ExcludeDatabase <System.Object[]>] [-AllDatabases <Switch>] -BackupRestore <Switch>
[-SharedPath <String>] [-AzureCredential <String>] [-WithReplace <Switch>] [-NoRecovery <Switch>]
[-NoBackupCleanup <Switch>] [-NumberFiles <Int>] [-SetSourceReadOnly <Switch>] [-ReuseSourceFolderStructure
<Switch>] [-IncludeSupportDbs <Switch>] [-UseLastBackup <Switch>] [-Continue <Switch>] [-InputObject
<Microsoft.SqlServer.Management.Smo.Database[]>] [-NoCopyOnly <Switch>] [-SetSourceOffline <Switch>] [-NewName
<String>] [-Prefix <String>] [-Force <Switch>] [-EnableException <Switch>] [<CommonParameters>]
Copy-DbaDatabase [-Source <DbaInstanceParameter>] [-SourceSqlCredential <Pscredential>] -Destination
<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-DestinationSqlCredential <Pscredential>] [-Database
<System.Object[]>] [-ExcludeDatabase <System.Object[]>] [-AllDatabases <Switch>] [-AzureCredential <String>]
-DetachAttach <Switch> [-Reattach <Switch>] [-SetSourceReadOnly <Switch>] [-ReuseSourceFolderStructure <Switch>]
[-IncludeSupportDbs <Switch>] [-InputObject <Microsoft.SqlServer.Management.Smo.Database[]>] [-NoCopyOnly
<Switch>] [-SetSourceOffline <Switch>] [-NewName <String>] [-Prefix <String>] [-Force <Switch>] [-EnableException
<Switch>] [<CommonParameters>]
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.
PARAMETERS
-AllDatabases [<Switch>]
If this switch is enabled, all user databases will be migrated. System and support databases will not be
migrated. Requires -BackupRestore or -DetachAttach.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-AzureCredential [<String>]
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
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-BackupRestore [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Continue [<Switch>]
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
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Database [<System.Object[]>]
Migrates only specified databases. This list is auto-populated from the server for tab completion. Multiple
databases may be specified as a collection.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Destination [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DestinationSqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DetachAttach [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDatabase [<System.Object[]>]
Excludes specified databases when performing -AllDatabases migrations. This list is auto-populated from the
Source for tab completion.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Force [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeSupportDbs [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InputObject [<Microsoft.SqlServer.Management.Smo.Database[]>]
Enables piped input from Get-DbaDatabase
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NewName [<String>]
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
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoBackupCleanup [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoCopyOnly [<Switch>]
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
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoRecovery [<Switch>]
If this switch is enabled, the restore is executed with WITH NORECOVERY. Ideal for staging.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NumberFiles [<Int>]
Number of files to split the backup. Default is 3.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Prefix [<String>]
All copied database names and physical files will be prefixed with this string
This option is mutually exclusive of NewName
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Reattach [<Switch>]
If this switch is enabled, all databases are reattached to Source after DetachAttach migration.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ReuseSourceFolderStructure [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SetSourceOffline [<Switch>]
If this switch is enabled, the Source database will be set to Offline after being copied.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SetSourceReadOnly [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SharedPath [<String>]
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/'
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Source [<DbaInstanceParameter>]
Source SQL Server.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SourceSqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-UseLastBackup [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-WithReplace [<Switch>]
If this switch is enabled, the restore is executed with WITH REPLACE.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Migration, Backup, Restore
Author: Chrissy LeMaire (@cl), netnerds.net
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
Requires: sysadmin access on SQL Servers
Limitations:
- Doesn't cover what it doesn't cover (replication, certificates, etc)
- SQL Server 2000 databases cannot be directly migrated to SQL Server 2012 and above.
- Logins within SQL Server 2012 and above logins cannot be migrated to SQL Server 2008 R2 and below.
-------------------------- 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 exists 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, 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
RELATED LINKS
https://dbatools.io/Copy-DbaDatabase