Need Help Msdb Cannot Be Opened, Marked Suspect By Recovery

[livinthailandos]

I'm using microsoft sql server 2005, second this issue only affects as far as I can tell only doing backup data and restoring data. I have google this issue but most websites assume you know where to find all the files or have experience already dealing sql or msdb issues before

1. I have no experience fixing issues with msdb or sql issues, I don't even know what files to look for or what steps to take

2. I live on koh samui, and i've check with some computer places but the people here have no experience dealing with this issues, most have told me they only fix basic computer problems

3. I have done backup and restore issue but that did not fix the problem

any help or information would be useful.

[matt111]

3. I have done backup and restore issue but that did not fix the problem

What exactly have you done here?

You should be able to recover the database to a point in time, but it looks the auto recovery (rolling back/fwd any pending transactions) has not been possible so its put the database offline to stop the system being in an inconsistent state. When you restarted the system it would of find the last checkpoint and then followed the transactions from transaction log verifying to get you get to a consistent state, my knowledge on SQL server is a little rusty but recovery should be relatively simple with such transactional database, PM me if you want.

[rakman]

First off, I'm not, by profession, a SQLExpress DBA, but I've had my share of things to do with them.

Download SQL Server Management Studio Express, a free download. Get 32 or 64 bit as necessary. This program gives you the ability to manually backup and restore the data, and a whole lot more, such as telling you where the files are.

If you can't get in with SSMSE, google putting SQLExpress in single user mode and add your administrator account to the users.

Typical "sa" account has sa for the password, but may not be on your system depending on how it's configured such as Windows Authentication, sa Authentication or both.

Restore failures are usually because the SQLSERVER service login account won't let the backup routine restore the data. Change the SQLSERVER service logon account to the same account as the backup service uses. Then bounce the SQLEXPRESS service to gain access.

You might also have to set the SQLSERVER access via SQL Server Configuration Manager via Protocols section to enable Named Pipes and TCP/IP and set port 1433 in the tcp/ip protocols section to allow access.

The files, by default, are located in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data for 32 bit. Or in C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data for 64 bit, but can be anywhere depending on who installed it. You can search for the default extensions *.mdf and *.ldf and the name of the files are typically the instance name of the db. It is possible to have multiple instances of SQLExpress and SQL Server on the same machine.

Or, if you want an excellent service to backup and restore your SQL, PM me and we can talk about my backup service based in the US that will work wonders for you.

[buenavad]

Check the log, is located in

Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

it will tell you what's the error about

If you can't understand it, just do this

sp_resetstatus 'msdb'

and try to enter to the database, if you need more help send me PM

cheers