MS SQL "migration" to older version - problem with Attach [duplicate]

Question

This question already has answers here:

Restoring a backup to an older version of SQL Server (5 answers)

Closed 4 years ago.

On SQL 2019 I try to change compability level to 2008 (to be sure that I will be able to attach on older version, at least I was in hope this will be possible).

enter image description here

I detach this db.

When I try to attach on destination SQL I still get enter image description here

As so far I found few related topics like this following: Restore DB to older version

But there are talks about Restoring backup, but I ask about Detach/Attach and compability level.

So Here are my questions:

Question 1: Is there any way to attach newest mdb file on older SQL server ?

if NO

Question 2: What for is feature "Compatibility level" ?

After reading both following ansers I have:

Question 3: Do you think that with tools like dbeaver or devart it will be possible to export/import data between NEW<>OLD SQL versions , or would you propose other tools for this kind of job to do ?

• LowlyDBA - John M: VtC as dup - Restoring a backup to an older version of SQL Server - also mentions not being able to attach/detach
• Michał Lipok: Question 3. added
• LowlyDBA - John M: Asking which tools to use ("shopping list") is also an off-topic type of question here, and far too broad for anyone to answer without better understanding what is in your database that would need to be copied anyways. Do some research on importing/exporting and then re-ask if you have a more specific problem.

Answer 1

Question 1 - NO

You cannot restore or attach a database from a newer version of SQL to an older version. The only way to go backwards is to use a bacpac to export the schema and data and then import again.

Question 2 - Compatibility Level

This mainly controls whether certain features are enabled on the database (such as JSON), and it also controls which cardinality engine is used for estimating plans and their various costs.

Question 3 - How to go backwards

I haven't used the tools you referenced so I don't know about them. But I have used bacpac (included with SQL Server) to move databases around before. It works better with smaller databases.

You can also use SSIS to migrate the data although that may be a lot of work compared to just using a bacpac.

https://www.sqlshack.com/importing-a-bacpac-file-for-a-sql-database-using-ssms/

Answer 2

Q1 > No unfortunately not Q2 > Compatibility level is helpful when you want to avoid changes in the query semantics & execution plans when doing a server upgrade

To get your database from a newer version to an older one you will need to script out the schema (tables, views, SPs etc) - remove any new features that are not supported by the older version & script out the data - BCP would help you here.