Share via

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 | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

0 comments

Answer accepted by question author

AI answer

2023-10-06T04:13:12.8233333+00:00

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:

AI-generated content may be incorrect. Read our transparency notes for more information.

Was this answer helpful?

1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 134.4K Reputation points MVP Volunteer Moderator
    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.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Olaf Helper 47,616 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.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments

Your answer

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