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,042 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
[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
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

623 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