Solved

Sql Server 2005: How to create a Database with the mdf and ldf being on the networked drive?

Posted on 2008-10-21
9
1,032 Views
Last Modified: 2011-10-03
Hi, I do not have much experience in creating databases and maintaining them. I want to create a Database and keep its data and log file on networked location. My command looks like:

RESTORE DATABASE MyDB FROM DISK='\\Machine1\MyDB.bak'
WITH
MOVE 'MyDB_Data' TO '\\Machine1\Data\MyDB_Data.MDF',
MOVE 'MyDB_Log' TO '\\Machine1\Log\MyDB_Log.LDF'

now when I do this, i get error:
The file "\\\Machine1\Data\MyDB_Data.MDF'" is on a network path that is not supported for database files.

I tried to look this up and found one article http://support.microsoft.com/kb/304261
and tried to OFF the trace flag 1807 [Actually it was never ON in my system] but still gets the error.

My Ques are:
1. Can we create DB like the way I am trying?
2. Or can we make an extra harddisk attached to machine as a NAS device?

Your help is greatly apprciated.

Thanks
Resham
0
Comment
Question by:resham
9 Comments
 
LVL 9

Expert Comment

by:Sander Stad
Comment Utility
You could also create a mapped networkdrive that points to a network location or NAS.
But remember that this can create quite a lot of networktraffic when your database get larger.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
the mapped drive "trick" will not work.

>1. Can we create DB like the way I am trying?
short answer: yes, with some trace flags activated
long answer: do NOT do it for production servers

>2. Or can we make an extra harddisk attached to machine as a NAS device?
NAS/SAN drives are different, and should not be considered "network" drives in the same matter as true networked mapped drives.

0
 

Author Comment

by:resham
Comment Utility
Hi angellll,

it is not a Producttion server. It is for development purpose but database is going to be huge. I think we are okay with the tadeoff of network traffic as it wont be hit as much.

Can you please explain which TRACE flags to activate for this to make possible.

Thanks angellll and sstad for prompt reply.

Resham
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
http://www.sqldba.org/articles/43-sql-server-database-on-UNC-network-drive-trace-flag-1807.aspx
DBCC TraceOn(1807)

GO

CREATE DATABASE DATABASE_NAME

ON

(NAME = DATABASE_DAT,

FILENAME = '\\ServerName\ShareName\database_dat.mdf' )

GO

DBCC TraceOff(1807)

Open in new window

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:resham
Comment Utility
Thanks Angellll,

This has been a helpful but problem is not completely solved.

DBCC TraceOn(1807)
RESTORE DATABASE Cargill_Incidents FILEGROUP='PRIMARY' FROM DISK = '\\ashutosht\Cargill CD set of 2\Altiris_Incidents_backup_200804031703.bak'
WITH
MOVE N'Altiris_Incidents' TO N'\\ashutosht\Data\Altiris_Incidents.mdf',
MOVE N'Altiris_Incidents_log' TO N'\\ashutosht\Log\Altiris_Incidents_log.LDF',
MOVE N'sysft_HD_workitem_comment' TO N'D:\Temp',
REPLACE
DBCC TraceOff(1807)

it gave me error:

Msg 5120, Level 16, State 101, Line 6
Unable to open the physical file "\\ashutosht\Data\Altiris_Incidents.mdf". Operating system error 5: "5(Access is denied.)".
Msg 3013, Level 16, State 1, Line 6
RESTORE DATABASE is terminating abnormally.

Then I went to that machine, MDF was created there. I gave full permission to 'Everyone' on that file and executed command again.

It gives me same error and when i check the permissions on the file, permissions for the 'Everyone' are lost. SQL server is running unders windows authentication and that same user has 'Administator' rights on the other machine as well.

Can you please tell me what am i doing wrong here??


Thanks
REsham
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
did you grant full permission to BOTH the folder and share permissions?
0
 

Author Comment

by:resham
Comment Utility
Yes.

Folder already has Full permission to Everyone and I did give it to file manually but everytime I execute the command, permissions for Everyone gets lost on it.

I dont know if this is the factor but I m accessing both machine using RDC.

Thanks,
Resham
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
If you are using RDC, then you are not connecting directly via the network as such. It is a different comms link and you are effectively "on" the machine using RDC rather than pointing to it - so - paths and suck like might not be anything "real".  You will need to ping the servername from your machine to make sure it is first visible as the UDC name. Then you will need a trusted connection from your machine to that server. Depending on if you need to "connect" to the server to use RDC, that  somtimes causes network elements to be locked out (e.g. can you send and receive mail, or browse the internet ?). But then, I only just saw the closing bits of this thread...
0
 

Author Closing Comment

by:resham
Comment Utility
Thanks Angellll for all your help.

Though it did not solve all my problems it did help me a lot in going forward. My requirements were changed afterwards so I couldnt come back to this questiosn earlier.

Thanks everybody for all your efforts.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now