2

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
Improve this question
2
  • Are you not running this in an unsupported configuration by using the symbolic links?
    – George.Palacios
    Jun 12, 2019 at 15:03
  • @George.Palacios yes I am.
    – isxaker
    Jun 12, 2019 at 15:15

1 Answer 1

Reset to default
1

Apparently it is not supported to use "localhost"

https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-with-smb-fileshare-as-a-storage-option?view=sql-server-2017

You may need to use another server instead of localhost.

Improve this answer
1
  • I've double checked it. Yes, it's true that you shouldn't use localhost(tested in sql 14 and 16). But anyway the problem is different. I have 2 pretty the same cases except sql servers are different(2014 and 2016) and with sql 2014 a db can be attached but with sql 2016 I can't attach the same db.
    – isxaker
    Jun 17, 2019 at 7:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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