SSMS behaviour if you have Cluster Shared Volume and C drive repeating while choosing location for backup\restore\database attach

Consider this scenario, when you try to attach, restore or taking database backup using SQL SERVER Management Studio GUI and trying to choose the location then you may see C: drive repeating.

ssms_gui_location

 

So this behavior may raise questions like, is it by design behavior? Will this cause any issue? Which drive to choose?

So this typically happened if you are using Cluster Shared Volume in your environment and have more than one CSV located under C drive. In such scenario you may see this behavior while doing any of above database activity using SSMS GUI, so you can choose any drive and this will not cause any issue as all are representing C drive only.

Also worth mentioning that when you have multiple CSV on Server then following DMV basically used in background to pull the drives information which basically return two rows for C drives which contain complete path detail and that’s the reason you may see multiple C drives in SSMS GUI if you have CSV.

select * from sys.dm_io_cluster_valid_path_names

 

 

path_name cluster_owner_node is_cluster_shared_volume
E:\ 45536SQLNODE1 0
C:\ClusterStorage\Volume1\ 45536SQLNODE1 1
C:\ClusterStorage\Volume2\ 45536SQLNODE1 1

 

Just in case if you are using CSV and getting following error message while using SQL SERVER 2014 SSMS and unable to access the path on Server:

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists. If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.

 

Fix for this issue already released for SQL SERVER 2014:

FIX: “Cannot access the specified path or file on the server” when you use CSV in SSMS to back up or restore database

https://support.microsoft.com/en-in/kb/3048627

 

I hope this will help.

 

Vikas Rana (@vikasrana_dba)

Support Escalation Engineer
Microsoft India GTSC