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
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,039 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
ID: 22766597
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22766614
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
ID: 22766638
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22766809
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
 

Author Comment

by:resham
ID: 22775344
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 143

Expert Comment

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

Author Comment

by:resham
ID: 22775588
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
ID: 22778316
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
ID: 31508226
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

829 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