How to change SQL Server 2014 database compatibility level

The day before yesterday i have published my thoughts about “Truncate log file up to 504 KB – SQL Server 2008, 2008-R2 & 2012”, today i am going to explain how to change compatibility of SQL Server 2014 databases.

Note:
We need to change compatibility of our earlier version of databases in order to use new features of SQL Server 2014. ( i.e. 120 )

This can be achieve in two ways, one by GUI prompt, means with the help of Microsoft SQL Server Management Studio & second with the help of T-SQL Script.

 

1st: Microsoft SQL Server Management Studio: ( GUI )
To change compatibility of SQL server databases, open Microsoft SQL Server Management Studio & Expand Databases.

Right click on TestDB >> select Properties, as show below:

How to change SQL Server 2014 database compatibility level

How to change SQL Server 2014 database compatibility level

 

On Database Properties wizard, Select Options tab & go for the Compatibility level as SQL Server 2014 (120) and click Ok.

How to change SQL Server 2014 database compatibility level - Database Property

How to change SQL Server 2014 database compatibility level – Database Property

 

2nd: T-SQL

Issue following T-SQL in order to change compatibility of databases:

USE [master]
GO
ALTER DATABASE [TestDB] SET COMPATIBILITY_LEVEL = 120
GO

How to change SQL Server 2014 database compatibility level - T-SQL

How to change SQL Server 2014 database compatibility level – T-SQL

 

By above two methods we can change compatibility of databases in SQL Server 2014 as well as SQL Server 2008, 2008-R2 & 2012.

 

Is your database log file ( .ldf ) size is greater then the data file ( .mdf ) size, then refer my one of the blog to truncate log file up to 504 KB.

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

One thought on “How to change SQL Server 2014 database compatibility level”

Leave a Reply