SQL Cluster Failed lost Primay SQL instance

>> I now have my E Drive back but it is empty.
>> There is no SQL Server failover cluster available to join

If you don't have a DR node available, then you might need to reconfigure Cluster(as you mentioned you couldn't identify the cluster at all) and then SQL from the scratch and then restore all the database backups you have..

This is one scenario where you can learn a lot:
1. Keep Transaction Log file sizes in control by taking Log backups regularly.
2. Monitor the free space available in Drives regularly and act on it immediately.
3. Perform Capacity planning well in advance so that your drives will not be used completely causing these kind of issues.

Hi I got it back up by doing this command

cd \program files\Microsoft sql server\120\setup bootstrap\sqlserver2014
setup /q /action=rebuilddatabase /instancename=mssqlserver /sqlsysadminaccounts=sa /sapwd=youreyesonly99

The master default and model etc databases where created.

I was able to start the mssqlserver service and open in SSMS

I stopped the mssqlserver service  and ran this net start mssqlserver /m    places sql in single user mode so I can restore the master database

from an administrator cmd prompt I try this

sqlcmd  -S  TGCS014-N1-SQLC -E    hit enter get 1> prompt
1> RESTORE DATABASE master FROM DISK = 'F:\path\master.bak' WITH REPLACE
2>  GO

Now off to restore the other databases


My problem is I can not restore the databases now

Error

System.data.sqlclient.sqlerror cannot use file e:\sql-data\database.mdf for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.  Either the disk respource containing the file in not present in the cluster group or the cluster resource of the sql server does not have a dependency on it   Microsoft.sqlserver.smoextended

Any ideas?  

Thanks

All my user databases are in Recovery Pending

I do not have any mdf or ldf files  

I just have .bak files

I tried just restore database mydb

The restore statement is invalid in the current context The recover data only option is only defined for secondary filegroups when the dtabase is in an online state. when the database is in an offline


How can I restore my databases

Urgently needed

I think I know why I can not restore

The cluster roles has a failed status.

That's because SQLAGENT service is not running.


After restoring the master database and then the MSDB i should have been able to start the SQLAGENT service but it will not start.

Any ideas?


Please help

>> The cluster roles has a failed status.
>> After restoring the master database and then the MSDB i should have been able to start the SQLAGENT service but it will not start.

Can you kindly check what is the error message you are receiving in Cluster and SQL Server Agent error log as that might help resolve the issue.
First, I would suggest you to see whether your SQL Server and SQL Server Agent Service are added and available in Cluster service properly or not.
If not, then I would suggest you to get the Cluster issues fixed out completely by ensuring all nodes, disks or other resources are listed properly..
Once this is done, then we can check the SQL Server Agent Error Logs in SQL Server installation logs path to see why SQL Server agent service is not starting up..

Only messages I see in the sql logs are users attempting to connect to the user database.  all fails since database is not there.

No sql agent logs since sql agent is not running

From services I try to start sqlagent it starts and stops right away

On SSMS  it shows    SQL SERVER AGENT (AGENT XP's disabled)   how do i enable that right click on it and the menus is mostly greyed out

Once I get SQL Agent started I think things will be good.

I removed SQLagent from the cluster and now the  SQL Cluster role is running

Also on SSMS the SQL Server ICON is WHITE NOT GREEEN  

Is there a sqlcmd to restore my user databases an example please.

I tried and it tell me that you can only restore master database in single user mode.

I am not running in single user mode  is there a way to check that too?


Sorry for so many questions

Thank you,

System is still down

Still having major issues

SQL Agent will not start that's first problem

I ran this

sp_configure 'show advanced options',1

go

reconfigure with override

go
 
sp_configure 'Agent XPs',1

go

reconfigure with override

go
 
sp_configure 'show advanced options',0

go

reconfigure with override

go


No change still ca not start sqlagent service


The databases are still unable to be restored but if we get sqlagent working then we can tackle the database issues.


Help needed

Guys Update.

I got sql agent running it was a permissions problem on the log folder

NOW MY ONLY PROBLEM is I can not restore any user database.



Looking for assistance


Thank you

Tom

Raja

NP  the article is very old

All disks including the failed E Drive are configured as dependences

I ran a dependency report everything looks good

I am trying to add the SQL agent now but it fails the role.

I think it is because  from the Microsoft SQL Server Management Studio   I see white icons by The SQL Server name and the SQL Server agent
they should be green


 

The services are started why they white?  should be green  my other instances are green

Any ideas.

>> I see white icons by The SQL Server name and the SQL Server agent they should be green

If it is green then it indicates that your current account have privileges to see SQL Server service status, if white your account doesn't have privileges to see the Service status but the Service is running so not a problem.

In the meanwhile, why all databases are still in Recovery pending status, how long are they in this status..

>> I am trying to add the SQL agent now but it fails the role.

SQL Server Agent Service is dependent on SQL Server service as well, kindly ensure that dependency is configured in cluster or not..

Raja

Yes I understand that the SQL agent has to be part of the cluster but it will not let me add it.  when I do it stops the role on the cluster.

Need to understand why I can not add the sql agent to the cluster.

Rega.

I figured out why when I added the sql agent to the cluster I needed to go into the properties and set the name of the sql server and check the other settings now all three instances are online.

My final issue is to restore the USER databases.

What is a good procedure for doing that?

Every thing I tried falls

Thank you

RAJA

I got it back up and running now.

I had to add the new disk to the dependency on the cluster role.

Thanks for all your help

>> I had to add the new disk to the dependency on the cluster role.

Glad to hear you had it resolved..
Yes, this is what I was referring to earlier..

Add appropriate dependencies for SQL Server, SQL Server Agent Service and all the clustered volumes(your E: drive which went down) to get it working..