Data migration from SQL Server 2005 to SQL Server 2022

Question

Hi,

I need to check if it is possible to migrate data from a SQL Server 2005 database to a SQL Server 2022 one.

I'm using SQL Server 2022 and it seems that it isn't possible to work with a compatibility level equals to 90 and so I think that it isn't possible to execute a backup operation in SQL Server 2005 and the next restore operation in SQL Server 2022.

Any suggests to me in order to solve a such issue? Thanks

Answer 1

Sure! Here are the steps in brief:

1. Intermediate Upgrade: Upgrade your SQL Server 2005 database to SQL Server 2008 or 2012 first.
2. Backup and Restore: Take a backup from the intermediate version and restore it on SQL Server 2022.
3. Data Migration Assistant (DMA): Use DMA to assess and assist with the migration.

Answer 2

You can backup the database in SQL 2005 and restore the database on SQL 2022, without any intermediate steps as Kilian suggests. You are correct that there is no compatibility level 90 in SQL 2022, so the compat level will be set to 100, which is the lowest one available.

I see little reason to copy the data in any other way.

Answer 3

Hi @pmscorca,

You can migrate databases from older versions of SQL Server to SQL Server 2022 (16.x), as long as the source database compatibility level is 90 or higher. Databases with a compatibility level of 90 (for example, on SQL Server 2005 (9.x)), are automatically upgraded to a compatibility level of 100 when migrated to SQL Server 2022 (16.x). If the compatibility level on the source database is 100 or higher, it is unchanged on SQL Server 2022 (16.x).

See Supported version and edition upgrades (SQL Server 2022)

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.