Get Sql server 2008 default data folder path via s

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get Sql server 2008 default data folder path via s

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-04-13 : 12:41:25

How can I get Get Sql server 2008 default data folder path via script.
I don't wanted to access registry value.
What I am doing is creating database via script with certain path
Now I wanted the path of default data folder of the database server from which i am executing the script.
I will put this in a variable and then concatenate it with my paths.

My current script is as following

DECLARE @SQL VARCHAR(MAX)
DECLARE @DBName VARCHAR(20)
DECLARE @DBPath VARCHAR(255)

SET @DBName = 'TestMA'
SET @DBPath = 'E:\SQLData\' -- here i wanted the default database path

SET @SQL = '
CREATE DATABASE ' + @DBName + '
ON
PRIMARY
(NAME = ' + @DBName + ',
FILENAME = '''+ @DBPath + @DBName + '.mdf'',
SIZE = 50MB,
FILEGROWTH = 10%),'

Set @SQL = @SQL + '
FILEGROUP [MA_CLUSTERINDEX]
(NAME = MA_CLUSTERINDEX,
FILENAME = '''+ @DBPath + @DBName + '_1.ndf'',
SIZE = 1MB,
FILEGROWTH = 10%),'

Set @SQL = @SQL + '
FILEGROUP [MA_NONCLUSTERINDEX]
(NAME = MA_NONCLUSTERINDEX,
FILENAME = '''+ @DBPath + @DBName + '_2.ndf'',
SIZE = 1MB,
FILEGROWTH = 10%),'

Set @SQL = @SQL + '
FILEGROUP [MA_IMR]
(NAME = MA_IMR,
FILENAME = '''+ @DBPath + @DBName + '_5.ndf'',
SIZE = 1MB,
FILEGROWTH = 10%);'

EXEC (@SQL)

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-04-13 : 13:41:28

SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1

Does the trick

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources