Setting Database Compatibility Level for SQL Server 2012

In this blog we shall look at 2 ways to set a database to compatibility level for SQL Server 2012. The Compatibility level for SQL Server 2012 is 110.

Using graphical interface in SSMS

The first method we are going to learn is from SQL Server Management Studio. Connect to the target SQL Server, inside SSMS, expand the SQL Server in Object Explorer and expand the databases node. Right click on the Database for which you would like to change / set the compatibility level

Once the database properties box is displayed, in the left side pane, select Options Page and the database options are displayed as shown below. Then in the right side of the properties box, there is a field Compatibility level (3rd from top), from the drop down list corresponding to Compatibility level choose SQL Server 2012 (110) and click OK button.Using T-SQL

Using T-SQL the same task can be achieved by running ALTER database command and Set the Compatibility_level property to 110 as shown in the below script. Remember to change the  database name with your database name.

ALTER DATABASE LearnSQLWithBru SET COMPATIBILITY_LEVEL = 110
GO

Once you have set the compatibility level, you can check if the changes have been applied by running the below script. Alternately you can check this by looking at the database properties (from object explorer).

SELECT NAME, COMPATIBILITY_LEVEL FROM SYS.DATABASES WHERE
NAME = 'LearnSQLWithBru'

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

  • Sidesoft

    I was trying to restore a bak database from Server 2005 into a new install of 2012 Express and faced nothing but failure…until i read this article. Changed the compatibility level before doing the backup and wala the restore into 2012 was possible. You saved me a lot of frustration!!!

  • http://www.LearnSQLWithBru.com Bru Medishetty

    That’s great to know.. I am glad, this article could help you..

  • Ananova

    Hi,

    When I typed the following query:

    ALTER DATABASE adventureworks SET COMPATIBILITY_LEVEL = 110;

    I got the following error:

    Msg 15048, Level 16, State 1, Line 1

    Valid values of the database compatibility level are 80, 90, or 100.