Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2002
    Posts
    4

    Unanswered: SQL Server with NAS

    Hi,
    I'm trying to set up SQL Server to use a database that resides on a different machine. I have been running this script in the Query Analyzer:

    dbcc traceon(1807)
    go
    sp_attach_db 'nas_test', '\\Tf-paris\c_drive\NAS\testdb_data.mdf', '\\Tf-paris\c_drive\NAS\testdb_log.ldf'
    dbcc traceoff(1807)

    I get this error:
    Server: Msg 5105, Level 16, State 2, Line 1
    Device activation error. The physical file name '\\Tf-paris\c_drive\NAS\testdb_data.mdf' may be incorrect.

    Before I run the script I have tried 2 different things:
    1) I have stopped the SQL service manually then copied the .mdf and .ldf files to the new location, then restarted the service OR
    2) I have used "sp_detach_db" from Query Analyzer then copied the files across.

    The UNC paths are correct.

    Please help,
    Thank you.
    Tony.

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    You won't be able to attach a database file from another machine, the file must reside on the same machine as SQL Server. You'll need to copy the files over. If these files are active on the other machine you'll need to either sp_detach_db or stop sql server before copying over.

  3. #3
    Join Date
    Mar 2002
    Posts
    4
    Hi, thanks for your reply,
    I'm a little confused. Are you saying that SQL Server can't use a network-based datafile at all? How does a NAS solution work with SQL Server? I know that Microsoft does not recommend NAS with SQL Server in general but they do say that if the NAS server meets certain hardware requirements (WHQL) and can guarantee certain write-through and write-order I/O transactions then Microsoft does support it. (ref: microsoft article Q304261).

    Just to explain my situation further: I have a win2k server with SQL Server 2000 on it. I am using a database called testdb on this same machine. I need to be able to put/move or recreate (whatever works!)this database file (.mdf and .ldf) onto a different machine which would be the NAS server with Win2k but NOT SQL Server running on it.

    Thanks,
    It's great to be able to talk to someone about this because the documentation on this situation so far seems contradictory.
    Tony.

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Well this is a surprise.

    I just tried this at my site and it worked. I'm running SQL Server 7 on Windows NT 4.0.

    From my desktop I connected to our test machine and detached a database EXEC sp_detach_db @dbname = N'east'. Then from my workstation connected to my SQL Server on my workstation I ran:
    EXEC sp_attach_db @dbname = N'east',
    @filename1 = N'\\NIKE\f$\db_data\east_data.mdf',
    @filename2 = N'\\NIKE\g$\db_log\east_log.ldf'


    No problem.
    I notice that the error you received was:
    Server: Msg 5105, Level 16, State 2, Line 1
    Device activation error. The physical file name '\\Tf-paris\c_drive\NAS\testdb_data.mdf' may be incorrect


    From the command prompt on the SQL Server machine, logged in as the person SQL Server is running as can you issue:

    c:\ dir \\Tf-paris\c_drive\NAS\testdb_data.mdf
    and does it work?

  5. #5
    Join Date
    Mar 2002
    Posts
    4
    Excellent!

    Yes I could use the command prompt to verify the file location.

    What seemes to have fixed it so far was that I used your T-SQL code with the EXEC function rather than the sp_attach_db function I used earlier.

    However this now gives me a new error:
    "Server: Msg 5110, Level 16, State 2, Line 1
    File '\\Tf-paris\f_drive\NAS\testdb_data.mdf' is on a network device not supported for database files."

    This message I have expected at some point and documentation states this error will occur unless I switch the trace flag 1807 on. Which basically is the flag to say its OK to use UNC names.

    I can switch the flag on:
    dbcc traceon(1807)
    and check it's status:
    dbcc tracestatus(1807)
    and switch it back off:
    dbcc traceoff(1807)

    but when I run the "EXEC sp_attach_db.." with the trace flag ON I get our old error:
    "Server: Msg 5105, Level 16, State 2, Line 1
    Device activation error. The physical file name '\\Tf-paris\c_drive\NAS\testdb_data.mdf' may be incorrect ."

    When the trace flag is OFF I get the earlier error mentioned above about not supporting database files.

    Hmmm. So close. Did you have to use the trace flag at all? If so, can you see what I'm doing wrong?

    Thanks so much,
    Tony.

  6. #6
    Join Date
    Mar 2002
    Posts
    4
    Important mistake made in last message:

    I wrote:

    but when I run the "EXEC sp_attach_db.." with the trace flag ON I get our old error:
    "Server: Msg 5105, Level 16, State 2, Line 1
    Device activation error. The physical file name '\\Tf-paris\c_drive\NAS\testdb_data.mdf' may be incorrect ."

    It should read:
    "... '\\Tf-paris\f_drive\NAS\testdb_data.mdf' may be incorrect ."

    f_drive NOT c_drive.
    I copy and pasted from earlier notes sorry.

    Tony.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •