sql 2k5 compatibility level option...

27 Jul 2006 10:15 PM

=== Steve L ===

if I back up a database in sql2k then restore it on sql2k5, does it
make the database sql2k5 automatically? wihen looking at the database
properties, there is compatibility level option for sql2000 and
sql2005, what is the difference? i noticed the default is sql2000.

thank you

28 Jul 2006 12:09 AM

Andrew J. Kelly

The database internal objects are upgraded to 2005 at the time you restore
or attach it on a 2005 server. It will put the compatibility mode to 8.0 on
upgraded dbs and 90 on newly created ones. The difference is basically in
how it behaves and what commands you can or can not use. In 80 compatibility
mode you won't have access to most of the new 2005 features.

--
Andrew J. Kelly SQL MVP

Show quote

"=== Steve L ===" <steve.***@powells.com> wrote in message
news:1154038501.740045.14090@h48g2000cwc.googlegroups.com...
> if I back up a database in sql2k then restore it on sql2k5, does it
> make the database sql2k5 automatically? wihen looking at the database
> properties, there is compatibility level option for sql2000 and
> sql2005, what is the difference? i noticed the default is sql2000.
>
> thank you
>

1 Aug 2006 10:10 PM

=== Steve L ===

well, then what the compatibility mode 8.0 is for? since one can't
manage it using SQL2k Enterprise manager. i just wonder, under what
circumstance would a person use sql2k5 SSMS to manage a compatibility
8.0 database?!

Andrew J. Kelly wrote:
Show quote

> The database internal objects are upgraded to 2005 at the time you restore
> or attach it on a 2005 server. It will put the compatibility mode to 8.0 on
> upgraded dbs and 90 on newly created ones. The difference is basically in
> how it behaves and what commands you can or can not use. In 80 compatibility
> mode you won't have access to most of the new 2005 features.
>
> --
> Andrew J. Kelly SQL MVP
>
> "=== Steve L ===" <steve.***@powells.com> wrote in message
> news:1154038501.740045.14090@h48g2000cwc.googlegroups.com...
> > if I back up a database in sql2k then restore it on sql2k5, does it
> > make the database sql2k5 automatically? wihen looking at the database
> > properties, there is compatibility level option for sql2000 and
> > sql2005, what is the difference? i noticed the default is sql2000.
> >
> > thank you
> >

1 Aug 2006 11:24 PM

Roy Harvey

Compatibility mode is there to allow applications that are dependent
on 8.0 (2000) behaviors that change in 9.0 (2005) compatability mode.
It has nothing to do with the tools.

Roy Harvey
Beacon Falls, CT

On 1 Aug 2006 15:10:05 -0700, "=== Steve L ==="
<steve.***@powells.com> wrote:

Show quote

>well, then what the compatibility mode 8.0 is for? since one can't
>manage it using SQL2k Enterprise manager. i just wonder, under what
>circumstance would a person use sql2k5 SSMS to manage a compatibility
>8.0 database?!
>
>Andrew J. Kelly wrote:
>> The database internal objects are upgraded to 2005 at the time you restore
>> or attach it on a 2005 server. It will put the compatibility mode to 8.0 on
>> upgraded dbs and 90 on newly created ones. The difference is basically in
>> how it behaves and what commands you can or can not use. In 80 compatibility
>> mode you won't have access to most of the new 2005 features.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "=== Steve L ===" <steve.***@powells.com> wrote in message
>> news:1154038501.740045.14090@h48g2000cwc.googlegroups.com...
>> > if I back up a database in sql2k then restore it on sql2k5, does it
>> > make the database sql2k5 automatically? wihen looking at the database
>> > properties, there is compatibility level option for sql2000 and
>> > sql2005, what is the difference? i noticed the default is sql2000.
>> >
>> > thank you
>> >

2 Aug 2006 12:00 AM

Gail Erickson [MS]

>>well, then what the compatibility mode 8.0 is for? since one can't
>>manage it using SQL2k Enterprise manager. i just wonder, under what
>>circumstance would a person use sql2k5 SSMS to manage a compatibility
>>8.0 database?!

In addition to Roy's information... The Books Online topic "sp_dbcmptlevel
(Transact-SQL) states the following:
"Use sp_dbcmptlevel as an interim migration aid to work around version
differences in the behaviors that are controlled by the relevant
compatibility level setting. If existing SQL Server applications are
affected by behavioral differences in SQL Server 2005, convert the
application to work properly."

The topic also lists the various behavior differences your application may
encounter. For example, if you have stored procedures that use the old-style
*= and =* operators for outer joins, the procedures will be broken in the 90
compatibility level. Setting the database compat level to 80 allows you to
run your application using SQL Server 2005 until you get your stored
procedures fixed.

Regards,
Gail

--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Show quote

"Roy Harvey" <roy_har***@snet.net> wrote in message
news:dhovc216m736k8sarnh2ovrdrqk85jaqou@4ax.com...
> Compatibility mode is there to allow applications that are dependent
> on 8.0 (2000) behaviors that change in 9.0 (2005) compatability mode.
> It has nothing to do with the tools.
>
> Roy Harvey
> Beacon Falls, CT
>
> On 1 Aug 2006 15:10:05 -0700, "=== Steve L ==="
> <steve.***@powells.com> wrote:
>
>>well, then what the compatibility mode 8.0 is for? since one can't
>>manage it using SQL2k Enterprise manager. i just wonder, under what
>>circumstance would a person use sql2k5 SSMS to manage a compatibility
>>8.0 database?!
>>
>>Andrew J. Kelly wrote:
>>> The database internal objects are upgraded to 2005 at the time you
>>> restore
>>> or attach it on a 2005 server. It will put the compatibility mode to 8.0
>>> on
>>> upgraded dbs and 90 on newly created ones. The difference is basically
>>> in
>>> how it behaves and what commands you can or can not use. In 80
>>> compatibility
>>> mode you won't have access to most of the new 2005 features.
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>>
>>> "=== Steve L ===" <steve.***@powells.com> wrote in message
>>> news:1154038501.740045.14090@h48g2000cwc.googlegroups.com...
>>> > if I back up a database in sql2k then restore it on sql2k5, does it
>>> > make the database sql2k5 automatically? wihen looking at the database
>>> > properties, there is compatibility level option for sql2000 and
>>> > sql2005, what is the difference? i noticed the default is sql2000.
>>> >
>>> > thank you
>>> >