attach and detach db woes

[David L]

I have developed a db on a local machine (98) and would like to move it to another machine running nt 4 where it will function as a production db. I am mapped to see the other server (called "labnt&#34. The drive on "labnt" is partitioned, with the c drive holding programs, and the e drive designed to hold the data.

I would like to move the db (called SNB01) to the MSSQLData subdirectory on the e-drive. I have tried the following sproc to detach:

EXEC sp_detach_db 'SNB01', 'true'

And the following to reattach:
EXEC sp_attach_db @dbname = 'SNB01',
@filename1 = '\labnt
ewmssqldataSNB01_Data.mdf',
@filename2 = '\labnt
ewmssqldataSNB01_log.ldf'

The above path was given me by our network admin guy.


QA gives the following error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name '\labnt
ewmssqldataSNB01_Data.mdf' may be incorrect.


Please help. I am thrashing about. D. Lewis

[Anu]

Hi,

Try using,
e$

EXEC sp_attach_db @dbname = 'SNB01',
@filename1 = '\labnte$
ewmssqldataSNB01_Data.mdf',
@filename2 = '\labnte$
ewmssqldataSNB01_log.ldf'

-Anu


------------
David L at 1/4/2002 10:57:05 AM


I have developed a db on a local machine (98) and would like to move it to another machine running nt 4 where it will function as a production db. I am mapped to see the other server (called "labnt&#34. The drive on "labnt" is partitioned, with the c drive holding programs, and the e drive designed to hold the data.

I would like to move the db (called SNB01) to the MSSQLData subdirectory on the e-drive. I have tried the following sproc to detach:

EXEC sp_detach_db 'SNB01', 'true'

And the following to reattach:
EXEC sp_attach_db @dbname = 'SNB01',
@filename1 = '\labnt
ewmssqldataSNB01_Data.mdf',
@filename2 = '\labnt
ewmssqldataSNB01_log.ldf'

The above path was given me by our network admin guy.


QA gives the following error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name '\labnt
ewmssqldataSNB01_Data.mdf' may be incorrect.


Please help. I am thrashing about. D. Lewis

[David L]

------------
David L at 1/4/2002 10:57:05 AM


I have developed a db on a local machine (98) and would like to move it to another machine running nt 4 where it will function as a production db. I am mapped to see the other server (called "labnt&#34. The drive on "labnt" is partitioned, with the c drive holding programs, and the e drive designed to hold the data.

I would like to move the db (called SNB01) to the MSSQLData subdirectory on the e-drive. I have tried the following sproc to detach:

EXEC sp_detach_db 'SNB01', 'true'

And the following to reattach:
EXEC sp_attach_db @dbname = 'SNB01',
@filename1 = '\labnt
ewmssqldataSNB01_Data.mdf',
@filename2 = '\labnt
ewmssqldataSNB01_log.ldf'

The above path was given me by our network admin guy.


QA gives the following error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name '\labnt
ewmssqldataSNB01_Data.mdf' may be incorrect.


Please help. I am thrashing about. D. Lewis

[David L]

No luck with e$. Same error message in the query analyzer.


------------
David L at 1/4/2002 10:57:05 AM


I have developed a db on a local machine (98) and would like to move it to another machine running nt 4 where it will function as a production db. I am mapped to see the other server (called "labnt&#34. The drive on "labnt" is partitioned, with the c drive holding programs, and the e drive designed to hold the data.

I would like to move the db (called SNB01) to the MSSQLData subdirectory on the e-drive. I have tried the following sproc to detach:

EXEC sp_detach_db 'SNB01', 'true'

And the following to reattach:
EXEC sp_attach_db @dbname = 'SNB01',
@filename1 = '\labnt
ewmssqldataSNB01_Data.mdf',
@filename2 = '\labnt
ewmssqldataSNB01_log.ldf'

The above path was given me by our network admin guy.


QA gives the following error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name '\labnt
ewmssqldataSNB01_Data.mdf' may be incorrect.


Please help. I am thrashing about. D. Lewis

[rogerjh]

I'm pretty sure you need to copy the database files over to the new server (labnt), then run your statements from the local machine:

EXEC sp_attach_db @dbname = 'SNB01',
@filename1 = 'e:mssqldataSNB01_Data.mdf',
@filename2 = 'e:
ewmssqldataSNB01_log.ldf'

I know this will work, as I have to do this all the time.





------------
David L at 1/7/2002 4:32:16 PM

No luck with e$. Same error message in the query analyzer.


------------
David L at 1/4/2002 10:57:05 AM


I have developed a db on a local machine (98) and would like to move it to another machine running nt 4 where it will function as a production db. I am mapped to see the other server (called "labnt&#34. The drive on "labnt" is partitioned, with the c drive holding programs, and the e drive designed to hold the data.

I would like to move the db (called SNB01) to the MSSQLData subdirectory on the e-drive. I have tried the following sproc to detach:

EXEC sp_detach_db 'SNB01', 'true'

And the following to reattach:
EXEC sp_attach_db @dbname = 'SNB01',
@filename1 = '\labnt
ewmssqldataSNB01_Data.mdf',
@filename2 = '\labnt
ewmssqldataSNB01_log.ldf'

The above path was given me by our network admin guy.


QA gives the following error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name '\labnt
ewmssqldataSNB01_Data.mdf' may be incorrect.


Please help. I am thrashing about. D. Lewis

[Anu]

Please check whether the file resides on that share drive.
It should work.

Otherwise check with the Network guy whether the Disk is in SAN
Reference: Q304261 on Microsoft Knowledge base



------------
David L at 1/7/2002 4:32:16 PM

No luck with e$. Same error message in the query analyzer.


------------
David L at 1/4/2002 10:57:05 AM


I have developed a db on a local machine (98) and would like to move it to another machine running nt 4 where it will function as a production db. I am mapped to see the other server (called "labnt&#34. The drive on "labnt" is partitioned, with the c drive holding programs, and the e drive designed to hold the data.

I would like to move the db (called SNB01) to the MSSQLData subdirectory on the e-drive. I have tried the following sproc to detach:

EXEC sp_detach_db 'SNB01', 'true'

And the following to reattach:
EXEC sp_attach_db @dbname = 'SNB01',
@filename1 = '\labnt
ewmssqldataSNB01_Data.mdf',
@filename2 = '\labnt
ewmssqldataSNB01_log.ldf'

The above path was given me by our network admin guy.


QA gives the following error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name '\labnt
ewmssqldataSNB01_Data.mdf' may be incorrect.


Please help. I am thrashing about. D. Lewis

[Juergen Leis]

You must use local path names not UNC paths in sp_attach_db.

e.g.: sp_attach_db 'SNB01', 'D:...SNB01_Data.mdf', 'D:...SNB01_Log.ldf'

anyhow, you should place Data and log on different drives
for recoverability and performance reasons.

------------
David L at 1/4/2002 10:57:05 AM


I have developed a db on a local machine (98) and would like to move it to another machine running nt 4 where it will function as a production db. I am mapped to see the other server (called "labnt&#34. The drive on "labnt" is partitioned, with the c drive holding programs, and the e drive designed to hold the data.

I would like to move the db (called SNB01) to the MSSQLData subdirectory on the e-drive. I have tried the following sproc to detach:

EXEC sp_detach_db 'SNB01', 'true'

And the following to reattach:
EXEC sp_attach_db @dbname = 'SNB01',
@filename1 = '\labnt
ewmssqldataSNB01_Data.mdf',
@filename2 = '\labnt
ewmssqldataSNB01_log.ldf'

The above path was given me by our network admin guy.


QA gives the following error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name '\labnt
ewmssqldataSNB01_Data.mdf' may be incorrect.


Please help. I am thrashing about. D. Lewis

[David L]

Awesome. Simple problem and an obvious solution, but nowhere mentioned in any help text. Thanks to all who read and respond to these posts!!! D. Lewis

------------
rogerjh at 1/7/2002 5:04:08 PM


I'm pretty sure you need to copy the database files over to the new server (labnt), then run your statements from the local machine:

EXEC sp_attach_db @dbname = 'SNB01',
@filename1 = 'e:mssqldataSNB01_Data.mdf',
@filename2 = 'e:
ewmssqldataSNB01_log.ldf'

I know this will work, as I have to do this all the time.





------------
David L at 1/7/2002 4:32:16 PM

No luck with e$. Same error message in the query analyzer.


------------
David L at 1/4/2002 10:57:05 AM


I have developed a db on a local machine (98) and would like to move it to another machine running nt 4 where it will function as a production db. I am mapped to see the other server (called "labnt&#34. The drive on "labnt" is partitioned, with the c drive holding programs, and the e drive designed to hold the data.

I would like to move the db (called SNB01) to the MSSQLData subdirectory on the e-drive. I have tried the following sproc to detach:

EXEC sp_detach_db 'SNB01', 'true'

And the following to reattach:
EXEC sp_attach_db @dbname = 'SNB01',
@filename1 = '\labnt
ewmssqldataSNB01_Data.mdf',
@filename2 = '\labnt
ewmssqldataSNB01_log.ldf'

The above path was given me by our network admin guy.


QA gives the following error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name '\labnt
ewmssqldataSNB01_Data.mdf' may be incorrect.


Please help. I am thrashing about. D. Lewis