data and log files won't copy

I copied a database using backup and restore from Sql server 2000 to Sql server 2005. I noticed the data(mdf) and log(ldf) files did not copy. when I try to do a copy and past it gives me an error: cannot copy lof: it is being used by another person or program. Close all programs and try again. Does this mean I have to stop both servers to copy between them? Please help.

Hi,

you have to detach your database form your sql server 2000, and then attach them to the sql server 2005.

...

Detaching a database requires exclusive access to the database

USE master;
ALTER DATABASE dbname
SET SINGLE_USER;
GO

exec sp_detach_db @.dbname='dbname'

...

plz read BOL for more information: Detaching and Attaching Databases

|||

If you are restoring from an actual backup file (which usually has a .bak extension), you would not need to copy the data and log files. The restore process creates new ones from the backup file.

There are two easy ways to go from 2000 on one DB Server to 2005 on another DB Server. One is to backup the SQL Server 2000 database to a .bak file, then copy the .bak file to the new server and use SQL Server 2005 to do the restore (on the new server). This converts the database to 2005 format.

The second way is to detach the database in SQL Server 2000 and then copy the data and log files to the new server and then use SQL Server 2005 to attach them. This also converts the database to 2005 format. Either way, once you have restored or attached a SQL Server 2000 database in SQL Server 2005, the database cannot go back to SQL Server 2000 format!

Finally, whichever method you use, you should change the compatibility level to 90 and run sp_UpdateStats on the upgraded database.

|||Thank you for all your help. This worked out fine.|||

Hi,

First step :Take database offline or stop the sql server 2000 service.

After that you copy the .mdf and .ldf files.

withRegards

S.kuraliniyan

SystemEngineer