dbatools is a free PowerShell module with over 100 SQL Server administration, best practice and migration commands included.


Copy-SqlDatabase

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.

Examples

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

From PowerShell, execute   Get-Help Copy-SqlDatabase -Detailed   for more information on this function. Want to see the source code? See it on GitHub