We are moving a 2000 database to 2005. I know we can just do a restore from
a backup but I want the 2005 database to be a version 9 DB. As I understand
it, the restore will just make it a 2000 version, is that correct? If so,
how can I make it so that it is a 2005 native DB? Thanks.

David

David,

The restore will convert the database to SQL Server 2005 format. Therefore,
that database cannot be detached or backup up and restored back to a SQL
Server 2000 server.

The 8.0 mode simply causes the language engine to continue to use 8.0 (2000)
syntax, but everything lying beneath that is SQL Server 2005. To switch to
2005 syntax, change to 9.0 mode.

RLF

"David C" <dlchase@lifetimeinc.com> wrote in message
news:O$Z9cEL0IHA.2208@TK2MSFTNGP04.phx.gbl...
> We are moving a 2000 database to 2005. I know we can just do a restore
> from a backup but I want the 2005 database to be a version 9 DB. As I
> understand it, the restore will just make it a 2000 version, is that
> correct? If so, how can I make it so that it is a 2005 native DB?
> Thanks.
>
> David
>

"David C" <dlchase@lifetimeinc.com> wrote in message
news:O$Z9cEL0IHA.2208@TK2MSFTNGP04.phx.gbl...
> We are moving a 2000 database to 2005. I know we can just do a restore
> from a backup but I want the 2005 database to be a version 9 DB. As I
> understand it, the restore will just make it a 2000 version, is that
> correct? If so, how can I make it so that it is a 2005 native DB?
> Thanks.
>
> David
Hi

If you have run the migration assistant you should have picked up any
incompatability issues before migration. Check out the upgrade handbook and
reference from http://www.microsoft.com/sql/solutio...e/default.mspx
which just about covers everything I could think of.

John

"David C" <dlchase@lifetimeinc.com> wrote in message
news:O$Z9cEL0IHA.2208@TK2MSFTNGP04.phx.gbl...
> We are moving a 2000 database to 2005. I know we can just do a restore
> from a backup but I want the 2005 database to be a version 9 DB. As I
> understand it, the restore will just make it a 2000 version, is that
> correct? If so, how can I make it so that it is a 2005 native DB?
> Thanks.
>
> David
Hi

Have you run the upgrade advisor before migrating to check for any problems?

use sp_dbcmptlevel to change compatibility level.

John

Thanks for the advice. I will download the Upgrade Advisor and run it.

David
"John Bell" <jbellnewsposts@hotmail.com> wrote in message
news:4398A05A-D954-461B-9AC3-E885CECF1AC5@microsoft.com...
>
> "David C" <dlchase@lifetimeinc.com> wrote in message
> news:O$Z9cEL0IHA.2208@TK2MSFTNGP04.phx.gbl...
>> We are moving a 2000 database to 2005. I know we can just do a restore
>> from a backup but I want the 2005 database to be a version 9 DB. As I
>> understand it, the restore will just make it a 2000 version, is that
>> correct? If so, how can I make it so that it is a 2005 native DB?
>> Thanks.
>>
>> David
> Hi
>
> Have you run the upgrade advisor before migrating to check for any
> problems?
>
> use sp_dbcmptlevel to change compatibility level.
>
> John

I have been doing tons of migration from SQL 2000 to 2005 and now
testing the same over to 2008.
As Suggested, run the Upgrade Advisor..
but here are the things you can do once you are ready to restore.
restore using your normal restore command.
Change compatibility level to 90
Check all user mappings using sp_change_users_login 'report'
Check your SP Code for inconsistencies such as deprecated syntax.
Check for <TableName>.* syntax, this one specifically doesnt work in
sql server 2005.
Run a Profiler on your DB and check for Deprecated Code.
More Pointers: SQL 2005 uses DMV insted of system Tables and Views.
Check that.


Manny Singh