How to Change SQL Database Compatibility Level

Plesk Servers > SQL > MSSQL

How to Change SQL Database Compatibility Level

Whenever we set up a new customer, we always set your database to use the latest SQL Server compatibility level. However, if you are migrating an existing site to us, we leave the compatibility level at the value set by your old host to ensure compatibility in your environment.

If you need to change your compatibility level follow the instructions below.

Note:  You will need to have Microsoft SQL Server Management Studio installed, and be able to connect to your database.  If you are unsure of how to connect to your database please refer to the article located at : /KB/a133/how-to-connect-to-your-ms-sql-2008-database-disable.aspx

Always back up the website before making any changes to the database . Shared hosting customers can do this through the Control Panel. Refer to Back Up Your Website Using Plesk. Dedicated server customers can back up the site either through the Control Panel, or through the Control Suite. Refer to How to Back Up a Domain Using Control Suite.

 

  1. Open Microsoft SQL Server Management Studio
  2. Click the New Query button
  3. Paste the following script into your SQL Query page

 

 

declare @DBName nvarchar(1000)
declare @CompatibilityMode int
set @DBName = db_name()
set @CompatibilityMode = 90
EXEC sp_dbcmptlevel @DBName, @CompatibilityMode;


Please set @CompatibilityMode to one of the following values:

 

 

  • 80 -   This means SQL 2000 (You will have to be on a SQL 2000 or above server for this to work)
  • 90 -   This means SQL 2005 (You will have to be on a SQL 2005 or above server for this to work)
  • 100 - This means SQL 2008 (You will have to be on a SQL 2008 or above server for this to work)
  • 110 - This means SQL 2012 (You will have to be on a SQL 2012 or above server for this to work)

You should not change your compatibility mode unless it is necessary.

 

Add Feedback