How to know default location of data & log files in SQL Server

Hello Friends,

If I say, How I can create a database dynamically? you most probably tell me “easy man”!!! , just put CREATE DATABASE script in dynamic query string with database name as a parameter and execute it, whenever you want. Absolutely correct!!!;

But If I say I have number of servers and don’t want to go and manually alter the script with it’s appropriate file-path location. Then, above idea will fail; because, we put file-path (log & data files) as a plain text in the query string.

To serve this purpose, SQL Server 2012 onward, very interesting parameters named “InstanceDefaultDataPath” & “InstanceDefaultLogPath” added in SERVERPROPERTY function.

This gives you default location, configured for data and log files respectively.  Try below code ….

SELECT SERVERPROPERTY('InstanceDefaultDataPath') AS [Data File Path]
, SERVERPROPERTY('InstanceDefaultLogPath') AS [Log File Path]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s