How to Find Where Your Databases Reside In The File System

SQL Script:

/*****************************************************************************************
 Script: Get SQL Server Database File Folder Locations
 Purpose: Returns DISTINCT list of all folders containing SQL Server database files 
          (data + log + FILESTREAM) across all databases on the instance.

 Features:
  * Works on SQL Server 2000-2025
  * Generates TWO create-directory methods:
     - xp_create_subdir: T-SQL for Windows/SQL Agent jobs (xp_cmdshell required)
     - mkdir -p: Linux shell commands for backup/migration targets
  * Falls back to legacy method if VIEW SERVER STATE denied or old version

 Usage: Run as sysadmin or user with VIEW SERVER STATE permission
******************************************************************************************/

SET NOCOUNT ON;

DECLARE @PRODUCTVERSION NVARCHAR(132)
DECLARE @PERMISSION_FLAG bit = 1  -- Set to 0 to force legacy path (testing)

-- Get major version number (e.g. '15' for SQL 2019, '16' for 2022)
SET @PRODUCTVERSION = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(32)) 

-- Extract major version: everything before first decimal point
DECLARE @MAJOR_VERSION INT = CAST(SUBSTRING(@PRODUCTVERSION, 1, CHARINDEX('.', @PRODUCTVERSION)-1) AS INT);

-- LEGACY PATH: SQL Server 2000-2008R2 OR no VIEW SERVER STATE permission
IF @MAJOR_VERSION < 10 OR @PERMISSION_FLAG = 0
BEGIN
    PRINT 'Using legacy method (SQL 2000-2008R2 compatibility or VIEW SERVER STATE denied)...';
    
    -- Clean up any existing temp table
    IF OBJECT_ID('tempdb..#t1') IS NOT NULL 
        DROP TABLE #t1;
    
    -- Temp table to hold folder paths and filenames from sysfiles
    CREATE TABLE #t1 (
        fpath   VARCHAR(4000),  -- Folder path only
        fname   VARCHAR(8000)   -- Full filename for reference
    );
    
    -- sp_MSforeachdb visits every DB on instance (undocumented but reliable)
    INSERT INTO #t1 (fpath, fname)
    EXEC sp_MSforeachdb '
        SELECT 
            LEFT(filename, LEN(filename) - CHARINDEX(''\\'', REVERSE(RTRIM(filename)))) AS fpath,
            filename AS fname 
        FROM [?].dbo.sysfiles
    ';
    
    SELECT DISTINCT fpath AS folder_path
    FROM #t1 
    ORDER BY fpath;
    
    DROP TABLE #t1;
END

-- MODERN PATH: SQL Server 2012+ with sys.master_files (recommended)
ELSE 
BEGIN
    PRINT 'Using modern sys.master_files method (SQL 2012+)...';
    
    WITH cte AS (
        SELECT 
            CASE 
                -- FILESTREAM files (type=2) use full physical_name as "folder"
                WHEN type = 2 THEN physical_name     
                -- Regular files: strip filename to get folder path
                ELSE LEFT(physical_name, 
                         LEN(physical_name) - CHARINDEX('\', REVERSE(RTRIM(physical_name)))) 
            END AS folder_path, 
            physical_name  
        FROM sys.master_files
        WHERE type IN (0,1,2)  -- ROWS, LOG, FILESTREAM only
    )
    SELECT DISTINCT 
        folder_path,
        'EXEC master.dbo.xp_create_subdir N''' + folder_path + '''' AS xp_create_subdir,
        'mkdir -p "' + folder_path + '"' AS create_folder
    FROM cte
    ORDER BY folder_path;
END

This SQL script is designed to return a list of distinct folder paths where SQL Server database files are stored. It adapts its behavior based on the version of SQL Server it's running against and whether specific permissions are set. 

The output of the script includes a column with command to create directories, using SQL command xp_create_subdir (preferred) or using mkdir -p methods, useful in cases where this script is part of a larger process, perhaps in database migrations or backups.

---

Use Cases:

Finding the folder paths where SQL Server database files are stored can be beneficial for several operational, management, and maintenance tasks related to database administration and infrastructure management. Here are some key use cases:

1. Backup and Restore Operations

- Backup Scripts: Knowing the folder paths helps in scripting backup operations, especially when you want to NOT save the backup files in the same location as the database files or in a related directory structure.

- Restore Operations: When restoring databases, especially to a new server or instance, knowing the original file locations can be crucial for planning the restore process, particularly if you need to maintain a specific directory structure or adhere to storage policies.

2. Disaster Recovery Planning

- Identifying the storage locations of database files is essential for creating effective disaster recovery plans. It allows administrators to ensure that all necessary files are included in backups and can be quickly restored in case of a failure.

3. Capacity Planning and Monitoring

- Storage Management: Monitoring the disk space usage of SQL Server file paths can help in forecasting future storage requirements and preventing disk space shortages that could lead to database downtime.

- Performance Optimization: File placement can significantly impact database performance. Knowing where files are stored enables DBAs to distribute files across different storage subsystems based on performance characteristics.

4. Database Migration

- When migrating databases between servers or instances, it's important to know the current storage paths to replicate the environment accurately on the target server or to plan new storage configurations that improve performance or resource utilization.

5. Auditing and Compliance

- For compliance with internal policies or external regulations, it may be necessary to verify that database files are stored on secure, encrypted, or approved storage devices. Knowing the file paths helps in auditing these practices.

6. Server and Storage Configuration

- High Availability (HA) and Disaster Recovery (DR) Solutions: Configuring solutions like Always On Availability Groups, Database Mirroring, or Log Shipping often requires precise control over file locations.

- Storage Optimization: Identifying file locations helps in moving files to optimize storage across different media (SSDs, HDDs) or to balance I/O load.

7. Troubleshooting and Maintenance

- When dealing with file corruption, access issues, or performance problems, knowing the exact location of database files is the first step in diagnosing and resolving such issues.

8. Automated Scripting and Tooling

- Automating database tasks such as backups, file growth monitoring, or applying storage policies often requires scripts that know where database files are located. Scripts may also need to create or modify directories based on these paths.