SQL Interview: 34 Restore SQL Server database back to older version

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Medium

Question:

You have a database attached to a SQL Server 2022 server.

You need a copy of the database on a SQL Server 2017 server.

How can you do that?

Answer:

It’s messy.

You cannot restore a backup from a later version to an earlier version, even if the database compatibility level is at the lower version.

You also cannot restore a bacpac from a later version to an earlier version.

You need to take an action to copy the schema and data to the older system. You could script the database, using the option to script for an earlier version, and use it to provide the structure of the migrated database. You can then use any tool that can copy data to migrate the data.

You might have challenges with table order, if there are foreign keys in place, or they might need to be disabled during the copy, then rechecked later.

Another option would be to temporarily enable transactional replication, and replicate the tables and data to the other database, then remove it, and use a database comparison tool to check for any other changes that the schema would require.

2025-04-08