Sql-server – Restoring database to UNC path on local drive

backuprestoresql serversql-server-2008-r2

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?

Best Answer

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.

Related Question