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]