How to Find Where Your Databases Reside In The File System

SQL Script:

/*
RETURNS LIST OF FOLDER NAMES WHERE SQL SERVER DATABASE
FILES ARE STORED. 
It is dynamic enough to be 
used on even much older versions of SQL Server.
*/
DECLARE @PRODUCTVERSION NVARCHAR(132)
DECLARE @PERMISSION_FLAG bit
SET @PRODUCTVERSION = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(32)) 
SET @PERMISSION_FLAG = 1
-- For use on older versions of sql server or if you don't have permission to sys.master_files
-- tested in sql 2000 & 20005
if SUBSTRING(@PRODUCTVERSION, 1, CHARINDEX('.', @PRODUCTVERSION)-1) < 10 OR @PERMISSION_FLAG = 0 OR @PERMISSION_FLAG IS NULL
BEGIN
PRINT 'We gonna use old school method.....'
set nocount on if object_id('tempdb..#t1') is not null drop table #t1
create table #t1 (fpath varchar(4000), fname varchar(8000))
insert into #t1
exec sp_msforeachdb 'select left(filename, len(filename) - charindex('''', reverse(rtrim(filename)))) fpath, filename fname from [?].dbo.sysfiles'
select distinct fpath from #t1 order by 1
END
-- For use on SQL version xxx and above 
ELSE 
with cte as
(
select 
case 
when type = 2 then physical_name     -- FILESTREAM
else left(physical_name, len(physical_name) - charindex('', reverse(rtrim(physical_name)))) 
end folder_path, 
physical_name  
from sys.master_files
)
select distinct 
folder_path,
[create_folder] = 'mkdir -p "' + folder_path + '"'
from cte order by folder_path;

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 (mkdir -p), 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.