SQL SERVER – Change The Compatibility Level OF Database

Question: How to change the compatibility level of Database in SQL Server 2005

Dear friends we can change the compatibility level of Database in SQL Server 2005/08 using two ways, as follows-

Method 1) Using SSMS Wizards

1)      Open the SSMS

2)      Right click on the database

3)      Click on the Properties Option (New Properties window will open)

4)      Click on the ‘Option’ Page which is showing on Left side in window

5)       After that you will find the Collation, Recovery Model Type and ‘Compatibility Level’ Drop down List.

6)      Select ‘Compatibility Level’ and

7)      Press OK Button.

Method 2) Using SSMS Commands (Try this also)

I always perfered this second Method to change the compatibility level of Database in SQL Server 2005. There are following some step to perform the task.

1)       First of all you have to set the database in Single User Mode (Access Mode).

2)       There after you can change the compatibility level of the database.

3)       And when compatibility level of Database will changed, you have to change the Access Mode in Multi-User Access Mode.

To perform this three step using this following T-SQL script. Run this T-SQL script SSMS.

-- Select the database

Use AdventureWorks

GO

-- Changing the Single User Access Mode

ALTER DATABASE AdventureWorks

SET SINGLE_USER

GO

-- Now Changing the Compatibility Level

EXEC sp_dbcmptlevel AdventureWorks, 90;

GO

-- Again changing the Single User Access Mode to Multi User Mode

ALTER DATABASE AdventureWorks

SET MULTI_USER

GO