sql-servert-sqlrelative-pathfilegroup

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

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?

Solution 
  • 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