Thursday, November 1, 2007

SQL SERVER - 2005 Change Database Compatible Level - Backward Compatibility

SQL SERVER - 2005 Change Database Compatible Level - Backward Compatibility « Journey to SQL Authority with Pinal Dave

Journey to SQL Authority with Pinal Dave

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 (http://www.SQLAuthority.com) MSDN Article.

No comments: