Error While Restoring Databases in SQL Server Cluster-Only Formatted Files On Which The Cluster Resource Of The Server Has Dependency Can Be Used

On By ADsqlIn Uncategorized
Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

Implementation: Restore databases in SQL server fail-over cluster from backup files

Scenario: Today We have been asked to restore few databases from mirrored backup files

The backup files and mirror backup restore syntax were fine and Its two node active passive cluster say AT (Active) and Passive (PS).

Error:

Server: Msg 5184, Level 16, State 2, Line 1. Cannot use file ‘%.*ls’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.

Server: Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.

Infrastructure: This steps are from the Windows server 2008 onward cluster .

To add a disk as a dependency to the SQL Server, the shared cluster disk must reside in the same group in the Cluster Administrator as the SQL Server resources.

Resolution:

First make sure the directory where you are restoring (creating) database should be added in SQL Server Group If not follow below instruction…

To move the shared cluster disk, Open Fail-over Cluster manager–>Go to Disk–>select the disk you want to move to the SQL Server group, and then right-click that resource and Change Group. After the disk is in the same group in which the SQL Server resource resides, follow these steps to add it as a SQL Server dependency:

  1. Open the Cluster Administrator (cluadmin).
  2. Make sure that all the physical disk resources that contain SQL Server databases are in the same group as the SQL Server resource.
  3. Right-click the SQL Server resource, and then bring the resource into an Offline state by clicking Bring Offline.
  4. Right-click the SQL Server resource, and then click Properties.
  5. Click the Dependencies tab.
  6. Click Modify to add the disk to the dependencies list for that resource.
  7. Bring the SQL Server resource back online, and then put the SQL Server files on that shared cluster disk
  8. Once this is done, now execute DB restoration or DB creation command.

Hope this helps!