4

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?

  • 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 Shah 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
4

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.

  • +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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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