Manual migration of SQL Server instances are nightmarish. You have to find out each and every login credentials, user mappings, Jobs, etc. Then you have to detach-attach the databases, again create all logins, create mappings, resolve schema related issues, and recreate the jobs, so and so. The process requires a lot of cooperation and support from the application team. But they may have other priorities and there can be some documentation problem too. The downtime is such a scenario will be prolonged and there will be uncertainties too. In a multi-tenant environment, putting all those bits together is just next to impossible.
Chrissy LeMaire has contributed a set of PowerShell scripts to Microsoft Community towards simplifying the process remarkably and reduce downtime in the process. We should really thank her for the excellent contribution towards Microsoft Community as it makes life much easier now for SQL Server admins.
Get the script from the URL –
https://gallery.technet.microsoft.com/scriptcenter/Use-PowerShell-to-Migrate-86c841df. If you simply want to migrate everything from the source SQL server instance, you may invoke the script like the following manner –
[code]
.\Start-SqlServerMigration.ps1 -Source old_sqlserver\old_instance -Destination new_sqlserver\new_instance -DetachAttach -ReAttach -Everything
[/code]
The above script re-attach the databases on the source server to let you compare the newly migrated instance with the source instance. This script support both SQL Server and Windows credentials. If everything goes well, the execution of the script will go like the ones shown below.
