Post
Ask Question

SQL Server - Get The Default Path Of Data And Log Files

In this blog, we will see how to get the default path of data and log files using "SERVERPROPERTY".

What is "SERVERPROPERTY"?

"SERVERPROPERTY" is a System-defined function used to return the SQL Server instance information.

"SERVERPROPERTY" Syntax

  1. SERVERPROPERTY ('propertyname')
Get the default path of data files using "SERVERPROPERTY".

Use INSTANCEDEFAULTDATAPATH property to get the default path of the data files.

Example
  1. select SERVERPROPERTY('InstanceDefaultDataPath') as 'Data Path'
Output



Get the default path of LOG files using "SERVERPROPERTY".

Use INSTANCEDEFAULTLOGPATH property to get the default path of log files.

Example
  1. select SERVERPROPERTY('InstanceDefaultLogPath') as 'Log Path'
Output



Get the default path of data and log files using PowerShell?

You can use Windows PowerShell to invoke the SQL command on a reachable server within the network using Invoke-Sqlcmd cmdlet as the following.
  • Open Windows PowerShell as Administrator.
  • Type the Invoke-Sqlcmd with the below parameters.

    • -query: the SQL query that you need to run on the remote server.
    • -ServerInstance: the SQL server instance name.
    • -Username: the username that has sufficient permission to access and execute SQL query on the remote server.
    • -Password: the password of the elevated user.

      PS SQLSERVER:\> Invoke-Sqlcmd -query "select SERVERPROPERTY('InstanceDefaultDataPath') as 'Default Path of data files'" -ServerInstance "epm\epmdb" -Username sa -Password *****


Applies to
  • SQL Server 2012 through the current version of updates beginning in late 2015.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

Reference

See Also