While creating a series of test databases, I was required to hardcode a location to store the files. This caused me a problem as I wanted the script to be reusable on different machines \ platforms while also being consistent. I couldn’t enter the path for the SQL Server data files as this can be different, and I couldn’t enter a different path because it might not exist and I wasn’t keen on creating directories via T-SQL and xp_cmdshell.

While, it turns out you can find the location of your default SQL Server store simply by querying the system tables –sysaltfiles and getting the path for a particular database such as master.

DECLARE @device_directory NVARCHAR(MAX)
SELECT @device_directory = SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1) FROM master.dbo.sysaltfiles WHERE dbid = 1 AND fileid = 1

After executing the query, on my local machine the value of @device_directory is ‘C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\’

The variable can then be used when creating the files, for example filegroups for your database.

EXECUTE (N'CREATE DATABASE Northwind
  ON PRIMARY (NAME = N''Northwind'', FILENAME = N''' + @device_directory + N'northwnd.mdf'')
  LOG ON (NAME = N''Northwind_log'',  FILENAME = N''' + @device_directory + N'northwnd.ldf'')')
go

Labels: SQL Server