Backing up a SQL 2000 database on SQL 2005 and restoring it on 2000

[sanjdhiman]

Hi

I am having a problem with a 2000 backup of a database made on a sql 2005 server.

The database was created as a 2000 database on 2005 box.

I now want to back it up and restore it on a 2000 box, however I get an error of compatibility.

Is their anything else i can do?? I have set the compatability levels using the system stored procedure but no luck, same problem !!!

Anything else I need to do with the backup to make it 2000 compliant

thanks in advance

sanj

 

[yetanotherjo]

Backward compatibility seems to be just about non-existant with 2005. Have you tried detaching the .mdf and .ldf file and copying them to the new server?

 

[sanjdhiman]

no i havent tried that yet, but that is strange though that you can keep a 2000 database and then when you want to restore it to a 2000 server it doesnt like it. Is that because the backup is done in 2005 and hence not compatable

 

[Jamfool]

if you have restored or attached a database on to a SQL2005 setup then you can not restore/or attach it on a SQL2000 instance even if you have specified compat mode.

You would have to use something like dts to move the objects to the SQL2000 instance.

 

[yetanotherjo]

You gotta wonder...what the HELL were they thinking!?!?!

 

[sanjdhiman]

Why would they offer 2000 compatability and then not enable us to restore the database on a sql 2000 server.

I even tried attach and re-attach but with no luck at all.

Is there a system or a 3rd party vendor which can convert the backup to 2000???

Sanj

 

[ptheriault]

Nope,
Your only option is to use DTS to import the database from the 2005 server.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[sanjdhiman]

so using SSIS I can transfer the database to the other server? is that right using the transfer database task!!!

 

[mutley1]

What exactly is the error message you receive? I have a colleague here who says he has done this.

M.