How to attach a sql db with blobs using a shared folder?

0

I use the following query for attaching a sql db:

CREATE DATABASE [myDb] ON 
 (FILENAME=N'C:\myDb\myDb.mdf'),
 (FILENAME=N'C:\myDb\myDb_log.ldf'),
 (FILENAME=N'C:\myDb\FILESTORE')
FOR ATTACH

In general it works well. I have a bit complicated case. I have to use a shared folder for attaching my db. I create a shared folder using the following command:

net share myShare=C:\myDb

Attaching a db from shared folder works well until the db doesn't have blobs. If db has blobs sql returns the following error:

The path '\\localhost\myShare\FILESTORE' cannot be used for FILESTREAM files. For information about supported paths, see SQL Server Books Online.
Msg 5135, Level 16, State 2, Line 2
The path '\\localhost\myShare\FILESTORE' cannot be used for FILESTREAM files. For information about supported paths, see SQL Server Books Online.
Msg 5120, Level 16, State 101, Line 2
Unable to open the physical file "\\localhost\myShare\FILESTORE". Operating system error 5: "5(Access is denied.)".
Msg 1802, Level 16, State 7, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

The sort of workaround for this problem is creating a symbolic link for blob folder.

mklink /D "C:\slink_myDb" "\\localhost\myShare"

and then

CREATE DATABASE [myDb] ON 
 (FILENAME=N'\\localhost\myShare\myDb.mdf.mdf'),
 (FILENAME=N'\\localhost\myShare\myDb_log.ldf'),
 (FILENAME=N'C:\slink_myDb\FILESTORE')
FOR ATTACH

It works with almost all sql servers but it looks like since sql server 2016 it was disabled. sql server 2016 returns this:

Msg 5120, Level 16, State 106, Line 9
Unable to open the physical file "C:\slink_myDb\FILESTORE". Operating system error -1073741766: "0xc000003a(failed to retrieve text for this error. Reason: 317)".
Msg 5105, Level 16, State 14, Line 9
A file activation error occurred. The physical file name 'C:\slink_myDb\FILESTORE' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 1813, Level 16, State 2, Line 9
Could not open new database 'myDb'. CREATE DATABASE is aborted.

How to attach a sql db with blobs via unc paths ? (without copying; db files are local, sql server is remote)

ps All needed permission ntfs and sharing are granted. I've tried fsutil

fsutil behavior set SymlinkEvaluation L2L:1 R2R:1 L2R:1 R2L:1
sql-server
database
network-share
remote-access
symbolic-link
asked on Server Fault Jun 12, 2019 by isxaker

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0