Can database created in SQL Server 2017 be used in SQL Server 2022?

Nishida Nobuko 20 Reputation points
2023-10-06T04:13:12.82+00:00

Can databases created in SQL Server 2017 be used in 2022?

If I use SQL Server 2022, Do I have to set ?

Is there anything I should be careful about?

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

Accepted answer
  1. Q&A Assist
    2023-10-06T04:13:12.8233333+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    Yes, databases created in SQL Server 2017 can be used in SQL Server 2022. SQL Server 2022 (16.x) is designed to be backward compatible with earlier versions of SQL Server. However, it is recommended to test your applications and databases for compatibility before upgrading to a new version of SQL Server. As for any precautions, it is always a good practice to back up your databases before performing any upgrades or migrations.


    References:

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Olaf Helper 41,331 Reputation points
    2023-10-06T05:21:13.46+00:00

    Is there anything I should be careful about?

    Once the database is attached to a newer SQL Server version, the database gets's automatically upgrade, then you can't go with it back to an earlier version.

    Best way to transfer is backup&restore and keep the backup for a while.

    1 person found this answer helpful.
    0 comments No comments

  2. Erland Sommarskog 102.3K Reputation points
    2023-10-06T21:18:10.88+00:00

    Adding to the AI-provided answer:

    When you upgrade the database to SQL 2022, the database will stay in compatibility level 140. I would recommend that you turn on Query Store and the keep the database in compat level 140 for a week or two. Then you run

    ALTER DATABASE db SET COMPATIBILITY_LEVEL = 160
    

    This will give you access to optimizer enhancements in SQL 2019 and SQL 2022, so you could expect better performance. However, optimization is based to estimates from statistics that is often sampled and these improvements may backfire, and you could have a query that seriously degrades in performance. If this happens, you can use Query Store to force the old plan, to quickly get out of the situation.

    SSMS offers support for this strategy. Select Tasks from the context menu for the database, and at the bottom of this submenu, you find Database Upgrade.

    1 person found this answer helpful.
    0 comments No comments