Any time you create a database in SQL Server, two files are created. One is the data file, and the other is the transaction log file.

The location of these files will depend on whether or not you explicitly specify a location for these files when you create the database. If not, they will be created in the default location.

You can find the default location with the following code:

SELECT
  SERVERPROPERTY('InstanceDefaultDataPath') AS 'Data Files',
  SERVERPROPERTY('InstanceDefaultLogPath') AS 'Log Files'

Running this with SQL Operations Studio on my Mac results in this:

However, just because we now know what the default locations are, it doesn’t mean that we know for sure where all data and log files are for all databases on that SQL Server instance. It’s possible that different databases on the server use different locations for their files.

The default location is only used if you don’t specify a location for the files when you create the database. For example, if you create a database in the simplest possible way (e.g. CREATE DATABASE my_database) its data file and log file will be created in the default location.

However, if you explicitly specify a different location for these files, they will reside in that other location. Therefore, it’s possible that not all database files will reside in the default location.

Here’s how to find the location of all data files and log files in SQL Server. That will display a list of all data files and log files along with their full paths.