Upgrading to SQL 2016

Question

Upgrading to SQL 2016

SQLSACT

SSC-Insane

Points: 21838
More actions
March 3, 2020 at 3:08 pm

#3730584

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

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

App vendor only supports up to SQL 2016...

This reply was modified 4 years, 8 months ago by SQLSACT.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Michael L John One Orange Chip Points: 27054 More actions · Answer 4

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/

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 6

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