Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
1,049 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

885 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