Cannot open database "msdb"

Question

Hi,

In my SQL Server, when I right-click on any database, to backup the database, I got the error message Cannot open database "msdb" requested by the login. The login is failed. Login failed for the user 'sa'. (Microsoft SQL Server, error: 4060)

Also, in System Databases, msdb is in suspect mode.

What should I do to recover from this?

Answer 1

Check the following links:

Rebuild System Databases :

https://msdn.microsoft.com/en-us/library/dd207003.aspx

How to Fix a Corrupt MSDB SQL Server Database:

https://www.mssqltips.com/sqlservertip/3658/how-to-fix-a-corrupt-msdb-sql-server-database/

How to repair corrupted msdb database in Microsoft SQL server 2008 R2:
https://mhlavaty.wordpress.com/2013/02/07/how-to-repair-corrupted-msdb-database-in-microsoft-sql-server-2008-r2/

And also make sure you are entering correct password for sa login.

---

Please click "Mark As Answer" if my post helped. Vijay.

Answer 2

First can you check is this really suspect by using any other command which has right permissions-

SELECT DATABASEPROPERTYEX('msdb', 'Status')

Also please check in the sql errorlog- what is the reason for MSDB went suspect(there are many reasons)- can you paste it.

1.Verify you have connected SSMS with run as administrator from other account to see whats the stauts?.

2.Verify what is that status of the account "sa" ?.

3.what is the sql version -run select @@version and share.

4.Verify do you have the latest sql db backup in place.

Links-

https://www.mssqltips.com/sqlservertip/3658/how-to-fix-a-corrupt-msdb-sql-server-database/
https://sudeeptaganguly.wordpress.com/2012/06/19/rebuild-msdb-database/


In 2005
https://rip747.wordpress.com/2008/05/26/rebulding-msdb-in-sql-server-2005/
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2006/06/06/how-to-recreate-the-msdb-database-in-sql-server-2005/

before you go requesting you to check the "sa" account has proper permissions to it(incase)

---

Regards, S_NO "_"