Unable to perform Backup or Restore to Cluster Shared Volume(CSV) Using SSMS GUI in SQL Server 2014
I had been playing with new features of SQL Server 2014 and I encountered an issue with SQL Server 2014 Cluster Shared Volume(CSV) related to Backup/Restore. Refer, if you are new to Cluster Shared Volume(CSV) whose supported has been introduced starting SQL Server 2014. This is a Windows feature which has been available since Windows Server 2012, but SQL Server only started supporting from SQL Server 2014.
I have setup two node Windows Server 2012 cluster and installed one SQL Server 2014 clustered instance. I have added a disk as Cluster Shared Volume(CSV).
When I try to backup a database, I encountered an issue where I tried to perform backup of a database and normally we see file structure where we can browse to choose the location where to backup the databases, but I see a blank file structure, so nothing there to select any path. If I try to specify the backup path manually to CSV disk, I get an error as below. as mentioned in the error, I have checked permissions to the CSV path, but no issues with the permission. I am able to create databases on CSV and access them, but just could not use the CSV path to perform backups/restores.
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.
After some research, I found out couple of workarounds to perform backups or restores.
Alternative 1 – Use T-SQL for performing backup from SSMS Query Analyzer
Alternative 2 – Follow below mentioned steps
– Add a regular cluster disk(Non Clustered Shared Volume(CSV)) to SQL Server Group in the cluster from Cluster Administrator and then add the new disk resource as dependency to SQL Server Group.
– From SSMS, right click on the SQL Instance properties, go to database settings tab, copy the existing path and save it for future reference and then change the path to the new regular disk which we just added, this is just like how we used to be in SQL Server 2012 or previous versions.
– Now try to perform the backup and you will be able to open the locate backup file and locate database files dialog with out any errors. We can also specify the backup path manually to CSV, but the path still will not be visible from the GUI to browse.
– Later, the new regular storage which we added before could be removed and then change back the default backup location to CSV path which we saved before changing to to new disk in Instance properties -> Database Settings tab.
– You should not encounter any more issues and now you can also see CSV path from GUI to choose path to backup/restore.
There is a Connect bug opened for this issue where we cannot see CSV path using SSMS GUI while trying to perform backup/resstore. You may check on the connect page to see any updates on fixing this issue in coming Service Pack/Cumulative Updates.
Hope this was helpful.