May 1, 2017 at 8:06 am
I have Sql server 2012 SP3 on a Windows server 2008 R2 box. I want to move it over to a Windows server 2012 R2 box with Sql server 2016 SP1 on it. What is the proper steps to get all the logins, jobs and databases over to the new server? If i am correct, I can take backups of user databases and restore it fine on sql server 2016 but not the system databases, correct? Thanks!
May 1, 2017 at 10:51 am
Yes, you can restore backups of user databases to the new SQL Server, and it will perform any needed upgrade as part of the restore process. You don't want to try to restore system databases. The best process is usually to script out all the system type objects from the old instance such as instance configuration settings, logins, stored credentials, database mail profiles and accounts, linked server definitions, SQL Agent operators, SQL Agent jobs, SQL Agent alerts, etc, and then use those scripts to create them on the new instance. Here's a checklist of such items for a migration:
http://www.sqlservercentral.com/articles/Editorial/154033/
Many of the items can be scripted by using the Object Explorer Details window in Management Studio, except for logins:
https://sqlstudies.com/2015/12/16/scripting-out-multiple-scripts-from-the-object-explorer-details-window/
There's a script from Microsoft that helps you in copying the old instance's logins to another instance:
https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server
May 11, 2017 at 2:26 pm
You can also checkout dbatools powershell module. It includes a command, start-sqlmigration, that will migrate everything for you. Or run individual command to migrate objects piecemeal (ie copy-sqllogin, copy-sqldatabase, copy-sqldatabasemail, etc)
June 22, 2017 at 4:00 pm
elee1969 - Monday, May 1, 2017 8:06 AMI have Sql server 2012 SP3 on a Windows server 2008 R2 box. I want to move it over to a Windows server 2012 R2 box with Sql server 2016 SP1 on it. What is the proper steps to get all the logins, jobs and databases over to the new server? If i am correct, I can take backups of user databases and restore it fine on sql server 2016 but not the system databases, correct? Thanks!
The mentioned Start-SqlMigration command would pull pretty much everything under Object Explorer in SSMS from your source server to the destination server you provide.
Another option I use is to piecemeal what I need to be copied over, especially if you need to stage it or test it first before you do the live migration. The list below is in order of precedence, as some commands in dbatools for copying will not copy unless the dependent object exists. The Start command takes care of this but in the piecemeal process, you have to be aware of this and adjust accordingly.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply