SQL 2008 cluster

Question

I have a problem with the SQL cluster 2008 after I backup one of the volume, format the volume and restore the volume, then the cluster SQL agent can't start anymore. I did so many times but everything was always fine. Now if I do the restore one Volume for the transaction log then SQL instance can't startup at all.
I'm having two errors:
1) msdb can't start.
2) login failure of the SQL service account.
Is it related? the volume for SQL data hasn't change at all.
I can't even restore the msdb database.

restore msdb

when repairing sql installation

Answer 1

Everything that's failing here seems to be pointing to a corrupt MSDB. The MSDB contains backup history and maintenance plans, as well as information about the SQL Agent jobs. Not sure how much if this you're using, but ideally your answer is "not much."

There's a fresh, original copy of the MSDB, leftover from installation, in the binn\templates folder until your SQL Server installation - for me, it's at:

C:\Program Files\MicrosoftSQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\Templates

Worst case, you can stop your SQL Server service, take the msdbdata.mdf and msdblog.ldf files from this folder and replace your live msdb files with them, and then restart your SQL instance - at least the files will be intact. You'll have to rebuild the contents, like SQL Agent jobs, but as I mentioned earlier, hopefully that's not a big undertaking.

Another option is to take a copy of your live data and log files and attach them to another SQL instance somewhere, where you might have the option to repair them. Do you have another SQL Server instance in the office, and if so, what happens when you try to attach your MSDB there (as another name, obviously)?

In an extreme case, if the database does not recognize attached files then you can try to repair sql file using Repair MDF Kit and try the new recovered files to attach to the database and start again.