This command provides the ability to easily migrate databases using detach/copy/attach or backup/restore. It works across versions, editions and standalone or clustered instances. Filestreams and filegroups are also migrated. Safety is emphasized. Mirroring and Log shipping support coming soon.
By default, databases will be migrated to the destination SQL Server’s default data and log directories. You can override this by specifying either -ReuseSourceFolderStructure which uses the source structure or with -Replace, which uses the destination SQL structure of databases that currently exist.
To migrate 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 skipped.
Copy-SqlDatabase -Source sqlserver2014a -Destination sqlcluster -Exclude Northwind, pubs, AdventureWorks -BackupRestore -NetworkShare \\fileshare\sql\migration
To migrate SQL Server databases using detach/copy/attach, then reattaching at the source by kicking all users out of the database, detaching all data/log files, moving files across the network over an admin share (\SqlSERVER\M$\MSSql…), attaching files on destination server, reattaching at source. If the database files (.mdf, *.ndf, *.ldf) on *destination exist and aren’t in use, they will be overwritten.
Copy-SqlDatabase -Source sqlserver2014a -Destination sqlcluster -DetachAttach -Reattach -Force
Watch Copy-SqlDatabase in action