SQL SERVER – Identifying Database Default Locations for Data and Logs

How often have you had the opportunity to go back to basics and learn the same thing again in a different manner? How many of you are developers here? I am sure each one of us write a piece of code that gets onto the production – how often have you been revisiting the same code say after a week post release. Have you ever tried to look at the code to enhance it or do the same thing differently? I am sure the majority of you never even want to look at the old code you wrote. This is second to human nature and trust me, you are not alone in this planet with this attitude. Let us learn about Database Default Locations.

I am no different, but I always am on the hunt to learn new ways to work for the same old problems we had for ages. When you just got Management studio and write a “CREATE DATABASE” command without any paths, where does the files get created? Often it is a mystery and for new age DBA’s it would be almost impossible. I wanted to check the blog about this and found a number of posts describing the problem – either using TSQL, UI etc. Some are here for a reference.

SQL SERVER – Changing Default Installation Path for SQL Server

SQL SERVER – Find Location of Data File Using T-SQL

From a recap perspective, the best solution and the most used solution to this is using the UI. We can go to SQL Server Management Studio -> Right Click on Server node -> Properties -> Database Settings. We will be presented with a section where the default location for our database data and log files are present.

SQL SERVER - Identifying Database Default Locations for Data and Logs Database-default-locations-01

From SQL server 2016, there is an enhancement added to the SERVERPROPERTY () function, wherein we can get this information that is useful.

-- SERVER PROPERTIES added for Default Paths
SERVERPROPERTY('InstanceDefaultDataPath') AS InstanceDefaultDataPath,
SERVERPROPERTY('InstanceDefaultLogPath') AS InstanceDefaultLogPath

As you can see now, we have gathered this important information via standard TSQL without any registry hacks or string manipulations etc.

SQL SERVER - Identifying Database Default Locations for Data and Logs Database-default-locations-02

As I personally felt this was an interesting addition. It was also nice to know that a total of 8 more properties was added to the SERVERPROPERTY function.

SERVERPROPERTY ('InstanceDefaultDataPath') AS InstanceDefaultDataPath,
SERVERPROPERTY ('InstanceDefaultLogPath') AS InstanceDefaultLogPath,
SERVERPROPERTY ('ProductBuild') AS ProductBuild,
SERVERPROPERTY ('ProductMajorVersion') AS ProductMajorVersion,
SERVERPROPERTY ('ProductMinorVersion') AS ProductMinorVersion,
SERVERPROPERTY ('ProductBuildType') AS ProductBuildType,
SERVERPROPERTY ('ProductUpdateLevel') AS ProductUpdateLevel,
SERVERPROPERTY ('ProductUpdateReference') AS ProductUpdateReference

I am sure you will find some of these interesting and please make sure to use these in your environments as SQL Server 2016 gets rolled out. I am sure there will be tons of learnings for sure.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Log, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Huge Transaction Log in Snapshot Replication!
Next Post
SQL SERVER – InMemory OLTP Migration Assistant Powershell Script

Related Posts

4 Comments. Leave new

  • Here a small script with a fallback to the SQL data root path stored in the registry:

    DECLARE @instanceName SQL_VARIANT, @instancePath SQL_VARIANT, @sqlRootRegKey NVARCHAR(1024)
    , @sqlDataRoot NVARCHAR(512), @sqlDataPath NVARCHAR(512), @sqlLogPath NVARCHAR(512);

    — Get SQL data root path from registry as fallback if database default paths are empty
    SELECT @instanceName = ISNULL(SERVERPROPERTY(‘InstanceName’), ‘MSSQLServer’);
    EXECUTE [master].dbo.xp_regread ‘HKEY_LOCAL_MACHINE’, ‘SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL’, @instanceName, @instancePath OUTPUT;

    SELECT @sqlRootRegKey = ‘Software\Microsoft\Microsoft SQL Server\’ + CONVERT(VARCHAR(64), @instancePath) + ‘\Setup’;
    EXECUTE [master].dbo.xp_regread ‘HKEY_LOCAL_MACHINE’, @sqlRootRegKey, ‘SQLDataRoot’, @sqlDataRoot OUTPUT;

    — Read database default paths with fallback to SQL data root path
    SELECT @sqlDataPath = ISNULL(CAST(SERVERPROPERTY(‘InstanceDefaultDataPath’) AS NVARCHAR(512)), @sqlDataRoot);
    SELECT @sqlLogPath = ISNULL(CAST(SERVERPROPERTY(‘InstanceDefaultLogPath’) AS NVARCHAR(512)), @sqlDataRoot);

    SELECT @sqlDataPath, @sqlLogPath, @sqlDataRoot;

  • Thanks for this good article.

  • SERVERPROPERTY (‘InstanceDefaultBackupPath’) AS InstanceDefaultBackupPath is also an option :-)


Leave a Reply