can't see drive in sql server

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tim Browne - 30 Jul 2005 17:48 GMT

Hello All -
I have two physical drives attached to my local machine (C: (40GB) & E:
(250GB) )

I have a large .mdf & .ldf that I detached from C: and (using windows
explorer) moved to my E: drive.

Now I want to re-attach these files, but SQL SERVER only see's the C: drive.

I was able to DTS (build) this file from my E: drive (meanning it saw the
data to make the .mdf) yet won't make a .mdf on that drive.

Any ideas whu this is happening? if it is permissions then how do I set
them properly?

Reply to this Message

David Portas - 31 Jul 2005 14:39 GMT

Putting database files on a network drive isn't supported. The MDF and LDF
files should go on permanent, physical drives or SAN devices only.

Signature

David Portas
SQL Server MVP
--

Reply to this Message

Tim Browne - 31 Jul 2005 16:56 GMT

my E: drive is a permanent physical drive connected to my local PC, when I
want to reattach the database, it doesn't see my E: drive

> Putting database files on a network drive isn't supported. The MDF and LDF
> files should go on permanent, physical drives or SAN devices only.

Reply to this Message

David Portas - 31 Jul 2005 21:51 GMT

As Hari mentioned, you should check that the service account has access to
that drive.

How are you attempting to do the attach? The most reliable way may be to use
sp_attach_db from Query Analyzer and make sure you specify the full path for
each file. For example:

EXEC sp_attach_db 'your_db_name',
'e:\mssql_data\your_db_data.mdf',
'e:\mssql_data\your_db_log.ldf'

Signature

David Portas
SQL Server MVP
--

Reply to this Message

Tim Browne - 31 Jul 2005 22:03 GMT

HERE IS THE ERROR i AM GETTING:

Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name
'E:\SQL_MCKESSON_DATA\MCKESSON_DATA_Data.MDF' may be incorrect.

> As Hari mentioned, you should check that the service account has access to
> that drive.
[quoted text clipped - 6 lines]
> 'e:\mssql_data\your_db_data.mdf',
> 'e:\mssql_data\your_db_log.ldf'

Reply to this Message

Hari Prasad - 31 Jul 2005 15:52 GMT

Hi,

Verify the SQL SERVER STARTUP account. Go to Control panel -- admin tools --
services -- select and double click MSSQL Server service
and choose logon tab.

The account in logon tab should have read/write prev to the E Drive.

After that try using SP_ATTACH_DB from Query Analyzer.

( Assuming E drive is a local hard drive)

Thanks
Hari
SQL Server MVP

> Hello All -
> I have two physical drives attached to my local machine (C: (40GB) & E:
[quoted text clipped - 11 lines]
> Any ideas whu this is happening? if it is permissions then how do I set
> them properly?

Reply to this Message

Hugo Kornelis - 31 Jul 2005 22:04 GMT

>Hello All -
>I have two physical drives attached to my local machine (C: (40GB) & E:
[quoted text clipped - 10 lines]
>Any ideas whu this is happening? if it is permissions then how do I set
>them properly?

Hi Tim,

Is the SQL Server service running on your local machine, or are you
connecting to a SQL Server on another server on the network?

What is the response when you execute the following commands:
1. master..xp_cmdshell 'dir c:'
2. master..xp_cmdshell 'dir e:'

Best, Hugo

Signature

(Remove _NO_ and _SPAM_ to get my e-mail address)

Reply to this Message

Tim Browne - 31 Jul 2005 22:10 GMT

xp_cmdshell 'dir e:'
Access is denied.
NULL

> >Hello All -
> >I have two physical drives attached to my local machine (C: (40GB) & E:
[quoted text clipped - 21 lines]
>
> Best, Hugo

Reply to this Message

Hugo Kornelis - 31 Jul 2005 22:48 GMT

>xp_cmdshell 'dir e:'
>Access is denied.
>NULL

Hi Tim,

Try the following: change the security settings for the E:-drive to
allow everyone full access to the drive, all folders and all files. Then
try re-attaching the database files again. If it works now (and I assume
it will), then either leave the security settings as they are, or change
them - but make sure that the account used to run the SQL Server service
has full access to all folders and files used for the databases.

Best, Hugo

Signature

(Remove _NO_ and _SPAM_ to get my e-mail address)

Reply to this Message

Tim Browne - 31 Jul 2005 23:29 GMT

WORKED!

Thank you sir!

> >xp_cmdshell 'dir e:'
> >Access is denied.
[quoted text clipped - 10 lines]
>
> Best, Hugo

Reply to this Message