Reading Default Data and Log Paths With InstanceDefaultDataPath

InstanceDefaultDataPath tells you where SQL Server will put the next new data file, not where your existing files already live. Those are two different questions, and mixing them up is how databases end up on the wrong drive.

Read the three defaults

Here is a call I have seen more than once. Someone moves the data default to a big new drive, tells the team, and everyone assumes the job is done. Months later the old drive is full anyway. The setting only affects databases created after the change.

Reading the defaults is a plain query. It changes nothing and moves nothing. The folders belong to the server, even when SSMS runs on your laptop.

SELECT CONVERT(nvarchar(4000), SERVERPROPERTY('InstanceDefaultDataPath')) AS DataDefault,
       CONVERT(nvarchar(4000), SERVERPROPERTY('InstanceDefaultLogPath')) AS LogDefault,
       CONVERT(nvarchar(4000), SERVERPROPERTY('InstanceDefaultBackupPath')) AS BackupDefault;

SELECT SERVERPROPERTY('PropertyNameThatDoesNotExist') AS UnknownProperty;

On my test server the data and log defaults point to the same DATA folder, and the backup default points to a Backup folder. Your folders will differ, so run it and look.

Notice one small trap. The data path ends with a backslash, but the backup path does not. If you glue a file name onto the end without checking, you get a path that does not exist. The second query shows the other safe habit: an unknown property name returns NULL, not an error. Keep that NULL visible. Do not quietly replace it with a guessed folder.

Create two databases and compare

Now the interesting part. The first database below gets no file paths at all, so SQL Server uses the defaults. The second gets explicit paths. I point it at the backup folder only because that folder exists on every server. It is a demo, not a good home for data files.

The demo creates two databases and drops both at the end.

DECLARE @DataFolder nvarchar(300) =
    CONVERT(nvarchar(300), SERVERPROPERTY('InstanceDefaultDataPath'));
DECLARE @OtherFolder nvarchar(300) =
    CONVERT(nvarchar(300), SERVERPROPERTY('InstanceDefaultBackupPath'));
IF RIGHT(@OtherFolder, 1) <> N'\' SET @OtherFolder += N'\';

DROP DATABASE IF EXISTS SqlAuthorityDemo;
DROP DATABASE IF EXISTS SqlAuthorityDemoElsewhere;
CREATE DATABASE SqlAuthorityDemo;

DECLARE @Sql nvarchar(max) = N'CREATE DATABASE SqlAuthorityDemoElsewhere
    ON (NAME = SqlAuthorityDemoElsewhere,
        FILENAME = N''' + @OtherFolder + N'SqlAuthorityDemoElsewhere.mdf'')
    LOG ON (NAME = SqlAuthorityDemoElsewhere_log,
        FILENAME = N''' + @OtherFolder + N'SqlAuthorityDemoElsewhere.ldf'');';
EXEC (@Sql);

Now ask the registered file list where each file really is, and compare it with the data default.

DECLARE @DataFolder nvarchar(300) =
    CONVERT(nvarchar(300), SERVERPROPERTY('InstanceDefaultDataPath'));

SELECT DB_NAME(database_id) AS DatabaseName,
       type_desc,
       CASE WHEN LEFT(physical_name, LEN(@DataFolder)) = @DataFolder
            THEN 'Yes' ELSE 'No' END AS UnderDefaultDataFolder
FROM sys.master_files
WHERE database_id IN (DB_ID('SqlAuthorityDemo'), DB_ID('SqlAuthorityDemoElsewhere'))
ORDER BY DatabaseName, type_desc;

The plain database reports Yes for its data file. The one with explicit paths reports No. The log rows follow the same pattern. I compare them with the data default as well, which works here only because my data and log defaults are the same folder. If yours differ, compare the log rows with the log default instead.

That is the whole lesson in two rows. The default is a promise about the future. The registered path is the fact about today.

Find files that do not follow the default

On a real server, run the same comparison across every database. Anything that says No either was created before the setting changed or was placed on purpose. Either way, you now have a list to review, one database at a time, with the owner.

DECLARE @DataFolder nvarchar(300) =
    CONVERT(nvarchar(300), SERVERPROPERTY('InstanceDefaultDataPath'));

SELECT DB_NAME(database_id) AS DatabaseName, type_desc, physical_name
FROM sys.master_files
WHERE type_desc = 'ROWS'
  AND LEFT(physical_name, LEN(@DataFolder)) <> @DataFolder
ORDER BY DatabaseName, file_id;

My demo database with explicit paths appears here, as it should. Expect system databases such as tempdb to appear on some servers, because they are often placed elsewhere on purpose.

Clean up, and plan a change safely

DROP DATABASE IF EXISTS SqlAuthorityDemoElsewhere;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Dropping the databases removes their files, so nothing is left in the backup folder.

If you plan to change a default, check three things first. The folder must exist. The SQL Server service account must be able to write to it. And moving old files is a separate job with its own plan, because changing the default does not touch them. After the change, create a throwaway database the way your team normally provisions one, and read its file paths. Reading the saved setting alone does not prove your deployment used it.

Next time someone says the data moved, ask to see the registered paths.

A default path is not a file move, it is a setting for the next database.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.