SQL Sever migration - SQL 2014 to 2022

Radhai Krish 191 Reputation points
2024-06-24T06:12:54.36+00:00

Checking on migration option for SQL Server upgrade from SQL 2014 to SQL 2022. Installing separately 2022 in the same server and move the DBs & setups to the new Server.

It is mentioned here, that

"System databases can be restored only from backups that are created on the version of SQL Server that the server instance is currently running"

When the above limitation is there, how can we migrate master & msdb databases from 2014 to 2022?

I couldn't get accurate answers from AI assist Q&A. So, please help.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,328 questions
0 comments No comments
{count} votes

Accepted answer
  1. MikeyQiaoMSFT-0444 1,755 Reputation points
    2024-06-24T08:59:37.0166667+00:00

    Hi,Radhai Krish

    If you must perform cross-version migration in one step, it is generally recommended to manually migrate specific objects from the master and msdb databases, such as logins, jobs, maintenance plans, database mail, alerts, etc., rather than directly migrating the entire database.- master(Logins and User Database Roles and Permissions, Server-Level Configurations,, and Maintenance Plans):

    Rebuild system databases

    Transfer logins and passwords between instances of SQL Server

    • msdb(Jobs,DBMail,Alerts,Operators......):

    Jobs:

    Using SSMS to Export Job Scripts:

    1. Open SQL Server Management Studio
    2. Expand SQL Server Agent -> Jobs
    3. Right-click the job you need to migrate, select "Script Job as" -> "CREATE To" -> "New Query Editor Window"
    4. Copy the generated script and execute it on the target server

    DBMail:

    Generate Database Mail Configuration Script

    EXEC msdb.dbo.sysmail_help_configure_sp;
    EXEC msdb.dbo.sysmail_help_profile_sp; 
    EXEC msdb.dbo.sysmail_help_account_sp; 
    EXEC msdb.dbo.sysmail_help_principalprofile_sp;
    

    ........

    Manually migrating the msdb and master databases is an extremely complex process that depends on the architecture of the original database.

    The best recommendation here is to install the same version of the database on the new server. Then, back up and restore the databases, and finally perform an in-place upgrade to the corresponding version.

    Best Regards,

    Mikey Qiao


    If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 106.5K Reputation points
    2024-06-24T21:27:26.49+00:00

    I think what Mikey means is that you install SQL 2014 on your new machine. Then you can restore master and msdb on that instance. Then you perform an in-place upgrade of that instance in its entirety. (You cannot upgrade only the system databases.) While is doable, I can see that you may prefer to have a clean server without no traces of SQL 2014.

    If the current server is virtualised, you could clone that VM, and perform an in-place upgrade on the clone, and then store the system database to the actual instance.

    If the current server is physical, you could take some temporary (virtual) server and install SQL 2014 on that server, restore the system databases from the source server, upgrade to SQL 2022, and then backup the system databases to restore on the target system.

    2 people found this answer helpful.