Developer Forum

I have read in the newsgroup that someone has suggested that when we detach
a SQL Server 2000 DB and attach it to a SQL Server 2005 Server, it will be
converted to SQL Server 2005 Server (Level 90). However, someone says that
it is still in Level 80 (Nothing has been changed).

I would like to know how many ways to convert 2000 DB to 2005 DB. So far as
I have searched the web, I find that there are 2 ways:

1) In place upgrade of SQL Server 2000 to SQL Server 2005. In this way, all
databases are converted to 2005 level.
2) Use "Copy Database Wizard" in SSMS from SQL Server 2000 to SQL Server
2005

It seems that detach / attach OR backup / restore can convert the SQL Server
2000 DB to SQL Server 2005 DB.

Your advice is sought.

There are three ways:

1. Let the setup program upgrade the whole installation (instance).
2. Backup/restore
3. Detach/attach (this is what Copy Database Wizard is doing)

For all three cases, the database(s) will be left in 80 compatibility mode.

--
Tibor Karaszi, SQL Server MVP
http://www.yqcomputer.com/
http://www.yqcomputer.com/

But how did you get the 2000 db to the 90 compatibility?

My other question is that if the db is at 80 compatibility does it means
that the performance will be less than the 90 sql 2005 db?

Thanks.

You go into the properties of the database, and choose 90 instead of
80, and it switches the comp. level. Not sure about hte performance
question.

It means you won't have access to the new features that are in SQL2005. You
will only be able to use what was there in 2000 when in 80 compatibility
mode.

--
Andrew J. Kelly SQL MVP

Dear Andrew,

If I change it to compatibility mode 90 manually, does it mean that the
features of SQL Server 2005 will be available ?

Thanks

Yes.

--
Tibor Karaszi, SQL Server MVP
http://www.yqcomputer.com/
http://www.yqcomputer.com/
Blog: http://www.yqcomputer.com/