November 17, 2003 at 5:23 pm
I am trying to upgrade a database from SQL 7 to SQL2K. I detached the database successfully using sp_detach_db. However, when I tried to attach the database using the following command, I got the error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name 'e:\database\test\test.mdf' may be incorrect.
I verified that the files are there. Whatelse could cause this error? Did I do something wrong? Thank you for your help.
-------------
EXEC sp_attach_db @dbname = N'test',
@filename1 = N'e:\database\test\test.mdf',
@filename2 = N'e:\database\test\test_1.mdf',
@filename3 = N'e:\database\test\test_2.mdf',
@filename4 = N'f:\tranlogs\test\test_log.ldf',
@filename5 = N'f:\tranlogs\test\test_log_1.ldf'
November 17, 2003 at 9:16 pm
BOL says
If you attach a database to a server other than the server from which the database was detached, and the detached database was enabled for replication, you should run sp_removedbreplication to remove replication from the database.
Is your db part of replication ?
November 17, 2003 at 9:29 pm
I did the same,& found it working well.
also make sure that SQL service Account has access to the paths / files specified.
sp_attach_db N'test' , N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\test_Data.MDF', N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\test_Log.LDF'
November 18, 2003 at 6:22 am
Totally sure you have the path correct?
Andy
November 18, 2003 at 12:11 pm
Thank you, guys, for all your help.
Andy is right. There are two databases on the server with similar names. I was attaching the wrong files...
I thought detach and attach would save time comparing backup/restore. Actually, the file copy of the 100GB data file took longer than the restore time...In that case, is it safer to use the backup/restore method? Thanks again.
November 19, 2003 at 6:30 am
Nothing wrong with either method. Faster still would be to compress either file using zip or similar. Faster than that would be a SQLLiteSpeed backup since they compress in memory (course you have to pay for it).
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply