Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Move DB off C Drive

Posted on 2003-03-05
5
Medium Priority
?
405 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
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

577 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