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 FilesMicrosoft SQL ServerMSSQL.3MSSQLDATA’
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