How to use a relative file path when adding a file to MSSQL database

Question

I'm creating a database with a filegroup and a file:

CREATE DATABASE SuperDb;

ALTER DATABASE SuperDb
ADD FILEGROUP FileStreamGroup CONTAINS FILESTREAM

ALTER DATABASE SuperDb
ADD FILE
(
    NAME = Blobbiez,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BLOBZ'
)
TO FILEGROUP FileStreamGroup;

This script does its job, but I want to use relative path for a file, because it will be executed in different machines. The file (actually, the folder) must be located just near the database file. How to achieve it?

sunysen · Accepted Answer · 2014-11-18 08:18:03Z

1

declare @path varchar(200) 
exec master.dbo.xp_regread 
'HKEY_LOCAL_MACHINE', 
'SOFTWARE\Microsoft\MSSQLSERVER\setup', 
'SQLPath',@path output 
set @path = @path + '\data\' 
print @path

answered Nov 18, 2014 at 8:18

sunysen

2,3711 gold badge15 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

astef Over a year ago

Thank you, it works. But I still beleive the "native" way exists

Tilo Over a year ago

that doesn't give you the default data directory of the SQL instance but the install ROOT with can be different!. see here for the correct check stackoverflow.com/questions/1883071/…

Collectives™ on Stack Overflow

How to use a relative file path when adding a file to MSSQL database

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related