Error while trying to restore SQL database to a cluster

 

Heavy-Equipment -256After a major round of engineering work on a large SQL cluster the DBA’s noted that they were unable to restore databases on one the clustered SQL instances. When they tried they got the following error message:

System.Data.SqlClient.SqlError: Cannot use file ‘’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it. (Microsoft.SqlServer.SmoExtended)

The specific scenario of why it happened was that the cluster was being migrated to VHDX’s and some resource dependencies were missed so the typical scenarios where this error comes up didn’t apply in quite the same way. I stepped in to troubleshoot this in isolation rather than part of the original engineering efforts so had to deduce a few things too figure out what had gone wrong.

Troubleshooting

Running the cluster validation wizard did not reveal any issues but knowing that the disks had undergone a great deal of change recently was useful.

The configuration inconsistency was obvious when running the ‘Show Dependencies Report’ on the SQL instance which, when compared with the other instances showed clear differences.

image

The fix

Launch failover cluster manager, select Roles > Your SQL Instance, in the details pane below select ‘Your SQL instance’ under the ‘other resources’ section.

image

Click on the properties button in the right hand pane and select the dependencies tab.

image

I went though and added all the missing resources. The only thing that didn’t need to be added was ‘SQL IP Address’.

image

No service restarts required or interruption to service.

Hope this helps.

References

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/90ae6025-9bca-4306-a81a-097dfe29a954/how-to-add-a-2nd-iscsi-virtual-drive-to-a-2-node-sql-server-2012-cluster?forum=sqldisasterrecovery

http://mssqlnuggets.wordpress.com/2014/06/05/sql-2012-system-data-sqlclient-sqlerror-cannot-use-file-for-clustered-server-only-formatted-files-on-which-the-cluster-resource-of-the-server-has-a-dependency-can-be-used-either-the-disk-res/

https://go4answers.webhost4life.com/Example/error-message-trying-restore-db-cluster-129982.aspx

http://support.microsoft.com/kb/835185

http://msdn.microsoft.com/en-us/library/ms177447(v=sql.110).aspx

Senior consultant and company director at The Full Circle. Passionate about automation scripting, end user experience and cloud technology.

Comments are closed.