Does SQL Server database compatibility level negate SQL Server version compatibility

Question

For instance I have an application that says it supports SQL Server 2005 to SQL Server 2008. If I transfer that database to a SQL Server 2017 machine and set the compatibility mode to 2008, should it behave just the same as if it were really on a SQL Server 2008 box? I know that the compatibility mode does limit the feature set and behaviors to what would be present in SQL Server 2008. So part of me thinks this should work just fine. But another part of me wonders if there may be others things that I am not considering that could potentially be an issue. Does anyone have any experience where this has come back to bite you?

Answer 1

"Upgrade the database to a SQL 2017 server and set the compatibility mode to 2008 should it behave just the same as if it were really on a SQL 2008 server"

Yes this is correct.

This also happens to be Microsoft's recommended upgrade process. As per Microsoft, upgrade to the latest version off SQL Server but keep the source database compatability level.

If a full database backup was taken on a older version of SQL server but restore was done on a new version, then the database compatability level is the same as old version unless its not supported on new version. In that case the database compatability level will switch to the lowest supported.

More here and here