How to change compatibility of SQL server 2000 to SQL Server 2005?

kenn

Member

Join Date: Aug 2008

Posts: 36
- Share
- Tweet
#1

How to change compatibility of SQL server 2000 to SQL Server 2005?

08-17-2009, 07:07 PM

Upgrading databases from SQL Server 2000 to SQL Server 2005 using backup and restore method or detach and attach method the compatibility level does not automatically change. Hence, to change the database compatibility, follow below mentioned steps:

1. Login in SQL Server Management Studio.
2. Expand database from the left hand side.
3. Right click on the database.
4. Click on Properties.

5. Click on Option tab.
6. Select SQL Server 2000 (80) from the Compatibility level section as shown in below figure.

7. Click on Ok button.
Tags: None
kilter

Member

Join Date: Oct 2009

Posts: 79
- Share
- Tweet
#2

11-24-2009, 03:04 AM

Re: How to change compatibility of SQL server 2000 to SQL Server 2005?

The database gets converted as soon as you restore it on a SQL Server 2005 instance.
The only effects of the compatibility level are:
* Most new reserved words will not generate an error if used as column or table name (at the cost of not being able to use the new function).
* Some (not all!) behaviour changes of existing functionality are only active in the newer compatibility level (in case you relied on the old
behaviour).
* Some features that have been deprecated for a while already are only available in lower compatibility settings.

Also, changing the compatibility setting won't enumerate any issues. That's what the upgrade advisor is used for.

Last edited by kilter; 11-24-2009, 03:09 AM.
Comment

Unconfigured Ad Widget

How to change compatibility of SQL server 2000 to SQL Server 2005?

Comment