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.
Answer by Kev Riley ·
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.