I use the following query for attaching a
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
If db has
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
mklink /D "C:\slink_myDb" "\\localhost\myShare"
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)
All needed permission
ntfs and sharing are granted.
fsutil behavior set SymlinkEvaluation L2L:1 R2R:1 L2R:1 R2L:1