locked
Sql 2012 Migration to 2016 RRS feed

  • Question

  • Hello experts - I am planning to migrate our sql 2012 to a new box that has 2016 in our test environment. What are some recommended migration suggestions? Do I script out the logins and move that first or do i restore user databases first? I know with going from 2012 to 2016 I can not restore the system databases because of different versions, correct? Thanks!

    Tuesday, May 9, 2017 1:04 PM

Answers

  • Migrate the databases first. Then migrate the logins. The logins will have default databases. If you script out the logins and migrate them first you will lose the default database setting.

    You can't restore system database on a new system if they are different versions. From:

    https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-system-databases-sql-server

    System databases can be restored only from backups that are created on the version of SQL Server that the server instance is currently running. For example, to restore a system database on a server instance that is running on SQL Server 2012 SP1, you must use a database backup that was created after the server instance was upgraded to SQL Server 2012 SP1.

    I script out all server objects, jobs, linked servers, proxies, etc.

    Tuesday, May 9, 2017 1:14 PM

All replies

  • Migrate the databases first. Then migrate the logins. The logins will have default databases. If you script out the logins and migrate them first you will lose the default database setting.

    You can't restore system database on a new system if they are different versions. From:

    https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-system-databases-sql-server

    System databases can be restored only from backups that are created on the version of SQL Server that the server instance is currently running. For example, to restore a system database on a server instance that is running on SQL Server 2012 SP1, you must use a database backup that was created after the server instance was upgraded to SQL Server 2012 SP1.

    I script out all server objects, jobs, linked servers, proxies, etc.

    Tuesday, May 9, 2017 1:14 PM
  • So everything will function correctly with out restoring the system databases from 2012 to 2016 ? 

    Is there anything i need to worry about with moving logins that use sql and logins that use AD? when scripting logins do the passwords for sql logins need to be reset or does it come over?


    • Edited by mkl69 Tuesday, May 9, 2017 4:00 PM
    Tuesday, May 9, 2017 3:01 PM
  • Hi Emanlee00,

    >>So everything will function correctly without restoring the system databases from 2012 to 2016 ? 

    Should be no issue at all, as long as you don’t have anything you needed stored in system databases(i.e. user defined functions stored in system database). And as Hilary said, you should also script out/recreate all server objects such as SQL Agent jobs, linked servers etc.

    >>Is there anything I need to worry about with moving logins that use SQL and logins that use AD? 

    Not that I’m aware of.

    >>when scripting logins do the passwords for SQL logins need to be reset or does it come over?

    Please review this KB article regarding how to transfer your logins. Personally I’d go with method 2.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 10, 2017 7:32 AM