April 18, 2011 at 3:32 am
Hello,
I am trying to Restore a database which is located in a Network Drive through the following command
dbcc traceon (1807,-1)
RESTORE DATABASE [DBNAME] FILE = N'DBNAME' FROM DISK = N'\\yyypc\Test\xxx.BAK' WITH FILE = 1, MOVE N'DBNAME' TO N'\\yyypc\Test\xxx.mdf', MOVE N'DBNAME_log' TO N'\\yyypc\Test\xxx.ldf', NOUNLOAD, REPLACE, STATS = 10
GO
I have logged in with user as "sa". While executing the above command I get the following error
The operating system returned the error '5(Access is denied.)' while attempting 'CreateFileW' on '\\yyypc\Test\xxx.mdf'.
Cheers
Madhu
April 18, 2011 at 3:51 am
Find out if your windows account and SQL Service have access to the network location and if this windows 2008 server then you have to be the owner of the folder with all the access. You have to restore using windows account which have sys admin privileges as well access to the network folder.
"More Green More Oxygen !! Plant a tree today"
April 18, 2011 at 4:00 am
Data files need to be on a local drive, not a network drive. The network file protocol does not support the IO requirements that SQL has. In addition, network glitches would result in databases going offline or suspect and the IO performance would be terrible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2011 at 4:19 am
Thank You for the reply. Could you please tell me how do I check if my SQL Service have access to the network location. In the SQL Server Configuration Manager, the status for Log On as is set to "Network Service" for the SQL Service. My OS is Windows 7 Enterprise edition. I am working under SQL Server 2008 R2 Express edition.
I am able to read and write files when I log in from Start -> run Command using Windows Account login details.
Madhu
April 18, 2011 at 4:37 am
First let me caution you for having data & log file on network drive. As said by Gail Shaw
this will impact perf as well as data corruption and IO issues. Save it in LOcal disk.
How you will restore the Database? Add your NT account to SQL Server with sys admin rights and add your account to the network folder with full rights and bethe owner of the folder and then try to login to sql server with your NT id and restore the DB.
"More Green More Oxygen !! Plant a tree today"
April 18, 2011 at 4:46 am
Minaz Amin (4/18/2011)
First let me caution you for having data & log file on network drive. As said by Gail Shaw
It's not just a caution. SQL 2008 will not allow a data file on a network drive without a traceflag (disabling certain IO checks)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply