Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I try to restore a database using a restore command with a local UNC path:

RESTORE DATABASE [dbname] 
FROM DISK = N'\\PC91\D\backup.BAK' WITH  FILE = 1,  
MOVE N'test' TO N'\\PC91\D\dbname.MDF',  
MOVE N'test_log' TO N'\\PC91\D\dbname_log.LDF',  
NOUNLOAD, STATS = 10

I get an error:

Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '5(Access is denied.)' while attempting 'CreateFileW' on '\PC91\D\dbname.MDF'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

If I use a local drive letter instead, then it works:

RESTORE DATABASE [dbname] 
FROM DISK = N'D:\backup.BAK' WITH FILE = 1,  
MOVE N'test' TO N'D:\dbname.MDF',  
MOVE N'test_log' TO N'D:\dbname_log.LDF',  
NOUNLOAD, STATS = 10

This command also restores the database to same folder. So why is there an error when I specify the network path?

share|improve this question
    
Did you actually create a share named D that points to the root of D:? Or did you mean to use the administrative share name for the drive (D$)? Why do you want to use a UNC path for a local database? Why in the root (which can have certain restrictions and require elevation of privileges) instead of data/log folders? –  Aaron Bertrand Aug 13 '13 at 12:53
    
And what account are you running as? I have a desktop database running as NT Service\Server$Instance, but that account does not have access to \\ServerName. –  RLF Aug 13 '13 at 13:00
    
@ITResearcher Best is to copy the backup locally on the server and restore it. –  Kin Aug 13 '13 at 18:56
1  
IT researcher - it looks like you're storing the backup and the database itself on the same drive - bad idea! If the drive crashes you have no backup! –  Max Vernon Oct 12 '13 at 19:34

1 Answer 1

up vote 4 down vote accepted

Sounds like "access is denied" - make sure that the SQL Server service account can access the share with both read and write (whether you can access the share is irrelevant). If you did not create a share named D that points to the root of D:, then your syntax is wrong - you need to use D$. However I recommend creating a share that is NOT in the root of a drive, and making sure that the SQL Server service account (again, not you unless you manually set the SQL Server service account to run as your Windows account) has read and write access. Then it would be:

MOVE N'test'     TO N'\\PC91\ShareName\Data\test.mdf',
MOVE N'test_log' TO N'\\PC91\ShareName\Log\test.ldf'

However I have to wonder why you would want to use a UNC path for this in the first place. It means your database files are more at risk because the share can be removed or become temporarily unavailable while SQL Server is running.

share|improve this answer
    
+1 for wondering why. Only thing I can think of might be duplicating some other environment. If that is the case, the other environment should be reconfigured to avoid using Windows network shares since as you pointed out they are susceptible to a myriad of problems. –  Max Vernon Oct 12 '13 at 19:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.