Copying SQL Server 2000 Database to SQL server 2005 database

[mateobus]

Hey,

I am trying to copy a SQL server 2000 database (from enterprise manager) to a SQL server 2005 database (from SQL server management studio). I have tried various methods, each of which returns a different error. Does anyone have a better way of doing this or a solution to one of these errors.

1.When I generate a .BAK file from the DB by running a backup. When I try to restore it, I get the following message: Cannot open backup device ... Oprating system error 5(Access is denied.).

2.When I run an export directly from Enterprise manager to export to the 2005 DB, I get this error, after the operation appears to be completed: To connect to this server you must use SQL server management studio, or sql server management objects (SMO).

3.Finally, when I try to export the data to an sql file, then import that sql file, I get the following error: Your provider does not support all the schema rowsets required by DTS.

Does anyone have a solution to any of these issues, or a different way to pull the db and its data? Thanks in advance.

 

[Glasgow]

Could it be that, when restoring, you are trying to overwrite the same physical data files as used by the SQL 2000 database? That would probably trigger the access denied message.

You need to store the data in different physical files from the SQL2000 version if you want to keep both versions alive.

 

[mateobus]

Yeah, I am definitely doing that. The two databases are on different machines.

 

[Glasgow]

It might be worth writing a query that uses RESTORE DATABASE with its 'MOVE' option to specify where the new files should be physically located.