Migrate from SQLExpress 2017 to SQLExpress2019

Bob Vitter 201 Reputation points
2021-10-30T15:59:57.38+00:00

Can I backup a SQL Express 2017 db and restore to SQL Express 2019 or do I need to use the Migration Assistant?

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

Accepted answer
  1. Erland Sommarskog 116.7K Reputation points MVP
    2021-10-30T18:46:42.223+00:00

    That should work fine. The purpose of the Migration Assistant is to identify things can potentially could be an issue in the migration.

    One word of caution though: before you restore the database, make sure that you have applied the most recent Cumulative Update to SQL 2019 which CU13.

    Keep in mind that when you restore the database, it will remain in compatibiliy level 140. You may want to do.

    ALTER DATABASE db SET COMPATIBILITY_LEVEL = 150
    

    to get access optimizer enhancements in SQL 2019. Although, there is a small risk that the enhancements may backfire.

    The one which is likely to have the biggest impact is inlining of scalar UDFs. There were a couple of issues with this change which have been addressed in a couple of CUs. That's why you should make sure that you have CU13 installed before you restore the database.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Ronen Ariely 15,201 Reputation points
    2021-10-30T18:19:11.33+00:00

    It should work fine in theory

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.