SQL Sever migration - SQL 2014 to 2022

Question

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.

Answer 1

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.

Answer 2

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.