Enumerate drives available to SQL Server

Question

I'm trying to work out a way of listing the drives that a particular instance of SQL Server is "aware" of, and understand the process.

I can run EXEC master..xp_fixeddrives which gives me a list of drives, but when I look in the GUI (Eg. Databases | Restore Database | From device | Add ) the list of available drives is far fewer.

By what process does SQL server become "aware" of a drive and is there any way to problematically determine the drives it is aware of?

Thanks in advance!

Answer 1

I'm guessing you're actually running on a clustered instance.

EXEC master..xp_fixeddrives is returning all the drives that the underlying OS is aware of, so a lot aren't available to your cluster instance

use:

SELECT * FROM sys.dm_io_cluster_shared_drives

instead, which will just list the ones available to your instance

Answer 2

SQL Server 2008 R2 SP1 and SQL Server 2012 add a new function, sys.dm_os_volume_stats, which - unlike xp_fixeddrives - is documented and supported (and includes more useful information as well). The catch is that it will only return information for drives that have at least one data or log file on them, since it takes database_id and file_id as input:

SELECT d.* FROM sys.master_files AS f 
  CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS d;

Result set:

database_id                 int
file_id                     int
volume_mount_point          nvarchar(256)
volume_id                   nvarchar(256)
logical_volume_name         nvarchar(256)
file_system_type            nvarchar(256)
total_bytes                 bigint
available_bytes             bigint
supports_compression        tinyint
supports_alternate_streams  tinyint
supports_sparse_files       tinyint
is_read_only                tinyint
is_compressed               tinyint