SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility

sp_dbcmptlevel
Sets certain database behaviors to be compatible with the specified version of SQL Server.

Example:
----SQL Server 2005 database compatible level to SQL Server 2000
EXEC sp_dbcmptlevel AdventureWorks, 80;
GO
----SQL Server 2000 database compatible level to SQL Server 2005
EXEC sp_dbcmptlevel AdventureWorks, 90;
GO

Version of SQL Server database can be one of the following:

• 60 = SQL Server 6.0
• 65 = SQL Server 6.5
• 70 = SQL Server 7.0
• 80 = SQL Server 2000
• 90 = SQL Server 2005

The sp_dbcmptlevel stored procedure affects behaviors only for the specified database, not for the entire server. sp_dbcmptlevel provides only partial backward compatibility with earlier versions of SQL Server. A database containing an indexed view cannot be changed to a compatibility level lower than 80.

The best practice to change the compatibility level of database is in following three steps.

• Set the database to single user access mode by using
  ALTER DATABASE SET SINGLE_USER
• Change the compatibility level of the database.
• Put the database in multiuser access mode by using
  ALTER DATABASE SET MULTI_USER

Reference : Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com) , MSDN Article.