Database still act as though they are running in earlier version even though it has been upgraded to latest version; Setting the Compatibility Mode in SQL Server

Whenever you are attaching or restoring an earlier version of database to the latest version using backup and restore Or using detach and attach method, the compatibility mode of SQL Server does not change automatically.

That’s the reason you are not able to take the advantage of latest feature of latest SQL Server version, and the need arise to change the compatibility level of your database.

Changing the compatibility Mode

Once you restore or attach database, the first thing you need to see the compatibility level for the attached database.

To change the compatibility mode of your database you need

1)       Go to database Node

2)       Go to option

3)       Check the compatibility Property and set the appropriate for your database

The number which are associated with each version of SQL Server are listed below

60 : SQL Server 6.0

65: SQL Server 6.5

70: SQL Server 7.0

80: SQL Server 2000

90: SQL Server 2005

100: SQL Server 2008

110: SQL Server 2012

120: SQL Server 2014

To see/Check the compatibility level of your database

select name,compatibility_level from sys.databases

name                  compatibility_level

--------------------- -------------------

master                120

tempdb                120

model                 120

msdb                  120

TestDB                120

AdventureWorksDW2012  110

AdventureWorks2012    110

AdventureWorks2008R2  100

(8 row(s) affected)

Now here you can see the AdventureWorks2008R2  is on 100 (SQL Server 2008) compatibility level, so if you want to use the feature of SQL Server 2014 you need to set the compatibility level to 120

Changing the Compatibility level of database

use master

go

Alter database AdventureWorks2008R2

set COMPATIBILITY_LEVEL = 120

Command(s) completed successfully.

Now lets see the updated compatibility level of database by querying the sys.databases

select name,compatibility_level from sys.databases

name                  compatibility_level

--------------------- -------------------

master                120

tempdb                120

model                 120

msdb                  120

TestDB                120

AdventureWorksDW2012  110

AdventureWorks2012    110

AdventureWorks2008R2  120

(8 row(s) affected)

So here we can see, the compatibility level of database has changed to 120.

Live with code, that the ultimate source of amusement and pleasure you have on this earth!

 

Post Reference: Vikram Aristocratic Elfin Share