Upgrading to SQL 2016

  • Hi All

    I am upgrading from SQL 2012 Std to SQL 2016 Std.

    A quick question I wanted to run past you guys.....

    Can I take the Master & MSDB mdf's and ldf's from the new 2016 instance and replace it with the mdf's and ldf's from the 2012 instance in attempt to bring across the logins, jobs etc.

    Can this work?

    Any advice would be appreciated.

     

     

    Thanks

  • I'm sure you have your reasons, but why not upgrade to 2017 or 2019?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • App vendor only supports up to SQL 2016...

    • This reply was modified 4 years, 9 months ago by  SQLSACT.
  • Ah yes, I've been in that situation before. It may be that you could run on a higher version, in 2016 compatibility mode.

    But most app vendors I've come across are too cautious/lazy to support that.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • You are not going to be able to bring the master and msdb databases from the 2012 instances to the 2016 instances.

    There are differences in the databases in the various versions, so the new instance may not start if the data files are moved.

    You can do an in-place upgrade, and then move them.  That may work.

     

    I think you need to look into https://dbatools.io/ There are a number of tools they have provided that will make this task pretty easy.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • I'd certainly recommend against trying to restore "master" database to a different instance.  The easiest way to copy the logins is using the technique discussed in this article:

    https://www.mssqltips.com/sqlservertip/3650/script-sql-server-logins-for-disaster-recovery/

    There are other scripts you can run to get server level roles:

    DECLARE @sqlcmd VARCHAR(255)
    DECLARE roles_curs CURSOR FOR
    SELECT 'EXEC sys.sp_addsrvrolemember '''+pr.name+''', '''+pm.name+''''
    FROM sys.server_role_members AS rm
    INNER JOIN sys.server_principals AS pm ON rm.role_principal_id=pm.principal_id
    INNER JOIN sys.server_principals AS pr ON rm.member_principal_id=pr.principal_id
    WHERE pr.name <> 'sa'
    AND pr.name NOT LIKE 'NT SERVICE%'
    AND pr.name NOT LIKE 'NT AUTHORITY%'

    OPEN roles_curs
    FETCH NEXT FROM roles_curs INTO @sqlcmd
    WHILE (@@fetch_status <> -1)
    BEGIN
    PRINT @sqlcmd
    FETCH NEXT FROM roles_curs INTO @sqlcmd
    END
    CLOSE roles_curs
    DEALLOCATE roles_curs

    and the security setup from "msdb"

    USE msdb
    PRINT 'USE msdb;'
    DECLARE @sqlcmd VARCHAR(255)
    DECLARE roles_curs CURSOR FOR
    SELECT 'EXEC sys.sp_addrolemember @rolename = ''' + r.name + ''', @membername = ''' + m.name + ''';' AS sqlcmd
    FROM sys.database_role_members rm
    INNER JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
    INNER JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
    WHERE r.name IN ('DatabaseMailUserRole','SQLAgentUserRole','SQLAgentReaderRole','SQLAgentOperatorRole')
    AND m.name NOT IN ('dc_operator','MS_DataCollectorInternalUser','PolicyAdministratorRole','SQLAgentReaderRole','SQLAgentOperatorRole')

    OPEN roles_curs
    FETCH NEXT FROM roles_curs INTO @sqlcmd
    WHILE (@@fetch_status <> -1)
    BEGIN
    PRINT @sqlcmd
    FETCH NEXT FROM roles_curs INTO @sqlcmd
    END
    CLOSE roles_curs
    DEALLOCATE roles_curs

    All the SQL Agent jobs can be scripted out as well using the Object Explorer Detail window like in this article:

    https://www.sqlmatters.com/Articles/Scripting%20all%20Agent%20Jobs%20Using%20SQL%20Server%20Management%20Studio%20(SSMS).asp

     

  • Thanks All

    I remembered that a few years back I was able to plug the Master & MSDB mdf's and ldf's into a newly installed instance but that was of the same version.

     

     

    Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply