question

themisist avatar image
themisist asked

When upgrade sql server 2008 to 2017 from backup&restore, result from query compatibility_level from sys.databases doesn't show 140 which represents 2017 version.

I found this sentence "After you restore an earlier version database to SQL Server 2019 (15.x), the database is automatically upgraded." from MS official documents.

However after restroration from backup, I looked into SSMS on the database option tab

and I realized that the compatibility information is not available.

Then I started searching on the internet and finally I got the query below which showed

result as 110 which is sql server version 2008.

SELECT compatibility_level FROM sys.databases WHEREname ='mydatabase'

Was upgrade done? or Do I have another procedures to do more?

Actually I tried alter database compatibility using a sentense below.

ALTERDATABASE mydatabase SET COMPATIBILITY_LEVEL = 140;

I worked and I can see the result 140 from sys.databases.

Is there anyone who can help me with this?

Thanks in advance.


backup-restore
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

The upgrade refers to the database engine version, whereas compatibility levels are more a behaviour configuration. So yes the database has been upgraded to 2017, but the compatibility level of 110 would enable some features to work in a way similar to a 2012 database.

Setting the compatibility level to 140 is what you need to do (and in fact what you did) to get the new database working at the highest available level.

One of the most important uses of compatibility levels on recent versions of SQL server has been to use the legacy cardinality estimator (i.e. SQL 2012 and below) on instances that have been upgraded to 2014 and above.

It's a useful tool to help you with upgrades and migrations as you can move to the new version and keep pre-upgrade behaviour whilst you rework your applications.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.