MS SQL “migration” to older version – problem with Attach

All we need is an easy explanation of the problem, so here it is.

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).

MS SQL “migration” to older version - problem with Attach

I detach this db.

When I try to attach on destination SQL I still get
MS SQL “migration” to older version - problem with Attach

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 ?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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/

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply