Cannot create database on drive other than OS drive?

I'm trying to migrate a SQL Server 2000 database to a SQL Server 2005
machine and am having difficulties.
The new server (Server 200303) has two drives, C and E. The OS is installed
on C, but there is limited space, so I want to put the data files on E.
To migrate the database from the SQL 2000 machine, I tried both a manuak
detach/attach and the Copy Database feature. When I attempt to tell the new
server to put the data files on drive E, it tells me that it's an invalid
path. To add to the confusion, if I try to alter the "database default
location" from the server's Properties dialog, it shows only a C drive.
I then uninstalled SQL Server 2005 from the C drive and reinstalled it, this
time putting all components that I could change the target path for on the E
drive. The install was successful, the folders were created properly, along
with the master, model, msdb, etc. databases on the E drive. However, when
I try to change a database's file location or alter the default database
location, it still insists that only the C drive is available and that
anything referring to the E drive is invalid.
I tried migrating the database to another SQL 2000 machine and putting it on
a separate data drive and it works just fine.
Is there some setting that SQL Server 2005 is expecting, or some limitation
that the data files must reside on the OS install drive? I assume that I'm
just not doing something right, but I've really not done any SQL admin since
7.0 and right now I'm just trying to migrate a database to a newer, better
machine.
-Chris
Chris Tacke, eMVP wrote:
> I'm trying to migrate a SQL Server 2000 database to a SQL Server 2005
> machine and am having difficulties.
> The new server (Server 200303) has two drives, C and E. The OS is installed
> on C, but there is limited space, so I want to put the data files on E.
> To migrate the database from the SQL 2000 machine, I tried both a manuak
> detach/attach and the Copy Database feature. When I attempt to tell the new
> server to put the data files on drive E, it tells me that it's an invalid
> path. To add to the confusion, if I try to alter the "database default
> location" from the server's Properties dialog, it shows only a C drive.
> I then uninstalled SQL Server 2005 from the C drive and reinstalled it, this
> time putting all components that I could change the target path for on the E
> drive. The install was successful, the folders were created properly, along
> with the master, model, msdb, etc. databases on the E drive. However, when
> I try to change a database's file location or alter the default database
> location, it still insists that only the C drive is available and that
> anything referring to the E drive is invalid.
> I tried migrating the database to another SQL 2000 machine and putting it on
> a separate data drive and it works just fine.
> Is there some setting that SQL Server 2005 is expecting, or some limitation
> that the data files must reside on the OS install drive? I assume that I'm
> just not doing something right, but I've really not done any SQL admin since
> 7.0 and right now I'm just trying to migrate a database to a newer, better
> machine.
> -Chris
>
>
Hi Chris
I assume that you have ensured that the E drive is actually working and
can be seen by the OS?
The only other thing I can think of, is that the path you have entered
for the new datafiles is wrong or doesn't exists. When you want to move
data and log files, the folder you're moving it to must already exists
otherwise it will fail.
Regards
Steen
|||> I'm trying to migrate a SQL Server 2000 database to a SQL Server 2005
> machine and am having difficulties.
> The new server (Server 200303) has two drives, C and E. The OS is installed
> on C, but there is limited space, so I want to put the data files on E.
> To migrate the database from the SQL 2000 machine, I tried both a manuak
> detach/attach and the Copy Database feature. When I attempt to tell the new
> server to put the data files on drive E, it tells me that it's an invalid
> path. To add to the confusion, if I try to alter the "database default
> location" from the server's Properties dialog, it shows only a C drive.
> I then uninstalled SQL Server 2005 from the C drive and reinstalled it, this
> time putting all components that I could change the target path for on the E
> drive. The install was successful, the folders were created properly, along
> with the master, model, msdb, etc. databases on the E drive. However, when
> I try to change a database's file location or alter the default database
> location, it still insists that only the C drive is available and that
> anything referring to the E drive is invalid.
> I tried migrating the database to another SQL 2000 machine and putting it on
> a separate data drive and it works just fine.
> Is there some setting that SQL Server 2005 is expecting, or some limitation
> that the data files must reside on the OS install drive? I assume that I'm
> just not doing something right, but I've really not done any SQL admin since
> 7.0 and right now I'm just trying to migrate a database to a newer, better
> machine.
It looks like the account on which the SQL Server is running has no rights
to access the E:\ folder (and subfolders), so it does not show it nor
is able to move any files there.
Check which account you are using for the SQL Server and check effective
rights for this account to the folders you want the server to access.
Hilarion