Move DB off C Drive

Hello,

I am fairly new to SQL Server and am using SQL Server 2000.  I have SQL Server installed on my PC, but it's not installed on the network.  One of my databases has grown very large and I need to move it off my C drive.

This is what I've tried using the Northwind db for practice...
I tried going through EM and received the message that the network device I wanted to restore my db to was not supported for database files.  So, I tried this approach: I created a new device using the sp_adddumpdevice proc
  code: sp_addumpdevice 'disk' 'Northwind_device' 'h:\nakamura\Northwind.dat'

Then I backed up the database to the new device.
Next, I dropped the database
Then I tried to restore to the h: drive using this code:

RESTORE DATABASE Northwind
from disk='h:\nakamura\Northwind.dat'
WITH MOVE 'Northwind' TO 'h:\nakamura\Northwind.mdf',
MOVE 'Northwind_log' TO 'h:\nakamura\Northwind.ldf'

I get the same error - that the network device is not created for database files.  

Any help would be sincerely appreciated.  I'm the only one in the office who is using this software and I'm wingin' it.
anakamuraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cdillonCommented:
can you use the entriprise manager?  
0
anakamuraAuthor Commented:
I originally tried the EM and received the same message indicating that I was trying to move the database to a network device not created for database files.  That is why I went through the Query Analyzer and added the WITH MOVE language.
0
MankeyMankeyCommented:
Database devices cannot be located on a network drive by design in sql server.  They have to be local to the machine sql server is install on.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree with MankeyMankey.
0
Paul_WisconsinCommented:
SQL Server is not configured by default to support the creation and usage of a data store on a network file share, either those located on a standard server or a Network-Attached Storage.
 
To enable support for network file shares, trace flag 1807 must be enabled. This trace flag bypasses the check to see if the location for the use and creation of the database file is on a network share. Use Query Analyzer, select the master database, and execute the following command:

DBCC TRACEON(1807)
The successful result of this command should be as follows:

DBCC Execution Completed. If DBCC Printed Error Messages, Contact Your System Administrator.
It is now possible to use a mapped drive or a Universal Naming Convention (UNC) path (that is, \\servername\sharename) with SQL Server 2000. If trace flag 1807 is not enabled prior to using network file share with SQL Server, you will encounter one of the following errors:

5105 (Device Activation Error)
5110 (File 'file_name' Is On A Network Device Not Supported For Database Files).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.