sql-servercompatibility-level

How to check safety of changing SQL Server database compatibility level?


I recently took over a SQL Server running SQL Server 2012. One of the databases is running at compatibility level 90 (SQL Server 2005). I've learned that the database originated on a 2005 server and was migrated over. The developers are claiming there's no need to keep it at 90.

Is there a way to check whether any of the code in the database relies on 2005 features or syntax?

I could back up the database and restore a new copy, then convert the copy to 2012 mode, but would that actually tell me anything? I don't want to end up in a situation where I've changed it, then find out days or weeks later that some function or procedure is broken.


Solution

  • You confuse database format version with its compatibility. The latter can be changed at any moment from and to any value supported by your SQL Server instance. So even if something will be broken, which I doubt, you can always return it back.

    Also, before rushing into anything like that, it is usually a good idea to consult with the documentation: Breaking Changes to Database Engine Features