Identify the location of your SQL Server databases using T-SQL

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.

  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”)’)

Leave a Reply

Your email address will not be published. Required fields are marked *