I've just spent a bunch time researching an answer to this question on the new disaster recovery forum because I couldn't find any definitive info on how to do this in SQL Server 2005. I pieced together a method to do this on previous releases of SQL Server and tried it on one of the SQL Server 2005 instances on my laptop to make sure it works. I'd like to share it with everyone to save someone having to do the same.
If your msdb goes suspect then you have two choices, restore it from a backup or recreate it (and then recreate any scheduled jobs). Obviously everyone has a comprehensive and valid set of backups, right? If only...
Of course, the very first thing you do is work out why it went suspect in the first place and take any necessary steps to stop it happening again.
Now you'd hope that if you don't have a valid msdb backup then you can at least run repair on it and so you don't lose everything in there. Well, that works as long as the transaction log isn't damaged. Ok, but then surely we can stick the database into the now-documented emergency mode (alter database dbname set emergency) and run emergency mode repair? (dbcc checkdb (dbname, repair_allow_data_loss) in emergency mode). Nope, msdb can't be put into emergency mode.
So, you're out of options and you're going to have to recreate msdb. Here's what to do (change the directory paths to suit your installation):
This works on SQL Server 2000 as well.
Hopefully you'll never have to do this but if you ever do, let me know how it goes for you.
Here's my findings trying this on SQL Server 2008:
You need to add startup parameter -s <instancename> if it is a named instance. Now, this I knew, but for the sake of other potential readers...
I initially started the instance from the Windows services applet by adding -T3608. That didn't allow for detaching msdb. So I started from an OS command prompt and also added -c. This allowed me to detach msdb.
I now ran instmsdb, but that produced a number of errors. Here are a few comments about some of them:
* Complaints on xp_cmdshell. I did try enabling this first and then ran instmsdb again but same result.
* Bunch of errors when creating various Data Collector objects. This wasn't good, because cleaning up DC was the reason to rebuild msdb in the frist place.
* 3 errors about sp_configure and -1 wasn't allowed value (two for Agent Xps and one for xp_cmdshell).
Just for the sake of trying, I now tried to connect to the instance using SSMS Object Explorer. But I now got some error regarding Agent Xp's when connecting. I tried to explicitly enabling Agent XP's using sp_configure but same error. When connected there's no node in Objects Explorer for Agent.
I took this as an indication that Agent isn't healthy. Whether it was me doing something fishy or it isn't as easy as just running insmsdb.sql for SQL Server 2008 - I don't know. But I'm in for a rebuild of system databases. This isn't that bad since it is a just a test machine. But these issues might serve as example why you want to follow Paul's initial advice: always backup msdb (also on test machines).
Because of the problems I had removing Data Collector I decided to rebuild msdb. You probably heard about
PingBack from http://www.easycoded.com/rebuilding-msdb-on-sql-server-2008/
PingBack from http://paidsurveyshub.info/story.php?title=sql-server-storage-engine-how-to-recreate-the-msdb-database-in-sql
PingBack from http://quickdietsite.info/story.php?id=4056
PingBack from http://fixmycrediteasily.info/story.php?id=1150
PingBack from http://edebtsettlementprogram.info/story.php?id=24418
For SQL 2005, this work perfect!!
Thanks Paul, a very-useful post again.
Varund
After trying to run SQL in trace mode as you mention in step 1, we could not detach msdb database, still got message about it being a system database. tried osql and sqlcmd
Had to do this on a disaster recovery server (w/SQL 2005 Enterprise Ed). The SAN failed and we lost tempdb and msdb b/c we moved them to SAN drives. Lesson learned (at least w/msdb). Worked great; thanks Paul!
Hi Paul. Dumb question: Is there a backout procedure for this, in case something goes terribly wrong (e.g., restore [the albeit] MSDB from backup)?
Paul, thanks a lot,
Anyway, the explanation you wrote in another thread you specified the use of sp_detach_db "msdb", what is an important part of information.
This is the thread: social.msdn.microsoft.com/.../d9a8bd3b-017c-4ff9-b26c-71402c70bfe6
Thanks paul,
Article very helpful....
Thanks its recovered but I have setup Distribution server for replication and after replace msdb replication not work.