?
Solved

Move DB off C Drive

Posted on 2003-03-05
5
Medium Priority
?
404 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:anakamura
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 3

Expert Comment

by:cdillon
ID: 8073693
can you use the entriprise manager?  
0
 

Author Comment

by:anakamura
ID: 8073981
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
 

Accepted Solution

by:
MankeyMankey earned 100 total points
ID: 8076533
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8087766
I agree with MankeyMankey.
0
 

Expert Comment

by:Paul_Wisconsin
ID: 12087082
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

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question