Database compatibility changing to 100 when restoring SQL 2005 to SQL 2017

Question: Don't ask , but I'm restoring some SQL Server 2005 databases onto a SQL Server 2017 - and  there is some database compatibility  behaviour I was not expecting. When the database is attached to SQL Server 2005 - the database compatibility is 90. But when restored to SQL Server 2017 - the database compatibility is set at 100. Why is this happening?

Answer: This is expected behaviour. i.e when changing from a lower version to a higher version - if the database compatibility of the source db is lower than the lowest compatibility of the target SQL Server version - then the restored database will be changed to the lowest possible compatibility level of the target SQL Server

You can see from this table - you can see the highest possible on SQL 2005 is 90 - but the lowest on SQL 2017 is 100

SQL Server Version             Database Engine Version        Default Compatibility Level           Supported Compatibility Levels

SQL Server 2019                   15                                            150                                                  150, 140, 130, 120, 110, 100

SQL Server 2017                   14                                            140                                                  140, 130, 120, 110, 100

SQL Server 2016                   13                                            130                                                  130, 120, 110, 100

SQL Server 2014                   12                                            120                                                  120, 110, 100

SQL Server 2012                   11                                            110                                                  110, 100, 90

SQL Server 2008 R2              10.5                                         100                                                  100, 90, 80

SQL Server 2008                   10                                            100                                                  100, 90, 80

SQL Server 2005                     9                                              90                                                  90, 80

SQL Server 2000                     8                                              80                                                  80

It's important on all these upgrades ,to complete some performance testing. It is amazing how many issues could be caught early is some sort of regression testing is completed. 

Read more on database compatibility and performance testing

Checking compatibility for a SQL Server database migration to Azure with sqlpackage.exe

Alter database compatibility on all databases in a SQL Server Instance